- 掛牌上市企業(yè)
- 60秒人工響應(yīng)
- 99.99%連通率
- 7*24h人工
- 故障100倍補(bǔ)償
Oracle按照一定順序提取數(shù)據(jù) |
| 發(fā)布時間: 2012/8/30 16:48:28 |
|
按照一定順序提取數(shù)據(jù)研究 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） 提取數(shù)據(jù)可以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$)))
增加數(shù)據(jù)
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
發(fā)現(xià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)")
繼續(xù)增加數(shù)據(jù) ---------------------------------------------- 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、 當(dāng)數(shù)據(jù)量變化很大后，分析變得非常很重要； 2、 不同的sql寫法，執(zhí)行計(jì)劃不同，不經(jīng)影響效率，還影響其功能； 3、 不能表面理解，需要仔細(xì)測試； 4、 執(zhí)行計(jì)劃…… 數(shù)據(jù)存放存放機(jī)制與高水位 本文出自：億恩科技【www.ibaoshan.net】 服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM] |
0371-60135900
京公網(wǎng)安備41019702002023號
香港服務(wù)器租用
美國服務(wù)器租用
電信骨干機(jī)房
聯(lián)通骨干機(jī)房
