как оказалось, фулскан и правда не выполняется
но вот еще вопрос:
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
откуда появились фулсканы? и как от них избавиться?