Bitmap-індекс або B * tree-індекс: який і коли застосовувати?, Інші СУБД, Бази даних, статті

Розуміння, як правильно застосувати кожен з індексів, може зробити істотний вплив на продуктивність.

Відома мудрість говорить, що bitmap-індекси більш застосовні для стовпців, які мають мало розрізняються значень – таких як ПОЛ, СЕМЕЙНОЕ_ПОЛОЖЕНІЕ і спорідненості. Однак, це припущення не завжди вірно. В реальності застосування bitmap-індексу завжди доцільно в системах, в яких дані рідко змінюються багатьма одночасно працюючими завданнями. Фактично, як я далі продемонструю в цій статті, bitmap-індекс для стовпчика з 100% унікальними значеннями (цей стовпець може бути первинним ключем) може бути також ефективний, як і індекс B * tree.

У цій статті я приведу декілька прикладів, що включають рішення оптимізатора, які є загальними для обох типів індексів для стовпців, як з низькою, так і з високою селективністю. Ці приклади допоможуть адміністраторам БД зрозуміти, що використання bitmap-індексів в дійсності залежить не від селективності, а від програми.

Порівняння індексів

Серед недоліків bitmap-індексу для унікального стовпця є потреба в досить великому просторі (і тому Oracle не рекомендує його). Однак, розмір bitmap-індексу залежить від селективності стовпця, на якому він побудований, а також від розподілу даних. Тому bitmap-індекс на стовпець ПОЛ буде менше, ніж B * tree-індекс на той же самий стовпець. З іншого боку, bitmap-індекс для EMPNO (кандидат в первинні ключі) буде набагато більше, ніж B * tree-індекс на цей же стовпець. Однак, так як до систем підтримки прийняття рішень (decision-support systems – DSS) мають доступ менше число користувачів, ніж до систем обробки транзакцій (transaction-processing systems – OLTP), то ресурси – це не проблема для цих додатків.

Для ілюстрації цього, я створив дві таблиці, TEST_NORMAL і TEST_RANDOM. У таблицю TEST_NORMAL додав мільйон рядків за допомогою PL / SQL-блоку, а потім ці рядки вставив в таблицю TEST_RANDOM в довільному порядку:

Create table test_normal (empno number(10), ename varchar2(30), sal number(10));

Begin
For i in 1..1000000
Loop
Insert into test_normal
values(i, dbms_random.string(“U”,30), dbms_random.value(1000,7000));
If mod(i, 10000) = 0 then
Commit;
End if;
End loop;
End;
/

Create table test_random
as
select /*+ append */ * from test_normal order by dbms_random.random;
SQL> select count(*) “Total Rows” from test_normal;
Total Rows
———-
1000000
Elapsed: 00:00:01.09
SQL> select count(distinct empno) “Distinct Values” from test_normal;
Distinct Values
—————
1000000
Elapsed: 00:00:06.09
SQL> select count(*) “Total Rows” from test_random;
Total Rows
———-
1000000
Elapsed: 00:00:03.05
SQL> select count(distinct empno) “Distinct Values” from test_random;
Distinct Values
—————
1000000
Elapsed: 00:00:12.07

Зауважте, що таблиця TEST_NORMAL заповнена послідовно, а таблиця TEST_RANDOM створена з довільним порядком записів і тому містить неорганізовані дані. У цій таблиці стовпець EMPNO має 100% різних значень і є хорошим кандидатом в первинні ключі. Якщо визначити цей стовпець як первинний ключ, буде створений B * tree-індекс, а не bitmap-індекс, бо Oracle не підтримує bitmap-індекси для первинних ключів.

Щоб проаналізувати поведінку цих індексів, виконаємо наступні кроки:



  1. Для TEST_NORMAL:

    1. Створюємо bitmap-індекс для стовпчика EMPNO і виконуємо декілька запитів з предикатом рівності.

    2. Створюємо B * tree індекс для стовпчика EMPNO, виконуємо декілька запитів з предикатом рівності і порівнюємо операції логічного і фізичного введення / виведення цих запитів, що виконуються для отримання результатів для цих наборів значень.

  2. Для TEST_RANDOM:

    1. Те ж саме що і Крок 1A.

    2. Те ж саме що і Крок 1B.

  3. Для TEST_NORMAL:

    1. Те ж саме що і Крок 1A, тільки запити виконуємо з діапазоном предикатів.

    2. Те ж саме що і Крок 1B, тільки запити виконуємо з діапазоном предикатів. Порівнюємо статистику.

  4. Для TEST_RANDOM:

    1. Те ж саме що і Крок 3A.

    2. Те ж саме що і Крок 3B.

  5. Для TEST_NORMAL:

    1. Створюємо bitmap-індекс для стовпчика SAL, і потім виконуємо декілька запитів з предикатом рівності і кілька з діапазонним предикатом.

    2. Створюємо B * tree індекс для стовпчика SAL, і потім виконуємо декілька запитів з предикатом рівності і кілька з діапазонним предикатом (той же набір значень, як на Кроці 5A). Порівнюємо операції введення / виводу запитів, що виконуються для отримання результатів.

  6. Додаємо стовпець GENDER в обидві таблиці, і виконаємо update цього стовпця, встановивши три можливі значення: M для чоловічої статі, F для жіночої статі, і null, якщо підлога не заданий. Значення цього стовпця встановлюються за одним і тим же умові.

  7. Створюємо bitmap-індекс для цього стовпця, і потім виконуємо декілька запитів з предикатом рівності.

  8. Створюємо B * tree індекс для стовпчика GENDER, і потім виконуємо декілька запитів з предикатом рівності. Порівнюємо результати з Кроком 7.

Кроки з 1 по 4 виконуються для стовпця з високою селективністю (100% різних значень), Крок 5 для стовпця з середньою селективністю, а Кроки 7 і 8 з низькою селективністю.

Крок 1A (для TEST_NORMAL)

На цьому кроці ми створимо bitmap-індекс на таблицю TEST_NORMAL і потім перевіримо розмір індексу, його фактор кластеризації, і розмір таблиці. Потім ми виконаємо кілька запитів з предикатом рівності і зафіксуємо кількість операцій введення / виводу запитів, що використовують цей bitmap-індекс.

SQL> create bitmap index normal_empno_bmx on test_normal(empno);
Index created.
Elapsed: 00:00:29.06
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:19.01
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_EMPNO_BMX”);

SEGMENT_NAME Size in MB
———————————— —————
TEST_NORMAL 50
NORMAL_EMPNO_BMX 28
Elapsed: 00:00:02.00
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
—————————— ———————————
NORMAL_EMPNO_BMX 1000000
Elapsed: 00:00:00.00

Ви бачите, що розмір індексу 28MB і що фактор кластеризації дорівнює кількості рядків у таблиці. Тепер виконаємо запити з предикатом рівності по різним наборам значень:

SQL> set autotrace only
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_normal where empno=&empno
new 1: select * from test_normal where empno=1000
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_NORMAL” (Cost=4 Card=1 Bytes=34)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_EMPNO_BMX”
Statistics
———————————————————-
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Крок 1B (для TEST_NORMAL)

Тепер видалимо цей bitmap-індекс і створимо B * tree індекс на стовпець EMPNO. Як і раніше, перевіримо розмір індексу і фактор кластеризації і виконаємо ці ж запити з тих же наборам значень, щоб порівняти введення / виведення.

SQL> drop index NORMAL_EMPNO_BMX;
Index dropped.
SQL> create index normal_empno_idx on test_normal(empno);
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_EMPNO_IDX”);
SEGMENT_NAME Size in MB
———————————- —————
TEST_NORMAL 50
NORMAL_EMPNO_IDX 18
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
———————————- ———————————-
NORMAL_EMPNO_IDX 6210

Видно, що B * tree індекс менше, ніж bitmap-індекс на стовпець EMPNO. Фактор кластеризації B * tree індексу істотно ближче до кількості блоків таблиці, тому B * tree індекс ефективний для запитів з діапазонним предикатом.

Тепер виконаємо ці ж запити по тому ж набору значень, використовуючи наш B * tree індекс.

SQL> set autot trace
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_normal where empno=&empno
new 1: select * from test_normal where empno=1000
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_NORMAL” (Cost=4 Card=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF “NORMAL_EMPNO_IDX” (NON-UNIQUE) (Cost=3 Card=1)
Statistics
———————————————————-
29 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Видно, що коли запити виконані по набору розрізняються значень, число consistent gets і physical reads ідентично для bitmap і B * tree індексів на 100% унікальному стовпці.























































BITMAP EMPNO B*TREE
consistent gets physical reads consistent gets physical reads
5 0 1000 5 0
5 2 2398 5 2
5 2 8545 5 2
5 2 98008 5 2
5 2 85342 5 2
5 2 128444 5 2
5 2 858 5 2
 

Крок 2A (для TEST_RANDOM)

Тепер виконаємо такий же експеримент над TEST_RANDOM:

SQL> create bitmap index random_empno_bmx on test_random(empno);
Index created.
SQL> analyze table test_random 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_RANDOM”,”RANDOM_EMPNO_BMX”);

SEGMENT_NAME Size in MB
———————————— —————
TEST_RANDOM 50
RANDOM_EMPNO_BMX 28
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
—————————— ———————————
RANDOM_EMPNO_BMX 1000000

Знову статистика (розмір і фактор кластеризації) ідентична для цих індексів зі статистикою за таблицею TEST_NORMAL:

SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_random where empno=&empno
new 1: select * from test_random where empno=1000
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_RANDOM” (Cost=4 Card=1 Bytes=34)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF “RANDOM_EMPNO_BMX”
Statistics
———————————————————-
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Крок 2B (для TEST_RANDOM)

Тепер, на Кроці 1B (мабуть, повинно бути 2В – примітка перекл.) Видалимо bitmap-індекс і створимо B * tree індекс на стовпець EMPNO.

SQL> drop index RANDOM_EMPNO_BMX;
Index dropped.
SQL> create index random_empno_idx on test_random(empno);
Index created.
SQL> analyze table test_random 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_RANDOM”,”RANDOM_EMPNO_IDX”);
SEGMENT_NAME Size in MB
———————————- —————
TEST_RANDOM 50
RANDOM_EMPNO_IDX 18
SQL> select index_name, clustering_factor from user_indexes;
INDEX_NAME CLUSTERING_FACTOR
———————————- ———————————-
RANDOM_EMPNO_IDX 999830

Результат показує, що розмір індексу дорівнює розміру цього індексу для таблиці TEST_NORMAL, але фактор кластеризації ближчий до кількості рядків, що робить цей індекс неефективним для запитів з діапазонним предикатом (його ми побачимо на Кроці 4). Цей фактор кластеризації не впливатиме на запити з предикатом рівності, тому що рядки мають 100% розрізняються значень і кількість рядків на значення дорівнює 1.

Тепер виконаємо запити з предикатом рівності і тим же самим набором значень.

SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_random where empno=&empno
new 1: select * from test_random where empno=1000
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_RANDOM” (Cost=4 Card=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF “RANDOM_EMPNO_IDX” (NON-UNIQUE) (Cost=3 Card=1)
Statistics
———————————————————-
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Знову результати повністю ідентичні отриманим на Кроці 1A і Кроці 1B. Розподіл даних не вплинуло на кількість consistent gets і physical reads для унікального стовпця.

Крок 3A (для TEST_NORMAL)

На цьому кроці ми створимо bitmap-індекс (як на Кроці 1A). Ми знаємо розмір і фактор кластеризації індексу, що дорівнює кількості рядків таблиці. Тепер виконаємо кілька запитів з діапазонними предикатами.

SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_normal where empno between &range1 and &range2
new 1: select * from test_normal where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:00.03
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_NORMAL” (Cost=451 Card=2299 Bytes=78166)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF “NORMAL_EMPNO_BMX”
Statistics
———————————————————-
0 recursive calls
0 db block gets
331 consistent gets
0 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed

Крок 3B (для TEST_NORMAL)

На цьому кроці виконаємо запити за таблицею TEST_NORMAL з B * tree індексом.

SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_normal where empno between &range1 and &range2
new 1: select * from test_normal where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:00.02
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_NORMAL” (Cost=23 Card=2299 Bytes=78166)
2 1 INDEX (RANGE SCAN) OF “NORMAL_EMPNO_IDX” (NON-UNIQUE) (Cost=8 Card=2299)
Statistics
———————————————————-
0 recursive calls
0 db block gets
329 consistent gets
15 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed

Коли ці запити будуть виконані для різних наборів діапазонів, результати, представлені нижче, покажуть:

















































BITMAP EMPNO (Range) B*TREE
consistent gets physical reads consistent gets physical reads
331 0 1-2300 329 0
285 0 8-1980 283 0
346 19 1850-4250 344 16
427 31 28888-31850 424 28
371 27 82900-85478 367 23
2157 149 984888-1000000 2139 35

Як бачите, що кількість consistent gets і physical reads обох індексів знову майже ідентичні. Останній діапазон (984888-1000000) повернув цілих 15,000 рядків, тобто найбільша кількість рядків з усіх витягнутих по іншим діапазонам. Тому, коли ми запросили full scan по таблиці (вказавши хинт / * + full (test_normal) * /), кількість consistent gets і physical reads було 7,239 і 5,663, відповідно.

Крок 4A (для TEST_RANDOM)

На цьому кроці ми виконаємо запити з діапазонними предикатами за таблицею TEST_RANDOM з bitmap-індексом і звіримо послідовні consistent gets і physical reads. Тут ви побачите вплив фактора кластеризації.

SQL>select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_random where empno between &range1 and &range2
new 1: select * from test_random where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:08.01
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_RANDOM” (Cost=453 Card=2299 Bytes=78166)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF “RANDOM_EMPNO_BMX”
Statistics
———————————————————-
0 recursive calls
0 db block gets
2463 consistent gets
1200 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed

Крок 4B (для TEST_RANDOM)

На цьому кроці ми виконаємо запити з діапазонним предикатом за таблицею TEST_RANDOM з B * tree індексом. Повторю, що фактор кластеризації цього індексу був дуже близький до кількості рядків у таблиці (і тому неефективний). Нижче показано, що про це повідомляє оптимізатор:

SQL> select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_random where empno between &range1 and &range2
new 1: select * from test_random where empno between 1 and 2300
2300 rows selected.
Elapsed: 00:00:03.04
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (FULL) OF “TEST_RANDOM” (Cost=613 Card=2299 Bytes=78166)
Statistics
———————————————————-
0 recursive calls
0 db block gets
6415 consistent gets
4910 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
 

Оптимізатор волів full scan таблиці, а не використання індексу, тому що фактор кластеризації:

















































BITMAP EMPNO (Range) B*TREE
consistent gets physical reads consistent gets physical reads
2463 1200 1-2300 6415 4910
2114 31 8-1980 6389 4910
2572 1135 1850-4250 6418 4909
3173 1620 28888-31850 6456 4909
2762 1358 82900-85478 6431 4909
7254 3329 984888-1000000 7254 4909

Тільки для останнього діапазону (984888-1000000) оптимізатор волів full scan таблиці з bitmap-індексом, тоді як для всіх інших діапазонів він віддав перевагу full scan таблиці з B * tree індексом. Це невідповідність утворилося внаслідок фактора кластеризації: Оптимізатор не бере до уваги значення фактора кластеризації, коли генерує план виконання з використанням bitmap-індексу, тоді як для B * tree індексу, він це робить. У цьому сценарії bitmap-індекс виконується більш ефективно, ніж B * tree індекс.

Нижченаведені кроки показують більш цікаві деталі про ці індекси.

Крок 5A (для 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 збігається.

Тому можна зробити висновок, що для стовпця з нормальною селективністю рішення оптимізатора для двох типів індексів були однакові і немає істотних відмінностей між вводом / виводом

Крок 6 (додавання стовпця GENDER)

Перед виконанням тесту щодо шпальти з низькою селективністю, давайте додамо стовпець GENDER в цю таблицю і виконаємо для нього update зі значеннями M, F, І null.

SQL> alter table test_normal add GENDER varchar2(1);
Table altered.
SQL> select GENDER, count(*) from test_normal group by GENDER;
S COUNT(*)
– ———-
F 333769
M 499921
166310
3 rows selected.

Розмір bitmap-індексу за цим стовпцем приблизно 570KB, як показано нижче:

SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);
Index created.
Elapsed: 00:00:02.08
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_GENDER_BMX”);
SEGMENT_NAME Size in MB
—————————— —————
TEST_NORMAL 50
NORMAL_GENDER_BMX .5625
2 rows selected.

З іншого боку, B * tree індекс на цей стовпець має розмір 13MB, який набагато більше, ніж bitmap-індекс на цей стовпець.

SQL> create index normal_GENDER_idx on test_normal(GENDER);
Index created.
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_GENDER_IDX”);
SEGMENT_NAME Size in MB
—————————— —————
TEST_NORMAL 50
NORMAL_GENDER_IDX 13
2 rows selected.

Тепер, якщо виконати запит з предикатом рівності, оптимізатор не буде використовувати індекс, ні bitmap, ні B * tree. А віддасть перевагу full scan по таблиці.

SQL> select * from test_normal where GENDER is null;
166310 rows selected.
Elapsed: 00:00:06.08
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=166310 Bytes=4157750)
1 0 TABLE ACCESS (FULL) OF “TEST_NORMAL” (Cost=601 Card=166310 Bytes=4157750)
SQL> select * from test_normal where GENDER=”M”;
499921 rows selected.
Elapsed: 00:00:16.07
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=499921 Bytes=12498025)
1 0 TABLE ACCESS (FULL) OF “TEST_NORMAL” (Cost=601 Card=499921Bytes=12498025)
SQL>select * from test_normal where GENDER=”F”
/
333769 rows selected.
Elapsed: 00:00:12.02
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=333769 Byte
s=8344225)
1 0 TABLE ACCESS (FULL) OF “TEST_NORMAL” (Cost=601 Card=333769
Bytes=8344225)

Висновок

Тепер, коли ми зрозуміли, як оптимізатор реагує на ці технічні прийоми, давайте перевіримо сценарій, який докладно демонструє найкраще застосування bitmap-і B * tree індексів.

Поряд з bitmap-індексом за стовпцем GENDER, створимо ще один bitmap-індекс на стовпець SAL і потім виконаємо кілька запитів. Запити будуть виконані і з B * tree індексами на ці стовпці.

З таблиці TEST_NORMAL потрібні номери співробітників чоловічої статі, щомісячна зарплата яких дорівнює одному з наступних значень:

1000
1500
2000
2500
3000
3500
4000
4500

Отже:

SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER=”M”;

Це звичайний запит до сховища даних, який, звичайно, ніколи не слід виконувати в OLTP-системі. Нижче показаний результат з bitmap-індексом по обох стовпцях:

SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER=”M”;
1453 rows selected.
Elapsed: 00:00:02.03
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=754 Bytes=18850)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “TEST_NORMAL” (Cost=198 Card=754 Bytes=18850)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP OR
5 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
6 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
7 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
8 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
9 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
10 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
11 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
12 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
13 4 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_SAL_BMX”
14 3 BITMAP INDEX (SINGLE VALUE) OF “NORMAL_GENDER_BMX”
Statistics
———————————————————-
0 recursive calls
0 db block gets
1353 consistent gets
920 physical reads
0 redo size
75604 bytes sent via SQL*Net to client
1555 bytes received via SQL*Net from client
98 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1453 rows processed

І з B * tree-індексом:

SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER=”M”;
1453 rows selected.
Elapsed: 00:00:03.01
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=754 Bytes=18850)
1 0 TABLE ACCESS (FULL) OF “TEST_NORMAL” (Cost=601 Card=754 Bytes=18850)
Statistics
———————————————————-
0 recursive calls
0 db block gets
6333 consistent gets
4412 physical reads
0 redo size
75604 bytes sent via SQL*Net to client
1555 bytes received via SQL*Net from client
98 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1453 rows processed

Звідси видно, що з B * tree індексом, оптимізатор воліє full scan за таблицею, в той час як у випадку з bitmap-індексом, для відповіді на запит він використовує індекс. Ви можете простежити за продуктивністю за допомогою кількості необхідних операцій введення / виводу, що виконуються для отримання результату.

Виходить, bitmap-індекси найкраще підходять для DSS-систем незалежно від селективності, з наступних причин:


З іншого боку, B * tree індекси добре застосовні для OLTP-додатків, в яких призначені для користувача запити досить складні (і добре оптимізовані перед промисловим застосуванням), на відміну від ad hoc запитів, які не такі часті і виконуються під час непікові навантажень. Так як дані часто оновлюються і видаляються з OLTP-додатків, bitmap-індекси можуть спричинити серйозні проблеми блокування.

Дані, представлені тут, достатньо прозорі. Обидва індексу мають схоже призначення: повернути результати як можна швидше. Проте ваш вибір, який з них використовувати, повинен залежати виключно від типу програми, а не від рівня селективності.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*