Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 hint для плана с использованием индексов при условии "where... between поле1 and поле2"  [new]
CTUDEHT
Member

Откуда: Moscow
Сообщений: 25
Здравствуйте!

Пожалуйста, подскажите hint для получения плана с использованием индексов при условии
"select ... where заданное_значение between поле1 and поле2", при наличии набора индексов по поле1 и поле 2:

select * from TABLE1 t
where '123' between t.SERIA_START and t.SERIA_END;

create index TABLE1_SS on TABLE1 (SERIA_START);
create index TABLE1_SS on TABLE1 (SERIA_END);
create index TABLE1_SS_SE on TABLE1 (SERIA_START, SERIA_END);

Ожидаемое количество строк в результате 1 - 3.
30 июл 07, 11:35    [4451747]     Ответить | Цитировать Сообщить модератору
 Re: hint для плана с использованием индексов при условии "where... between поле1 and поле2"  [new]
senieur
Member

Откуда: Пермь
Сообщений: 29
ИМХО, кажется что тут эти индексы не применимы.
Т.е.: в данном случае, для того чтобы понять - принимать данную строку в выюорку или нет, Oracle нужно для этой данной строки вычислить where-условие. В такой записи запроса - указанные индексы неприменимы; В данных условиях Oracle остается делать FTS по табличке t;
30 июл 07, 12:15    [4452088]     Ответить | Цитировать Сообщить модератору
 Re: hint для плана с использованием индексов при условии "where... between поле1 and поле2"  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
senieur
ИМХО, кажется что тут эти индексы не применимы.
Т.е.: в данном случае, для того чтобы понять - принимать данную строку в выюорку или нет, Oracle нужно для этой данной строки вычислить where-условие. В такой записи запроса - указанные индексы неприменимы; В данных условиях Oracle остается делать FTS по табличке t;
Если построить составной индекс по полям SERIA_START, SERIA_END, то как минимум можно попасть на NON-UNIQUE INDEX RANGE SCAN.
Будет ли ЭТО эффектвнее FTS - вопрос для отдельного исследования...
30 июл 07, 13:28    [4452794]     Ответить | Цитировать Сообщить модератору
 Re: hint для плана с использованием индексов при условии "where... between поле1 and поле2  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
senieur
ИМХО, кажется что тут эти индексы не применимы.
Т.е.: в данном случае, для того чтобы понять - принимать данную строку в выюорку или нет, Oracle нужно для этой данной строки вычислить where-условие. В такой записи запроса - указанные индексы неприменимы; В данных условиях Oracle остается делать FTS по табличке t;


Да что Вы говорите... А вот oracle считает иначе:

-- без составного индекса
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id  | Operation                   |  Name          | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   122 |   239K|    44 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ANE_TABLE1     |   122 |   239K|    44 |
|*  2 |   INDEX RANGE SCAN          | ANE_TABLE1_SS  |   123 |       |     2 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."SERIA_END">=123)
   2 - access("T"."SERIA_START"<=123)
Note: cpu costing is off

-- С составным индексом
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name             | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   122 |   239K|    43
|   1 |  TABLE ACCESS BY INDEX ROWID| ANE_TABLE1        |   122 |   239K|    43
|*  2 |   INDEX RANGE SCAN          | ANE_TABLE1_SS_SE  |   122 |       |     2
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SERIA_END">=123 AND "T"."SERIA_START"<=123)
       filter("T"."SERIA_END">=123)
Note: cpu costing is off
30 июл 07, 13:31    [4452827]     Ответить | Цитировать Сообщить модератору
 Re: hint для плана с использованием индексов при условии "where... between поле1 and поле2"  [new]
senieur
Member

Откуда: Пермь
Сообщений: 29
Признаю - ошибочка, виноват.
Подумалось что where условие будет вычислятся как что то типа: where литерал between :b1 and :b2; где :b1, :b2 - значения полей t.SERIA_START, t.SERIA_END текущей строки табл. t;
Ан нет, Oralce сделал вот так:

SQL> create table test nologging as select rownum id, a.* from all_objects a;

Table created.

SQL> create index test_oid_ix on test(object_id);

Index created.

SQL> create index test_id_ix on test(id);

Index created.

SQL> create index test_id_oid_ix on test(id, object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'TEST',null,100,method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

SQL> explain plan for select t.* from test t where '1' between t.ID and t.OBJECT_ID;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2346342768
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 3 (0)|00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 3 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_ID_IX | 1 | | 2 (0)|00:00:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">=1)
2 - access("T"."ID"<=1)

15 rows selected.

Причем, что отдельно интересно, если записать select t.* from test t where '1' between t.OBJECT_ID and t.ID;
То доступ будет выполнятся по индексу на столбец OBJECT_ID:

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."ID">=1)
2 - access("T"."OBJECT_ID"<=1)

Тогда хинты: смотреть нужно тут: "Database Performance Tuning Guide" > "Hints for Access Paths";
30 июл 07, 13:48    [4452986]     Ответить | Цитировать Сообщить модератору
 Re: hint для плана с использованием индексов при условии "where... between поле1 and поле2  [new]
CTUDEHT
Member

Откуда: Moscow
Сообщений: 25
Спасибо за подсказку!

Рассуждая, пришел к следующему: в таблице TABLE1 1 млн. строк, известно, что запрос должен возвратить только одну. В результате при добавлении в запрос хинта /*+ first_rows */ скорость получения результата уменьшается с 5 до 0,4 сек., т.е. на порядок. Оптимизатор строит план :

1 SELECT STATEMENT 26738
2 TABLE ACCESS BY INDEX ROWID TABLE1 1
3 INDEX RANGE SCAN TABLE1_SS_SE 1

Индекс TABLE1_SS_SE построен по двум полям.
1 авг 07, 14:43    [4464099]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить