oracle 提取顺序,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、 执行计划……

数据存放存放机制与高水位