按照一定顺序提取数据研究
create table xxx (n number);
insert into xxx values(1);
insert into xxx values(2);
insert into xxx values(3);
insert into xxx values(4);
insert into xxx values(5);
commit;
select * from xxx
N
1
2
3
4
5
如果我们希望按照（2, 4, 1, 3, 5） 提取数据可以
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)
N
2
4
1
3
5
我们不能用
select * from xxx where n in (2, 4, 1, 3, 5)
N
1
2
3
4
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)
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 all columns
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
N
2
4
1
3
5
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(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 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 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
N
2
4
1
3
5
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_XXX | 1 | 2 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
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)
N
1
2
4
5
3
发现这不是我们需要的顺序
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 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
--------------------------------------------------------------------------
| 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、 执行计划……
数据存放存放机制与高水位