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 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 <= 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
 
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 <= 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、 執行計劃……

數據存放存放機制與高水位

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