Plan hash value: 4014781130
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 32672 | 29 (0)| 00:00:01 |
| 1 | NESTEDLOOPS | | 8168 | 32672 | 29 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
|* 3 | INDEX RANGE SCAN | IDX_XXX | 1 | 2 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"."N"=TO_NUMBER(VALUE(KOKBF$)))
增加数据
insert into xxx
select r from
(
select rownum r from dual connect by level <= 100
)
where r>5
order by dbms_random.value(1,20)
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(dbms_xplan.display);
Plan hash value: 4014781130
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 32672 | 29 (0)| 00:00:01 |
| 1 | NESTEDLOOPS | | 8168 | 32672 | 29 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
|* 3 | INDEX RANGE SCAN | IDX_XXX | 1 | 2 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
3 - access("X"."N"=TO_NUMBER(VALUE(KOKBF$)))
增加提示
select x.* from xxx x,
((select /*+Cardinality(t,0)*/to_number(column_value) s from table(mytable(2, 4, 1, 3, 5)) t )) m
where x.n=m.s
explain plan set statement_id='T_TEST' for
select x.* from xxx x,
((select /*+Cardinality(t,0)*/to_number(column_value) s from table(mytable(2, 4, 1, 3, 5)) t )) m
where x.n=m.s
select * from table(dbms_xplan.display);
Plan hash value: 4014781130
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 29 (0)| 00:00:01 |
| 1 | NESTEDLOOPS | | 1 | 4 | 29 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
|* 3 | INDEX RANGE SCAN | IDX_XX