Oracle按照一定顺序提取数据

按照一定顺序提取数据研究 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

如果我们希望按照（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）

我们不能用select * from xxx where n in （2, 4, 1, 3, 5）

效率explain plan set statement_id="T_TEST" forselect * 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 columnsexplain plan set statement_id="T_TEST" forselect * 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）） mwhere x.n=m.s

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" forselect x.* from xxx x,（（select to_number（column_value） s from table（mytable（2, 4, 1, 3, 5）） t ）） mwhere 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 xxxselect r from（select rownum r from dual connect by level <= 100） where r>5order by dbms_random.value（1,20） explain plan set statement_id="T_TEST" forselect x.* from xxx x,（（select to_number（column_value） s from table（mytable（2, 4, 1, 3, 5）） t ）） mwhere 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 ）） mwhere x.n=m.s

explain plan set statement_id="T_TEST" forselect x.* from xxx x,（（select /*+Cardinality（t,0）*/to_number（column_value） s from table（mytable（2, 4, 1, 3, 5）） t ）） mwhere 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）

发现这不是我们需要的顺序 explain plan set statement_id="T_TEST" forselect * 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 xxxselect r from（select rownum r from dual connect by level <= 1000000） where r>1000order by dbms_random.value（1,20） explain plan set statement_id="T_TEST" forselect * 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" forselect * 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" forselect * 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" forselect * 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、执行计划…… 数据存放存放机制与高水位Oracle中主键约束跟唯一索引之间的关联关系Oracle半连接学习总结相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）