文章內(nèi)容

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

1

2

3

4

5

如果我們希望按照（2, 4, 1, 3, 5）提取數(shù)據(jù)可以select * from xxx where n

(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

(select /*+Cardinality(t,0)*/to_number(column_value) from table(mytable(2, 4, 1, 3, 5)) t where rownum>0)

Plan hash value: 2336544415

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

(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

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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

(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

發(fā)現(xiàn)這不是我們需要的順序

explain plan set statement_id='T_TEST' for

select * from xxx where n

(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

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

(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

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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、當數(shù)據(jù)量變化很大后，分析變得非常很重要；

2、不同的sql寫法，執(zhí)行計劃不同，不經(jīng)影響效率，還影響其功能；

3、不能表面理解，需要仔細測試；

4、執(zhí)行計劃……

數(shù)據(jù)存放存放機制與高水位

本文出自：億恩科技【mszdt.com】

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]

上一篇 >> Oracle中主鍵約束跟唯一索引之間的關聯(lián)關系
下一篇 >> Oracle中的NULL和空字符串

无码视频在线观看,99人妻,国产午夜视频,久久久久国产一级毛片高清版新婚

服務器租用

服務器托管

機柜批發(fā)

云服務器

建站俠

空間/域名

安全保姆

幫助類別

幫助中心

文章內(nèi)容

Oracle按照一定順序提取數(shù)據(jù)

同類文章

億恩公告

在線客服