- 掛牌上市企業
- 60秒人工響應
- 99.99%連通率
- 7*24h人工
- 故障100倍補償
Oracle按照一定順序提取數據 |
| 發布時間: 2012/8/30 16:48:28 |
|
按照一定順序提取數據研究 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 如果我們希望按照（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 我們不能用select * from xxx where n in (2, 4, 1, 3, 5) N 效率 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
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 <= 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 N
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
發現這不是我們需要的順序
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
-------------------------------------------------------------------------- | 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、 執行計劃…… 數據存放存放機制與高水位 本文出自：億恩科技【www.artduck.net】 |
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
