explain plan set statement_id='T_TEST' for
select * from xxx where n
in
(select /*+Cardinality(t,0)*/to_number(column_value) from table(mytable(2, 4, 1, 3, 5)) t where rownum>0)
select * from table(dbms_xplan.display);
Plan hash value: 4112344697
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 30 (4)| 00:00:01 |
| 1 | NESTEDLOOPS | | 1 | 15 | 30 (4)| 00:00:01 |
| 2 | VIEW | VW_NSO_1| 1 | 13 | 29 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 2 | | |
| 4 | COUNT | | | | | |
|* 5 | FILTER | | | | | |
| 6 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
|* 7 | INDEX RANGE SCAN | IDX_XXX | 1 | 2 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(ROWNUM>0)
7 - access("N"="TO_NUMBER(COLUMN_VALUE)"
这个语句也可以这样写
select x.* from xxx x,
((select /*+Cardinality(t,0)*/to_number(column_value) s from table(mytable(2, 4, 1, 3, 5)) t where rownum>0)) m
where x.n=m.s
Plan hashvalue: 2981154701
-----------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 15 | 29 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 15 | 29 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 29 (0)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | FILTER | | | | | |
| 5 | COLLECTION ITERATOR CONSTRUCTORFETCH| | | | | |
|* 6 | INDEX RANGE SCAN | IDX_XXX | 1 | 2 | 0 (0)| 00:00:01|
-----------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
4- filter(ROWNUM>0)
6- access("X"."N"="M"."S")
去掉提示
explain plan set statement_id='T_TEST' for
select x.* from xxx x,
((select to_number(column_value) s from table(mytable(2, 4, 1, 3, 5)) t )) m
where x.n=m.s
select * from table(dbm