Выборки из индексированных таблиц (Диаграмма# 5) |
# |
Тест |
Команда |
32 бита, с |
64 бита, с |
Прирост, % |
Задействовано строк (rows) |
7 |
Выборка из 28 962 208 записей |
select count(*) from sales_fact_1997 WHERE product_id = 1 and time_id = 741 and customer_id = 614 and store_id = 17 |
0,671 |
0,58 |
115,491 |
28 962 208 |
8 |
Выборка из большой таблицы с битовым индексом |
select
sum (store_sales) store_sales_total, sum (store_cost) store_cost_total,
sum (unit_sales) unit_sales_total from sales_fact t
(select time_id from 'time_by_day' t where (the_day
= TRANSLATE('Saturday' USING NCHAR_CS) or the_day = TRANSLATE('Sunday' USING NCHAR_CS))) |
759,442 |
3609,119 |
-- |
231 697 664 |
9 |
выборка из большой таблицы с битовым индексом |
select
sum (store_sales) store_sales_total, sum (store_cost) store_cost_total,
sum (unit_sales) unit_sales_total from sales_fact t
(select time_id from 'time_by_day' t where (the_day
= TRANSLATE('Saturday' USING NCHAR_CS) or the_day = TRANSLATE('Sunday' USING
NCHAR_CS))) |
759,442 |
680,00 |
111,683 |
231 697 664 |
10 |
выборка из файла с битовым индексом |
select
sum (store_sales) store_sales_total, sum (store_cost) store_cost_total,
sum (unit_sales) unit_sales_total from sales_fact_1997 t
(select time_id from 'time_by_day' t where (the_day
= TRANSLATE('Saturday' USING NCHAR_CS) or the_day = TRANSLATE('Sunday' USING NCHAR_CS))) |
91,812 |
11,22 |
-- |
28 962 208 |
11 |
Выборка из таблицы с битовым индексом |
select
/*+ INDEX(sales_fact_1997 sales_fact_7_by_time_bit) */ sum (store_sales)
store_sales_total, sum (store_cost) store_cost_total,
sum (unit_sales) unit_sales_total from sales_fact_1997 t
(select time_id from 'time_by_day' t where (the_day
= TRANSLATE('Saturday' USING NCHAR_CS) or the_day = TRANSLATE('Sunday' USING
NCHAR_CS))) |
17,405 |
11,31 |
153,931 |
28 962 208 |