Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
create table t1 as select level a, systimestamp b, mod(level, 10) c from dual connect by level < 100;
create unique index i1 on t1(a);
create index i11 on t1(c);

create table t2 as select level + 100 a, systimestamp b, mod(level, 10) c from dual connect by level < 100;
create unique index i2 on t2(a);
create index i21 on t2(c);

create table t3 as select level + 200 a, systimestamp b from dual connect by level < 100
create unique index i3 on t3(a);

create view v1 as
select a, b, c from t1
union all
select a, b, c from t2
union all
select a, b, null from t3;

explain plan for select * from v1 where c = 5;
select * from table(dbms_xplan.display);

Plan hash value: 3321031724

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 1008 | 4 (0)| 00:00:01 |
| 1 | VIEW | V1 | 21 | 1008 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 480 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I11 | 10 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 480 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I21 | 10 | | 1 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | T3 | 99 | 3465 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

4 - access("C"=5)
6 - access("C"=5)
7 - filter(NULL IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

как с помощью хинтов сделать чтобы таблица t3 не использовалась в этом запросе?
спасибо заранее
24 мар 11, 00:00    [10416481]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
А в чем смысл filter понимаешь?
24 мар 11, 00:13    [10416506]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
AmKad
Member

Откуда:
Сообщений: 5344
--Eugene--,

Можно обойти fullscan по t3 создав в t3 поле с (можно not null для целостности) и повесить на нее (уникальный) индекс, а во вью сделать
create or replace view v1 as
select a, b, c from t1
union all
select a, b, c from t2
union all
select a, b, c from t3;
С учетом хранения null-ов и их неиндексируемости - издержки на хранение минимальные.
24 мар 11, 00:27    [10416524]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
AmKad
Member

Откуда:
Сообщений: 5344
AmKad
(можно not null для целостности)

Хотел сказать check (c is null).
24 мар 11, 00:32    [10416530]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
AmKad
--Eugene--,

Можно обойти fullscan по t3 создав в t3 поле с (можно not null для целостности) и повесить на нее (уникальный) индекс...
AmKad, у автора нет fullscan по t3
24 мар 11, 01:21    [10416575]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
AmKad
Member

Откуда:
Сообщений: 5344
wurdu
AmKad
--Eugene--,

Можно обойти fullscan по t3 создав в t3 поле с (можно not null для целостности) и повесить на нее (уникальный) индекс...
AmKad, у автора нет fullscan по t3

Действительно, спасибо за замечание. Как таковое сканирование по фильтру null is not null не производится.
24 мар 11, 01:40    [10416585]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
как оказалось, фулскан и правда не выполняется

но вот еще вопрос:

SQL> select c from v1 where a = 99;
         C
----------
9

explain plan for select c from v1 where a = 99;
select * from table(dbms_xplan.display);
Plan hash value: 3844905898

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 2 (0)| 00:00:01 |
| 1 | VIEW | V1 | 3 | 78 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | I2 | 1 | | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I3 | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

4 - access("A"=99)
6 - access("A"=99)
7 - access("A"=99)

explain plan for select * from v1 where c = 9;
select * from table(dbms_xplan.display);
Plan hash value: 3321031724

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 1008 | 4 (0)| 00:00:01 |
| 1 | VIEW | V1 | 21 | 1008 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 480 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I11 | 10 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 480 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I21 | 10 | | 1 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | T3 | 99 | 3465 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

4 - access("C"=9)
6 - access("C"=9)
7 - filter(NULL IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

explain plan for select * from v1 where c = (select c from v1 where a = 99);
select * from table(dbms_xplan.display);
Plan hash value: 3905504442

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 297 | 14256 | 11 (0)| 00:00:01 |
|* 1 | VIEW | V1 | 297 | 14256 | 9 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS FULL | T1 | 99 | 4752 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 99 | 4752 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 99 | 3465 | 3 (0)| 00:00:01 |
| 6 | VIEW | V1 | 3 | 78 | 2 (0)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I1 | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I2 | 1 | | 0 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I3 | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - filter("C"= (SELECT /*+ */ "C" FROM ( (SELECT /*+ */ "A" "A","B"
"B","C" "C" FROM SANDBOX."T1" "T1" WHERE "A"=99) UNION ALL (SELECT /*+ */ "A"
"A","B" "B","C" "C" FROM SANDBOX."T2" "T2" WHERE "A"=99) UNION ALL (SELECT
/*+ */ "A" "A","B" "B",NULL "C" FROM SANDBOX."T3" "T3" WHERE "A"=99)) "V1"))
9 - access("A"=99)
11 - access("A"=99)
12 - access("A"=99)

Note
-----
- dynamic sampling used for this statement



откуда появились фулсканы? и как от них избавиться?
24 мар 11, 21:51    [10421223]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
--Eugene--,

не умеет оракл проталкивать такие предикаты в такие вью, разве что так в 11.2 :
select * from v1 where c in (select /*+ PRECOMPUTE_SUBQUERY */ c from v1 where a = 1)
24 мар 11, 23:34    [10421561]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
_Nikotin,

тоесть в 10.2 никакие хинты не помогут избавиться от этих фулсканов?
24 мар 11, 23:42    [10421597]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
опаньки, помогло!
explain plan for select * from v1 where c in (select /*+ PRECOMPUTE_SUBQUERY */ c from v1 where a = 99);
select * from table(dbms_xplan.display);
Plan hash value: 3321031724

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 1008 | 4 (0)| 00:00:01 |
| 1 | VIEW | V1 | 21 | 1008 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 180 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I11 | 10 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 190 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I21 | 10 | | 1 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | T3 | 99 | 1683 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

4 - access("C"=9)
6 - access("C"=9)
7 - filter(NULL IS NOT NULL)

спасибо!
24 мар 11, 23:48    [10421617]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
блин, не помогло :(
explain plan for select * from v1 where c in (select /*+ PRECOMPUTE_SUBQUERY */ c from v1 where a = :1);
select * from table(dbms_xplan.display);
Plan hash value: 258013574

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 432 | 11 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | V1 | 297 | 14256 | 9 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL | T1 | 99 | 1782 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 99 | 1881 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T3 | 99 | 1683 | 3 (0)| 00:00:01 |
| 7 | VIEW | V1 | 3 | 78 | 2 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I1 | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I2 | 1 | | 0 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
|* 14 | INDEX UNIQUE SCAN | I3 | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - filter( EXISTS (SELECT /*+ PRECOMPUTE_SUBQUERY */ 0 FROM ( (SELECT /*+
*/ "A" "A","B" "B","C" "C" FROM SANDBOX."T1" "T1" WHERE "A"=TO_NUMBER(:1) AND
"C"=:B1) UNION ALL (SELECT /*+ */ "A" "A","B" "B","C" "C" FROM SANDBOX."T2"
"T2" WHERE "A"=TO_NUMBER(:1) AND "C"=:B2) UNION ALL (SELECT /*+ */ "A"
"A","B" "B",NULL "C" FROM SANDBOX."T3" "T3" WHERE NULL IS NOT NULL AND
"A"=TO_NUMBER(:1))) "V1"))
9 - filter("C"=:B1)
10 - access("A"=TO_NUMBER(:1))
11 - filter("C"=:B1)
12 - access("A"=TO_NUMBER(:1))
13 - filter(NULL IS NOT NULL)
14 - access("A"=TO_NUMBER(:1))
24 мар 11, 23:52    [10421629]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
а вот так протолкнёт :)

+ test
create table t1 as select level a, systimestamp b, mod(level, 10) c from dual connect by level < 100;
create unique index i1 on t1(a);
create index i11 on t1(c);

create table t2 as select level + 100 a, systimestamp b, mod(level, 10) c from dual connect by level < 100;
create unique index i2 on t2(a);
create index i21 on t2(c);

create table t3 as select level + 200 a, systimestamp b, cast(null as number) c from dual connect by level < 100;
create unique index i3 on t3(a);
create index i31 on t3(c);

create view v1 as
select a, b, c from t1
union all
select a, b, c from t2
union all
select a, b, c from t3;

explain plan for 
select v1.* from  v1, (select c from v1 where a = 99 and rownum = 1) v2 where v1.c = v2.c;
select * from table(dbms_xplan.display);


Plan hash value: 328183327
 
-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |     3 |   213 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                    |      |     3 |   213 |     9   (0)| 00:00:01 |
|   2 |   VIEW                           |      |     1 |    15 |     3   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                 |      |       |       |            |          |
|   4 |     VIEW                         | V1   |     3 |    39 |     3   (0)| 00:00:01 |
|   5 |      UNION-ALL                   |      |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| T1   |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN         | I1   |     1 |       |     0   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| T2   |     1 |    26 |     1   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | I2   |     1 |       |     0   (0)| 00:00:01 |
|  10 |       TABLE ACCESS BY INDEX ROWID| T3   |     1 |    26 |     1   (0)| 00:00:01 |
|* 11 |        INDEX UNIQUE SCAN         | I3   |     1 |       |     0   (0)| 00:00:01 |
|  12 |   VIEW                           | V1   |     1 |    56 |     6   (0)| 00:00:01 |
|  13 |    UNION ALL PUSHED PREDICATE    |      |       |       |            |          |
|  14 |     TABLE ACCESS BY INDEX ROWID  | T1   |     1 |    48 |     2   (0)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN            | I11  |     1 |       |     1   (0)| 00:00:01 |
|  16 |     TABLE ACCESS BY INDEX ROWID  | T2   |     1 |    48 |     2   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN            | I21  |     1 |       |     1   (0)| 00:00:01 |
|  18 |     TABLE ACCESS BY INDEX ROWID  | T3   |     1 |    48 |     2   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN            | I31  |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(ROWNUM=1)
   7 - access("A"=99)
   9 - access("A"=99)
  11 - access("A"=99)
  15 - access("C"="V2"."C")
  17 - access("C"="V2"."C")
  19 - access("C"="V2"."C")
 
Note
-----
   - dynamic sampling used for this statement
25 мар 11, 00:30    [10421759]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
_Nikotin,

спасибо!

а по поводу PRECOMPUTE_SUBCUERY, это странно:
explain plan for select * from v1 where c in (select /*+ PRECOMPUTE_SUBQUERY */ c from v1 where a = 99);
select * from table(dbms_xplan.display);
Plan hash value: 3321031724

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 1008 | 4 (0)| 00:00:01 |
| 1 | VIEW | V1 | 21 | 1008 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 180 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I11 | 10 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 190 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I21 | 10 | | 1 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | T3 | 99 | 1683 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

4 - access("C"=9)
6 - access("C"=9)
7 - filter(NULL IS NOT NULL)

explain plan for select * from v1 where c in (select /*- PRECOMPUTE_SUBQUERY */ c from v1 where a = 99);
select * from table(dbms_xplan.display);
Plan hash value: 258013574

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 432 | 11 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | V1 | 297 | 14256 | 9 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL | T1 | 99 | 1782 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 99 | 1881 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T3 | 99 | 1683 | 3 (0)| 00:00:01 |
| 7 | VIEW | V1 | 3 | 78 | 2 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I1 | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I2 | 1 | | 0 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
|* 14 | INDEX UNIQUE SCAN | I3 | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - filter( EXISTS (SELECT /*+ */ 0 FROM ( (SELECT /*+ */ "A" "A","B"
"B","C" "C" FROM SANDBOX."T1" "T1" WHERE "A"=99 AND "C"=:B1) UNION ALL
(SELECT /*+ */ "A" "A","B" "B","C" "C" FROM SANDBOX."T2" "T2" WHERE "A"=99 AND
"C"=:B2) UNION ALL (SELECT /*+ */ "A" "A","B" "B",NULL "C" FROM SANDBOX."T3"
"T3" WHERE NULL IS NOT NULL AND "A"=99)) "V1"))
9 - filter("C"=:B1)
10 - access("A"=99)
11 - filter("C"=:B1)
12 - access("A"=99)
13 - filter(NULL IS NOT NULL)
14 - access("A"=99)

оракл 10.2 - разве там есть этот хинт?
25 мар 11, 01:15    [10421885]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2188
:(
а одиннадцатый, похоже, умнее десятки:
explain plan for select v1.* from v1, (select c from v1 where a = 99 and rownum = 1) v2 where v1.c = v2.c;
select * from table(dbms_xplan.display);
Plan hash value: 1015307974

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 183 | 11 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 183 | 11 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | V1 | 3 | 39 | 2 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I1 | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I2 | 1 | | 0 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I3 | 1 | 4 | 0 (0)| 00:00:01 |
|* 11 | VIEW | V1 | 3 | 144 | 9 (0)| 00:00:01 |
| 12 | UNION-ALL | | | | | |
| 13 | TABLE ACCESS FULL | T1 | 99 | 1782 | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | T2 | 99 | 1881 | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | T3 | 99 | 1683 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

3 - filter(ROWNUM=1)
7 - access("A"=99)
9 - access("A"=99)
10 - access("A"=99)
11 - filter("V1"."C"="V2"."C")
25 мар 11, 01:26    [10421896]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
В 10.2 проблема, что он не хочет без хинта делать unnest, но с хинтом все в подядке. Немного изменю тесткейс, чтобы индекс был выгодным. 10.2.0.5:
create table t1 as select level a, systimestamp b, rownum c from dual connect by level < 100000;

Table created.

create unique index i1 on t1(a);

Index created.

create index i11 on t1(c);

Index created.


create table t2 as select level + 100 a, systimestamp b, rownum c from dual connect by level < 100000;

Table created.

create unique index i2 on t2(a);

Index created.

create index i21 on t2(c);

Index created.


create table t3 as select level + 200 a, systimestamp b, cast(null as number) c from dual connect by level < 100000;

Table created.

create unique index i3 on t3(a);

Index created.

create index i31 on t3(c);

Index created.


create or replace view v1 as
  2  select a, b, c from t1
  3  union all
  4  select a, b, c from t2
  5  union all
  6  select a, b, c from t3;

View created.

set autotrace traceonly explain
select * from v1 where c in (select /*+ unnest */ c from v1 where a = 99);

Execution Plan
----------------------------------------------------------
Plan hash value: 772881275

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          | 10002 |   488K|    40   (3)| 00:00:01 |
|   1 |  NESTED LOOPS                    |          | 10002 |   488K|    40   (3)| 00:00:01 |
|   2 |   VIEW                           | VW_NSO_1 |     3 |    39 |     7  (15)| 00:00:01 |
|   3 |    HASH UNIQUE                   |          |     3 |    78 |     7  (15)| 00:00:01 |
|   4 |     VIEW                         | V1       |     3 |    78 |     6   (0)| 00:00:01 |
|   5 |      UNION-ALL                   |          |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN         | I1       |     1 |       |     1   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| T2       |     1 |    26 |     2   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | I2       |     1 |       |     1   (0)| 00:00:01 |
|  10 |       TABLE ACCESS BY INDEX ROWID| T3       |     1 |    26 |     2   (0)| 00:00:01 |
|* 11 |        INDEX UNIQUE SCAN         | I3       |     1 |       |     1   (0)| 00:00:01 |
|  12 |   VIEW                           | V1       |    33 |  1221 |    11   (0)| 00:00:01 |
|  13 |    UNION ALL PUSHED PREDICATE    |          |       |       |            |          |
|  14 |     TABLE ACCESS BY INDEX ROWID  | T1       |  1254 | 60192 |     3   (0)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN            | I11      |   502 |       |     1   (0)| 00:00:01 |
|  16 |     TABLE ACCESS BY INDEX ROWID  | T2       |  1004 | 48192 |     3   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN            | I21      |   402 |       |     1   (0)| 00:00:01 |
|  18 |     TABLE ACCESS BY INDEX ROWID  | T3       |  1076 | 51648 |     5   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN            | I31      |   430 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   7 - access("A"=99)
   9 - access("A"=99)
  11 - access("A"=99)
  15 - access("C"="$nso_col_1")
  17 - access("C"="$nso_col_1")
  19 - access("C"="$nso_col_1")

Note
-----
   - dynamic sampling used for this statement

25 мар 11, 03:27    [10421938]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
--Eugene--
оракл 10.2 - разве там есть этот хинт?

Видимо есть:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as SYS
 
SQL> select version from GV$SQL_HINT where name = 'PRECOMPUTE_SUBQUERY';
 
VERSION
-------------------------
10.2.0.1
25 мар 11, 08:01    [10422018]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить