Базы данных Oracle - статьи

         

A (для TEST_NORMAL)


Создаем bitmap-индекс на столбец SAL таблицы TEST_NORMAL. Этот столбец имеет нормальную селективность.

SQL> create bitmap index normal_sal_bmx on test_normal(sal); Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns; Table analyzed.

Теперь давайте получим размер индекса и фактор кластеризации.

SQL>select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB" 2* from user_segments 3* where segment_name in ('TEST_NORMAL','NORMAL_SAL_BMX');

SEGMENT_NAME Size in MB ------------------------------ -------------- TEST_NORMAL 50 NORMAL_SAL_BMX 4

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR ------------------------------ ---------------------------------- NORMAL_SAL_BMX 6001

Теперь запросы. Сначала выполним их с предикатом равенства:

SQL> set autot trace SQL> select * from test_normal where sal=&sal; Enter value for sal: 1869 old 1: select * from test_normal where sal=&sal new 1: select * from test_normal where sal=1869

164 rows selected. Elapsed: 00:00:00.08

Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=4032) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=39 Card=168 Bytes=4032) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'

Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 165 consistent gets 0 physical reads 0 redo size 8461 bytes sent via SQL*Net to client 609 bytes received via SQL*Net from client 12 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 164 rows processed

И затем с диапазонными предикатами:

SQL> select * from test_normal where sal between &sal1 and &sal2; Enter value for sal1: 1500 Enter value for sal2: 2000 old 1: select * from test_normal where sal between &sal1 and &sal2 new 1: select * from test_normal where sal between 1500 and 2000






83743 rows selected. Elapsed: 00:00:05.00
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes=2001024) 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376 Bytes=2001024)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11778 consistent gets 5850 physical reads 0 redo size 4123553 bytes sent via SQL*Net to client 61901 bytes received via SQL*Net from client 5584 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 83743 rows processed
Теперь удалим bitmap-индекс и создадим B*tree индекс на TEST_NORMAL.
SQL> create index normal_sal_idx on test_normal(sal); Index created.
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns; Table analyzed.
Взгляните на размер индекса и фактор кластеризации.
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB" 2 from user_segments 3 where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX');
SEGMENT_NAME Size in MB ------------------------------ --------------- TEST_NORMAL 50 NORMAL_SAL_IDX 17
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR ------------------------------ ---------------------------------- NORMAL_SAL_IDX 986778
Из полученных выше данных можно увидеть, что этот индекс больше, чем bitmap-индекс на тот же столбец. Фактор кластеризации также близок к количеству строк в таблице.
Теперь для тестов; сначала предикаты равенства:
SQL> set autot trace SQL> select * from test_normal where sal=&sal; Enter value for sal: 1869 old 1: select * from test_normal where sal=&sal new 1: select * from test_normal where sal=1869
164 rows selected. Elapsed: 00:00:00.01
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=169 Card=168 Bytes=4032) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=169 Card=168 Bytes=4032) 2 1 INDEX (RANGE SCAN) OF 'NORMAL_SAL_IDX' (NON-UNIQUE) (Cost=3 Card=168)


Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 177 consistent gets 0 physical reads 0 redo size 8461 bytes sent via SQL*Net to client 609 bytes received via SQL*Net from client 12 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 164 rows processed
...и затем диапазонные предикаты:
SQL> select * from test_normal where sal between &sal1 and &sal2; Enter value for sal1: 1500 Enter value for sal2: 2000 old 1: select * from test_normal where sal between &sal1 and &sal2 new 1: select * from test_normal where sal between 1500 and 2000
83743 rows selected. Elapsed: 00:00:04.03
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes =2001024) 1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376 Bytes=2001024)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11778 consistent gets 3891 physical reads 0 redo size 4123553 bytes sent via SQL*Net to client 61901 bytes received via SQL*Net from client 5584 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 83743 rows processed
Когда запросы выполнены для различных наборов значений, результат, как показано ниже, показывает, что число consistent gets и physical reads совпадают.


BITMAP SAL (Equality) B*TREE Извлечено строк
consistent gets physical reads consistent gets physical reads
165 0 1869 177 164
169 163 3548 181 167  
174 166 6500 187 172  
75 69 7000 81 73  
177 163 2500 190 175  


BITMAP SAL (Range) B*TREE Извлечено строк
consistent gets physical reads consistent gets physical reads
11778 5850 1500-2000 11778 3891 83743
11765 5468 2000-2500 11765 3879 83328
11753 5471 2500-3000 11753 3884 83318
17309 5472 3000-4000 17309 3892 166999
39398 5454 4000-7000 39398 3973 500520

Для диапазонных предикатов оптимизатор предпочитает full scan таблицы для всех различных наборов значений — он не использует индексы вообще — в то время как для предикатов равенства оптимизатор использует индексы. И опять количество consistent gets и physical reads совпадает.
Поэтому можно сделать вывод, что для столбца с нормальной селективностью решения оптимизатора для двух типов индексов были одинаковые и нет существенных различий между вводом/выводом

Содержание раздела