Базы данных 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 совпадает.
Поэтому можно сделать вывод, что для столбца с нормальной селективностью решения оптимизатора для двух типов индексов были одинаковые и нет существенных различий между вводом/выводом
