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)
效率
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)
Plan hash value: 2336544415
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 33 (4)| 00:00:01 |
|* 1 | HASH JOINSEMI | | 1 | 26 | 33 (4)| 00:00:01 |
| 2 | TABLEACCESS FULL | XXX | 5 | 65 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1| 1 | 13 | 29 (0)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | FILTER | | | | | |
| 6 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"="TO_NUMBER(COLUMN_VALUE)")
5 - filter(ROWNUM>0)
Note
-----
- dynamic samplingused for this statement
create index idx_xxx on xxx(n)
Plan hashvalue: 4112344697
-------------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 26 | 30 (4)| 00:00:01|
| 1 | NESTED LOOPS | | 1 | 26 | 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 CONSTRUCTORFETCH| | | | | |
|* 7 | INDEX RANGE SCAN | IDX_XXX | 1| 13 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 -filter(ROWNUM>0)
7 -access("N"="TO_NUMBER(COLUMN_VALUE)")
Note
-----
- dynamic sampling used forthis statement
analyze table xxx compute statistics for table for all indexes for