Как избежать напрасного FULLSCAN(WHERE NULL IS NOT NULL) ?

--Eugene--
Дата: 24.03.2011 00:00:17
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 не использовалась в этом запросе?
спасибо заранее
_Nikotin
Дата: 24.03.2011 00:13:56
А в чем смысл filter понимаешь?
AmKad
Дата: 24.03.2011 00:27:33
--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-ов и их неиндексируемости - издержки на хранение минимальные.
AmKad
Дата: 24.03.2011 00:32:25
AmKad
(можно not null для целостности)

Хотел сказать check (c is null).
wurdu
Дата: 24.03.2011 01:21:14
AmKad
--Eugene--,

Можно обойти fullscan по t3 создав в t3 поле с (можно not null для целостности) и повесить на нее (уникальный) индекс...
AmKad, у автора нет fullscan по t3
AmKad
Дата: 24.03.2011 01:40:04
wurdu
AmKad
--Eugene--,

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

Действительно, спасибо за замечание. Как таковое сканирование по фильтру null is not null не производится.
--Eugene--
Дата: 24.03.2011 21:51:12
как оказалось, фулскан и правда не выполняется

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

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



откуда появились фулсканы? и как от них избавиться?
_Nikotin
Дата: 24.03.2011 23:34:54
--Eugene--,

не умеет оракл проталкивать такие предикаты в такие вью, разве что так в 11.2 :
select * from v1 where c in (select /*+ PRECOMPUTE_SUBQUERY */ c from v1 where a = 1)
--Eugene--
Дата: 24.03.2011 23:42:48
_Nikotin,

тоесть в 10.2 никакие хинты не помогут избавиться от этих фулсканов?
--Eugene--
Дата: 24.03.2011 23:48:36
опаньки, помогло!
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)

спасибо!