Використання декількох індексів для виконання запиту, Інші СУБД, Бази даних, статті

У цьому випуску ми розглянемо деякі особливості використання індексів у Oracle.

Максимальна кількість індексів для запиту


Коли в запиті є два і більше умови з перевіркою на рівність, можна використовувати кілька індексів. Oracle буде “об’єднувати” (зливати, merge) індекси під час виконання, повертаючи рядки, які знайдені за обома індексами. Наприклад, при виконанні запиту:

SELECT    ename
FROM emp
WHERE deptno=20
AND job=”manager”

може бути виконано злиття наступних індексів:



А як реально сервер здійснює злиття індексів? Що краще: конкатенувати індекс по стовпцях job і deptno або окремі індекси за стовпцями job і deptno, Які можуть бути об’єднані?


Чи правда, що Oracle використовує для запиту не більше 5 індексів? Скільки максимум індексів за таблицею можна створити?


Відповідь Тома Кайта


Індекси об’єднуються саме так, як ви і подумали. Умова deptno=20 буде генерувати один список ідентифікаторів рядків-кандидатів, а умова job=”manager”– Інший. Ці списки будуть об’єднуватися, і фактично вибрані з таблиці будуть тільки рядки, що входять в обидва списки.


А ось відповідь на питання, що краще (як завжди) залежить від багатьох обставин. Бувають випадки, сприятливі для обох варіантів. У розглянутому вище прикладі, конкатенувати індекс по deptno, job, Швидше за все, буде краще (роботи менше). Однак, якщо в таблиці EMP є 50 шпальт, а в умови можуть згадуватися будь ДВА з них? Ви хочете створювати індекс для кожного поєднання (я – ні)?


При роботі з індексами на основі бітових карт (bitmap indexes), використовувати конкатенувати індекс майже ніколи немає сенсу. Практично завжди індексуються окремі стовпці.


Що стосується обмеження “5“, – Ми будемо використовувати стільки індексів, скільки потрібно. Наприклад:

ops$tkyte@ORA817.US.ORACLE.COM> create table t ( a int, b int, c int, d int, e
int, f int, g int, h int, i int );
Table created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_a on t(a);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_b on t(b);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_c on t(c);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_d on t(d);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_e on t(e);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_f on t(f);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_g on t(g);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_h on t(h);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> create bitmap index t_i on t(i);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, “T”,
numrows=>10000000, numblks => 10000000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA817.US.ORACLE.COM> select count(*)
2 from t
3 where a = 1
4 and b = 2
5 and c = 3
6 and d = 4
7 and e = 5
8 and f = 6
9 and g = 7
10 and h = 8
11 and i = 9
12 /
COUNT(*)
———-
0
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=100)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF “T_A”
5 3 BITMAP INDEX (SINGLE VALUE) OF “T_B”
6 3 BITMAP INDEX (SINGLE VALUE) OF “T_C”
7 3 BITMAP INDEX (SINGLE VALUE) OF “T_D”
8 3 BITMAP INDEX (SINGLE VALUE) OF “T_E”
9 3 BITMAP INDEX (SINGLE VALUE) OF “T_F”
10 3 BITMAP INDEX (SINGLE VALUE) OF “T_G”
11 3 BITMAP INDEX (SINGLE VALUE) OF “T_H”
12 3 BITMAP INDEX (SINGLE VALUE) OF “T_I”

Навіть якщо використовуються звичайні індекси, можна буде їх з’єднати:

ops$tkyte@ORA817.US.ORACLE.COM> select /*+ index_join( t ) */ count(*)
2 from t
3 where a = 1
4 and b = 2
5 and c = 3
6 and d = 4
7 and e = 5
8 and f = 6
9 and g = 7
10 and h = 8
11 and i = 9
12 /
COUNT(*)
———-
0
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=1 Bytes=100)
1 0 SORT (AGGREGATE)
2 1 VIEW OF “index$_join$_001” (Cost=32 Card=1 Bytes=100)
3 2 HASH JOIN
4 3 HASH JOIN
5 4 HASH JOIN
6 5 HASH JOIN
7 6 HASH JOIN
8 7 HASH JOIN
9 8 HASH JOIN
10 9 HASH JOIN
11 10 INDEX (RANGE SCAN) OF “T_A” (NON-UNIQUE)
(Cost=11 Card=1 Bytes=100)
12 10 INDEX (RANGE SCAN) OF “T_B” (NON-UNIQUE)
(Cost=11 Card=1 Bytes=100)
13 9 INDEX (RANGE SCAN) OF “T_C” (NON-UNIQUE) (Cost=11
Card=1 Bytes=100)
14 8 INDEX (RANGE SCAN) OF “T_D” (NON-UNIQUE) (Cost=11
Card=1 Bytes=100)
15 7 INDEX (RANGE SCAN) OF “T_E” (NON-UNIQUE) (Cost=11
Card=1 Bytes=100)
16 6 INDEX (RANGE SCAN) OF “T_F” (NON-UNIQUE) (Cost=11 Card=1
Bytes=100)
17 5 INDEX (RANGE SCAN) OF “T_G” (NON-UNIQUE) (Cost=11 Card=1
Bytes=100)
18 4 INDEX (RANGE SCAN) OF “T_H” (NON-UNIQUE) (Cost=11 Card=1
Bytes=100)
19 3 INDEX (RANGE SCAN) OF “T_I” (NON-UNIQUE) (Cost=11 Card=1
Bytes=100)

Використання декількох індексів …


Я тут борюся з використанням декількох індексів для Index Join… У мене є два індекси на основі b-дерева по таблиці, і я хочу вибирати дані шляхом з’єднання цих двох індексів, а не повним переглядом таблиці. Ці два індекси містять всі стовпці, які вибираються в запиті. Я намагався використовувати підказку /*+ INDEX_JOIN(TAB1 IND1 IND2) */, Але вона не працює. Не могли б ви пояснити, як використовувати Index Join для індексів на основі B-дерев? Які параметри ініціалізації треба встановити, щоб ця можливість використовувалася?


Відповідь Тома Кайта


Приклади – завжди потрібні приклади того, що саме намагалися робити, щоб пояснити, чому щось сталося чи не сталося …


Причин може бути безліч. Серед найбільш вірогідних – наступні:


ops$tkyte@ORA817DEV.US.ORACLE.COM> @desc big_table
Datatypes for Table big_table
Data Data
Column Name Type Length Nullable
—————————— ——————– ———– ——–
OWNER VARCHAR2 30 not null
OBJECT_NAME VARCHAR2 30 not null
SUBOBJECT_NAME VARCHAR2 30 null
OBJECT_ID NUMBER not null
DATA_OBJECT_ID NUMBER null
OBJECT_TYPE VARCHAR2 18 null
CREATED DATE 7 not null
LAST_DDL_TIME DATE 7 not null
TIMESTAMP VARCHAR2 19 null
STATUS VARCHAR2 7 null
TEMPORARY VARCHAR2 1 null
GENERATED VARCHAR2 1 null
SECONDARY VARCHAR2 1 null
Indexes on big_table
Index Is
Name Unique COLUMNS
—————————— —— ——————————–
BIG_TABLE_IDX1 No OBJECT_NAME
BIG_TABLE_IDX2 No OBJECT_ID

Для цієї таблиці з 1000000 рядків я запросто домагаюся з’єднання індексів:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index_join( big_table
big_table_idx1 big_table_idx2 ) */
object_name, object_id
2 from big_table
3 where object_name like “ABCDEF%”
4 and object_id between 1000 and 1500
5 /
no rows selected
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=138 Bytes=4140)
1 0 VIEW OF “index$_join$_001” (Cost=83 Card=138 Bytes=4140)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF “BIG_TABLE_IDX2” (NON-UNIQUE) (Cost=81
Card=138 Bytes=4140)
4 2 INDEX (RANGE SCAN) OF “BIG_TABLE_IDX1” (NON-UNIQUE) (Cost=81
Card=138 Bytes=4140)
Statistics
———————————————————-
0 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
272 bytes sent via SQL*Net to client
319 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Мені довелося поставити підказку, оскільки це помилкове план:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_name, object_id
2 from big_table
3 where object_name like “ABCDEF%”
4 and object_id between 1000 and 1500
5 /
no rows selected
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=431 Card=138 Bytes=4140)
1 0 TABLE ACCESS (BY INDEX ROWID) OF “BIG_TABLE” (Cost=431 Card=138
Bytes=4140)
2 1 INDEX (RANGE SCAN) OF “BIG_TABLE_IDX1” (NON-UNIQUE) (Cost=52
Card=138)
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
272 bytes sent via SQL*Net to client
319 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Ніяких параметрів ініціалізації ставити не потрібно.


Підказка index_join призводить до використання тільки одного з індексів …


У мене є запит з підказкою index_join:

SELECT /*+index_join(ho idx1 idx2)*/
sum( nvl(mw,0) )
FROM ho
WHERE fko = :b4 AND
fks = :b3 AND
hdate >= :b2 AND
hdate < :b1

Отримується в результаті план виконання на oracle 9.2.0.3.0 (AIX – 64BIT) показує, що виконується index_join, Але використовується тільки один із зазначених індексів, і з’єднується він з якимось третім індексом. Проблема в тому, що цей третій індекс не секціонірована, тому запит працює довше.

/     Rows Row Source Operation
/——— —————————————————
/ 345 SORT AGGREGATE (cr=6409065 pr=6730699 pw=340953 time=7952.32)
/ 252288 .FILTER (cr=6409065 pr=6730699 pw=340953 time=7951.67)
/ 252288 ..VIEW (cr=6409065 pr=6730699 pw=340953 time=7951.20)
/ 252288 …HASH JOIN (cr=6409065 pr=6730699 pw=340953 time=7950.17)
/154842493 ….PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=264615 pr=264615 pw=0 time=534.27)
/154842493 …..INDEX RANGE SCAN idx2 PARTITION: KEY KEY (object id 42026 ) (cr=264615 pr=264615 pw=0 time=316.62)
/ 5228609 ….INDEX FAST FULL SCAN idx3 (object id 36664 ) (cr=6144450 pr=6125131 pw=0 time=6704.00)

Мене цікавить, чому Oracle вибрав тільки один з двох індексів, зазначених в підказці index_join?


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

CREATE TABLE ho_t
(hid NUMBER(9,0) NOT NULL,
hdate DATE NOT NULL,
fko NUMBER(9,0) NOT NULL,
fks NUMBER(9,0) NOT NULL,
df NUMBER(1,0) NOT NULL,
mw NUMBER(13,3) – … ще 25 стовпців
)
PARTITION BY RANGE (hdate)
(
PARTITION p_2_2 VALUES LESS THAN (TO_DATE(” 2000-07-01 00:00:00″, “SYYYY-MM-DD
HH24:MI:SS”, “NLS_CALENDAR=GREGORIAN”)) – … ще 15 секцій
)
/
CREATE OR REPLACE VIEW ho (
hid,
hdate,
fko,
fks,
df,
mw – .. ще 25 стовпців
)
AS
select hid, hdate, fko, fks, df, mw – … ще 25 стовпців
from ho_t
/
CREATE UNIQUE INDEX idx1 ON ho_t
(
hdate ASC,
fks ASC,
df ASC,
fko ASC
)
LOCAL (
PARTITION p_2_2 – … ще 15 секцій
)
/
CREATE INDEX idx2 ON ho_t
(
hdate ASC,
fko ASC,
mw
)
LOCAL (
PARTITION p_2_2 – … ще 15 секцій
)
/
CREATE INDEX idx3 ON ho_t
(
fko ASC,
fks ASC
)
/
ALTER TABLE ho_t
ADD CONSTRAINT pk_ho_t PRIMARY KEY (hid)
USING INDEX
/
ALTER TABLE ho_t
ADD CONSTRAINT fk_h FOREIGN KEY (fko)
REFERENCES R.s (oi) ON DELETE SET NULL
/

Відповідь Тома Кайта


Допоможіть мені “поламати” наступний приклад:

drop table ho_t;

CREATE TABLE ho_t
(hid NUMBER(9,0) NOT NULL,
hdate DATE NOT NULL,
fko NUMBER(9,0) NOT NULL,
fks NUMBER(9,0) NOT NULL,
df NUMBER(1,0) NOT NULL,
mw NUMBER(13,3)
)
partition by range(hdate)
(
partition p1 values less than
(TO_DATE(” 2000-07-01 00:00:00″, “SYYYY-MM-DD HH24:MI:SS”,
“NLS_CALENDAR=GREGORIAN”)),
partition p2 values less than
(TO_DATE(” 2001-07-01 00:00:00″, “SYYYY-MM-DD HH24:MI:SS”,
“NLS_CALENDAR=GREGORIAN”))
)
/

create or replace view ho
as
select * from ho_t
/

CREATE UNIQUE INDEX idx1 ON ho_t
(
hdate ASC,
fks ASC,
df ASC,
fko ASC
)
local
/

CREATE INDEX idx2 ON ho_t
(
hdate ASC,
fko ASC,
mw
)
local
/

CREATE INDEX idx3 ON ho_t
(
fko ASC,
fks ASC
)
/

variable b1 varchar2(25);
variable b2 varchar2(25);
variable b3 varchar2(25);
variable b4 varchar2(25);
set linesize 121

delete from plan_table;
explain plan for
SELECT /*+index_join(ho idx1 idx2)*/
sum( nvl(mw,0) )
FROM ho
WHERE fko = to_number(:b4) AND
fks = to_number(:b3) AND
hdate >= to_date(:b2) AND
hdate < to_date(:b1)
/
select * from table(dbms_xplan.display);


Коментар читача від 6 травня 2004


Щоб “поламати” ваш сценарій, мені довелося б скопіювати дані з вихідної таблиці (більше 11 мільйонів рядків) і подивитися, чи буде ваша таблиця вести себе так само, як і вихідна.


Я сподівався, що ви вже стикалися з цією проблемою, наприклад, з недокументованими обмеженнями підказки index_join, Ось чому я і послав визначення таблиці, індексів і подання …


Відповідь Тома Кайта


Ні, використовуйте пакет dbms_stats для установки статистичної інформації для таблиці і т.п., щоб “мій” план став “вашим”.


Якщо з’єднання індексів можна використовувати, воно повинно було використовуватися. Повинно бути щось, Що зробить цей тестовий приклад “більш походимо на реальний”, щоб отримати ваш результат.

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


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

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

Ваш отзыв

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

*

*