Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 индекс ага или не ага?  [new]
Упс
Guest
Люди! Что-то я засомневался...

есть таблица t в ней поле item1 поп полю построен индекс ( уникальный)
при
select item1 from t where NVL(item1,'ggg'); индекс не сработает ведь?
13 фев 06, 13:04    [2349741]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
нет конечно, для такого случая нужен FBI по NVL(item1,'ggg').
13 фев 06, 13:27    [2349879]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
Всем низачот.

Это

where NVL(item1,'ggg')

простите, что?

Если автор предполагал конструкцию типа

where NVL(item1,'ggg') = 'ggg'

То индекс в принципе может использоваться. Оракл разваливает выражение с NVL в выражение без NVL:

where (item1 = 'ggg' or item1 is null and 'ggg' = 'ggg')

Для поиска строк удовлетворяющих условию item1 = 'ggg' можно использовать индекс. Картину портит "item1 is null". Это условие поддерживается не всяким индексом. Однако, если колонка item1 not null, оракл может сообразить, что условие item1 is null всегда ложное и решиться на использование индекса.
13 фев 06, 14:08    [2350104]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Это кому еще назачот?

SQL> create table t1 as select * from all_objects;

Table created.

SQL> select nullable from user_tab_columns where table_name='T1' and column_name='OBJECT_ID';

N
-
N

SQL> create index i_t1_object_id on t1 (object_id);

Index created.
SQL> exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t1 where nvl(object_id, -1)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   150   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   150   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter(NVL("OBJECT_ID",(-1))=1)

SQL> select * from t1 where (object_id=1 or object_id is null or -1=-1);

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49181 |  4466K|   150   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   | 49181 |  4466K|   150   (1)| 00:00:02 |
--------------------------------------------------------------------------

SQL> select * from t1 where (object_id=1 or object_id is null);

Execution Plan
----------------------------------------------------------
Plan hash value: 852682354

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1             |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=1)

SQL> alter table t1 modify object_id null;

Table altered.

SQL> select * from t1 where (object_id=1 or object_id is null);

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   150   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   150   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1 OR "OBJECT_ID" IS NULL)


where nvl(object_id,-1) = full scan
where (object_id=1 or object_id is null or -1=-1) = full scan
where (object_id=1 or object_id is null) = зависит от nullable поля, но если поле not null - какой смысл в nvl?
13 фев 06, 15:07    [2350381]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
Q u a d r o
Это кому еще назачот?


Всем. Запрос "select item1 from t where NVL(item1,'ggg');" даже не скомпилируется, так что FBI не поможет.
13 фев 06, 16:01    [2350714]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Не отмазывайтесь. Как насчет остальных ваших сказок.
13 фев 06, 16:09    [2350767]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Далай-ламо
Member

Откуда: никого не Тибёт
Сообщений: 92
Q u a d r o
Не отмазывайтесь. Как насчет остальных ваших сказок.

:)
13 фев 06, 16:35    [2350900]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Я все-таки подразумеваю что люди по дефолту не идиоты, и автор полное условие where опустил намерянно - условие вопроса понятно.

Не знаю, что там в буддизме насчет этого :-)
13 фев 06, 17:23    [2351121]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Упс
Guest
в сабже конечно же имелась в виду конструкция ...NVL(item1,'ggg')=NVL(variable_,'ggg').... ну, ошибся :). И достаточно было бы ответа, что в таком случае построенный обычный древовидный индекс не будет испольоваться ( ведь так?).
Если я правильно догадался про FBI, то речь идет о построении битового индекса? Увы, в данном случае структуру БД я править не уполномочен.
к слову БД - 8.1.6
13 фев 06, 17:30    [2351149]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Proteus
Member

Откуда:
Сообщений: 1348
не "битового" в функционального.
13 фев 06, 17:37    [2351188]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
fbi = function based index = индекс по функции.
13 фев 06, 17:39    [2351201]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
Упс
в сабже конечно же имелась в виду конструкция ...NVL(item1,'ggg')=NVL(variable_,'ggg').... ну, ошибся :). И достаточно было бы ответа, что в таком случае построенный обычный древовидный индекс не будет испольоваться ( ведь так?).
Если я правильно догадался про FBI, то речь идет о построении битового индекса? Увы, в данном случае структуру БД я править не уполномочен.
к слову БД - 8.1.6


В 8.1.6 такая конструкция индекс для поиска строк использовать не будет.
ИМХО, в данном случае стоит пожертвовать краткостью, и переписать запрос без NLV. Работать быстрее будет, однозначно, план можно улучшить, да и магическую строку 'ggg' исключить.

(variable_ is not null and item1=variable_ or variable_ is null and item1 is null)

Следующий шаг - развалить запрос на два подзапроса в union all. Один для variable_ is not null (легко сажается на индекс), другой для variable_ is null (тут нужно смотреть, какие ещё условия и индексы есть).
13 фев 06, 18:37    [2351511]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Stax.
Guest
100% видел на сайте Кайта
что оптимизатор умный стал и понимает NVL(item1,'ggg')
не могу найти сцылку по англ
......
stax
13 фев 06, 22:00    [2352079]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Elic
Member

Откуда:
Сообщений: 30023
Stax.
100% видел на сайте Кайта
что оптимизатор умный стал и понимает NVL(item1,'ggg')
не могу найти сцылку
:) Дежавю?
У нас есть немного примеров: 1), 2).
14 фев 06, 04:41    [2352395]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Stax.
что оптимизатор умный стал и понимает NVL(item1,'ggg')

Тут основная проблема не в оптимизаторе, а в том что в B*Tree индекс не попадают null ключи. Использовать B*Tree индекс для поиска null ключей нельзя. Но null попадают в bitmap-индексы.
14 фев 06, 07:33    [2352502]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
P.S. Там к сожалению не случай автора - первым аргументом nvl у него является само поле.
14 фев 06, 08:30    [2352593]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
Q u a d r o
P.S. Там к сожалению не случай автора - первым аргументом nvl у него является само поле.


Если запрос развалится в конкатенацию подзапросов, то индекс может быть использован в том подзапросе, где item1 не null. Но в 8i nvl, кажется, так не разваливался. Остаётся сделать это вручную.
14 фев 06, 10:56    [2353218]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Ну так для NVL(item1,'ggg')='ggg' нам надо:

1. найти все item1='ggg' - тут можно использовать индекс
2. найти все item1 is null - тут индекс использоваться не может, full scan.

Вы понимаете, что конкатенировать результат 1 с результатом 2 будет менее эффективно, чем просто сделать один full scan ?
14 фев 06, 12:19    [2353716]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5964
Q u a d r o
Вы понимаете, что конкатенировать результат 1 с результатом 2 будет менее эффективно, чем просто сделать один full scan ?


Понимаю. Но у тебя в where выражение другое. Автор говорит про NVL(item1,'ggg')=NVL(variable_,'ggg'), а не про NVL(item1,'ggg')='ggg'.
14 фев 06, 12:47    [2353911]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Тут нужны разные планы => разные запросы.

if nvl(variable_, 'ggg') = 'ggg'
then
... where nvl(item1,'ggg')='ggg' => один full scan
else
... where item1=variable_ => range scan
end if;
14 фев 06, 13:03    [2353991]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: индекс ага или не ага?  [new]
Stax..
Guest
Elic
Stax.
100% видел на сайте Кайта
что оптимизатор умный стал и понимает NVL(item1,'ggg')
не могу найти сцылку
:) Дежавю?
У нас есть немного примеров: 1), 2).


ops$tkyte@ORA9IR2> @plan "select * from t where id1 = nvl(:x,id1) and id2 = nvl(:y,id2)"

At runtime, if the bind to be compared to ID2 is NOT NULL, it'll index range scan, else if the
bind is NULL, it'll full scan. reverse the predicate and in this case:



.....
stax
14 июн 07, 09:38    [4265177]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Elic
Member

Откуда:
Сообщений: 30023
Stax..
Не прошло и полтора года
14 июн 07, 09:53    [4265241]     Ответить | Цитировать Сообщить модератору
 Re: индекс ага или не ага?  [new]
Stax..
Guest
Elic
Stax..
Не прошло и полтора года

я не очень знаю языки, тяжело безграмотным
да и не очень искал, а тут в соседней увидел сцылку

ops$tkyte@ORA817DEV> select *
2 from t t1
3 where c1 = nvl(:c1,c1)
4 and c2 = nvl(:c2,c2)
5 and c3 = nvl(:c3,c3)
6 /

tkprof says...

select *
from t t1
where c1 = nvl(:c1,c1)
and c2 = nvl(:c2,c2)
and c3 = nvl(:c3,c3)


Rows Row Source Operation
------- ---------------------------------------------------
1 CONCATENATION
1 FILTER
1 TABLE ACCESS FULL T
0 FILTER
0 TABLE ACCESS BY INDEX ROWID T
0 INDEX RANGE SCAN (T_IDX3)

so, it would optimize the c3=nvl(:c3,c3) but not all of the combinations

my point at the end here is.....

with this above technique you generate a FINITE (small relatively speaking) number of distinct
sql's (the goal is not "1" sql or "42" sql's the goal is a finite number of sql's) and you size
your shared pool accordingly. You won't have millions of statements, you'll have dozens and that
you can size for.

......
stax
14 июн 07, 10:19    [4265401]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить