--------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
3 - access("X"."N"=TO_NUMBER(VALUE(KOKBF$)))
再来看看
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)
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 byoperation id):
---------------------------------------------------
5 - filter(ROWNUM>0)
7 - access("N"="TO_NUMBER(COLUMN_VALUE)")
继续增加数据
----------------------------------------------
select * from table(dbms_xplan.display);
insert into xxx
select r from
(
select rownum r from dual connect by level <= 1000000
)
where r>1000
order by dbms_random.value(1,20)
explain plan set statement_id='T_TEST' for
select * from xxx where n member of in_list2('2, 4, 1, 3, 5')
Plan hash value: 1759293582
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XXX | 1| 2 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"MEMBER OF"IN_LIST2"('2,4, 1, 3, 5'))
analyze table xxx compute statistics for table for all indexes for all columns
explain plan set statement_id='T_TEST' for
select * from xxx where n member of in_list2('2, 4, 1, 3, 5')
47s
select * from table(dbms_xplan.display);
Plan hash value: 1759293582
---