Oracle按照一定顺序提取数据

文章由LinuxBoy分享于2019-03-30 02:03:38热评（114）

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、执行计划……

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

推荐文章：

评论暂时关闭

最新Linux教程教程

Linux头条