В этом выпуске мы рассмотрим некоторые особенности использования индексов в Oracle.
Максимальное количество индексов для запроса
Когда в запросе есть два и более условия с проверкой на равенство, можно использовать несколько индексов. Oracle будет "объединять" ( сливать , merge) индексы во время выполнения, возвращая строки, которые найдены по обоим индексам. Например, при выполнении запроса:
SELECT ename FROM emp WHERE deptno=20 AND job='manager'
может быть выполнено слияние следующих индексов:
- неуникального индекса по столбцу job
- неуникального индекса по столбцу deptno
А как реально сервер осуществляет слияние индексов? Что лучше: конкатенированный индекс по столбцам 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 для установки статистической информации для таблицы и т.п., чтобы "мой" план стал "вашим".
Если соединение индексов можно использовать, оно должно было использоваться. Должно быть нечто, что сделает этот тестовый пример "более походим на реальный", чтобы получить ваш результат.