| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 597 (26)| 00:00:08 |
|* 1 | TABLE ACCESS FULL|XXX | 50000 | 195K| 597 (26)| 00:00:08 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1 - filter("N"MEMBER OF"IN_LIST2"('2, 4, 1, 3, 5'))
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 | 17 | 32 (4)| 00:00:01 |
| 1 | NESTEDLOOPS | | 1 | 17 | 32 (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 | 4 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
5 - filter(ROWNUM>0)
7 - access("N"="TO_NUMBER(COLUMN_VALUE)")
delete from xxx where n>100
explain plan set statement_id='T_TEST' for
select * from xxx where n member of in_list2('2, 4, 1, 3, 5')
select * from table(dbms_xplan.display);
Plan hash value: 1759293582
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 597 (26)| 00:00:08 |
|* 1 | TABLE ACCESS FULL|XXX | 50000 | 195K| 597 (26)| 00:00:08 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"MEMBER OF"IN_LIST2"('2, 4, 1, 3, 5'))
1、 当数据量变化很大后，分析变得非常很重要；
2、 不同的sql写法，执行计划不同，不经影响效率，还影响其功能；
3、 不能表面理解，需要仔细测试；
4、 执行计划……
数据存放存放机制与高水位