(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Использование нескольких индексов для выполнения запроса

Источник: ln

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

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

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Oracle »
Написать редактору 
 Рекомендовать » Дата публикации: 16.02.2010 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Personal Edition Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Standard Edition 2 Processor License
Oracle Database Standard Edition 2 Named User Plus License
Panda Internet Security - ESD версия - на 1 устройство - (лицензия на 1 год)
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
СУБД Oracle "с нуля"
eManual - электронные книги и техническая документация
Adobe Photoshop: алхимия дизайна
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Арго на деньги (1)
Интересная и захватывающая игра на деньги в казино Argo https://zerkalo-argo-casino.site/ это...
 
Как выбрать матрас (5)
Подскажите как правильно выбрать матрас и на что в целом следует обратить внимание на ваш...
 
работа на дому! (7)
Доброго времени суток дорогие друзья. Многоуровневый маркетинг окончательно признан...
 
Актуальные зеркала букмекерских контор (4)
Бетторы не всегда доверяют зеркалам БК, а зря, потому что эти «запасные аэродромы» запускают...
 
Ставки на спорт через зеркало БК Пинакл (1)
Актуальное зеркало букмекерской конторы Пинакл https://superbet.guru/pinacle-mirror/ всегда...
 
 
 



    
rambler's top100 Rambler's Top100