Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Май 2004   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : использование нескольких индексов для выполнения запроса


Информационный Канал Subscribe.Ru

Выпуск 69

Уважаемые подписчики рассылки!

В этом выпуске мы рассмотрим некоторые особенности использования индексов в Oracle. Давненько я не публиковал переводов ответов Тома Кайта на вопросы пользователей... А, тем временем, у рассылки стало уже более 8000 подписчиков. Вдруг кто-то из вас еще не знает, почему надо регулярно посещать его сайт.

Максимальное количество индексов для запроса

Когда в запросе есть два и более условия с проверкой на равенство, можно использовать несколько индексов. 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 для установки статистической информации для таблицы и т.п., чтобы "мой" план стал "вашим".

Если соединение индексов можно использовать, оно должно было использоваться. Должно быть нечто, что сделает этот тестовый пример "более походим на реальный", чтобы получить ваш результат.

Оригинал обсуждения этого вопроса можно найти здесь. Я не переводил часть, не относящуюся непосредственно к использованию нескольких индексов для выполнения запроса.

Copyright © 2004 Oracle Corporation

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)

В следующем выпуске

По-прежнему не хватает времени, чтобы начать выкладывать исходные тексты к моему переводу книги Тома Кайта. Но, честное слово, скоро я этим займусь. В следующем же выпуске я снова обращусь к материалам сайта AskTom.

А еще скоро я начну переводить новую книгу, посвященную Oracle PL/SQL... Эту книгу рекомендовал Том Кайт. И, вероятно, уже сегодня я буду держать ее в руках. Возможно, в следующем выпуске я напишу о ней подробнее.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу

В избранное