管理版的问题 不大明白,来这里讨论一下:
create table product
(
pro_id number,
pro_name varchar2(20),
pro_info varchar2(60),
pro_price number,
pro_user varchar2(20)
);
alter table PRODUCT add constraint PRI_PRO_ID primary key (PRO_ID);
insert into product values(28985,'SYS','/1000e8d1_LinkedHashMapValueIt',30137,'JAVA CLASS');
insert into product values(28986,'PUBLIC','/1000e8d1_LinkedHashMapValueIt',30138,'SYNONYM');
insert into product values(15935,'SYS','/1005bd30_LnkdConstant',17087,'JAVA CLASS');
insert into product values(15936,'PUBLIC','/1005bd30_LnkdConstant',17089,'SYNONYM');
insert into product values(15937,'PUBLIC','/1005bd30_LnkdConstant',17090,'SYNONY');
insert into product values(15938,'PUBLIC','/1005bd30_LnkdConstant',17091,'SYNONYMM');
insert into product values(15939,'PUBLIC','/1005bd30_LnkdConstant',17092,'SYNONYMZ');
insert into product values(15940,'PUBLIC','/1005bd30_LnkdConstant',17093,'SYNONYMg');
MYDB@MYDB10G >select rownum,a.* from (select * from product ORDER BY pro_info) a;
ROWNUM PRO_ID PRO_NAME PRO_INFO PRO_PRICE PRO_USER
---------- ---------- -------------------- ------------------------------------------------------------ ---------- --------------------
1 28985 SYS /1000e8d1_LinkedHashMapValueIt 30137 JAVA CLASS
2 28986 PUBLIC /1000e8d1_LinkedHashMapValueIt 30138 SYNONYM
3 15937 PUBLIC /1005bd30_LnkdConstant 17090 SYNONY
4 15938 PUBLIC /1005bd30_LnkdConstant 17091 SYNONYMM
5 15939 PUBLIC /1005bd30_LnkdConstant 17092 SYNONYMZ
6 15940 PUBLIC /1005bd30_LnkdConstant 17093 SYNONYMg
7 15935 SYS /1005bd30_LnkdConstant 17087 JAVA CLASS
8 15936 PUBLIC /1005bd30_LnkdConstant 17089 SYNONYM
已选择8行。
MYDB@MYDB10G >select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a0dmy1cd3qg94, child number 1
-------------------------------------
select rownum,a.* from (select * from product ORDER BY pro_info) a
Plan hash value: 722800309
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 1 | COUNT | | 1 | | 8 |00:00:00.01 | 7 | | | |
| 2 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 7 | | | |
| 3 | SORT ORDER BY | | 1 | 8 | 8 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS FULL| PRODUCT | 1 | 8 | 8 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
已选择19行。
MYDB@MYDB10G >select rownum , a.* from (select * from product ORDER BY pro_info) a where rownum<21;
ROWNUM PRO_ID PRO_NAME PRO_INFO PRO_PRICE PRO_USER
---------- ---------- -------------------- ------------------------------------------------------------ ---------- --------------------
1 28985 SYS /1000e8d1_LinkedHashMapValueIt 30137 JAVA CLASS
2 28986 PUBLIC /1000e8d1_LinkedHashMapValueIt 30138 SYNONYM
3 15935 SYS /1005bd30_LnkdConstant 17087 JAVA CLASS
4 15936 PUBLIC /1005bd30_LnkdConstant 17089 SYNONYM
5 15937 PUBLIC /1005bd30_LnkdConstant 17090 SYNONY
6 15938 PUBLIC /1005bd30_LnkdConstant 17091 SYNONYMM
7 15939 PUBLIC /1005bd30_LnkdConstant 17092 SYNONYMZ
8 15940 PUBLIC /1005bd30_LnkdConstant 17093 SYNONYMg
已选择8行。
MYDB@MYDB10G >select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a1ksua9f6w984, child number 1
-------------------------------------
select rownum , a.* from (select * from product ORDER BY pro_info) a where rownum<21
Plan hash value: 3153827937
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 8 |00:00:00.01 | 7 | | | |
| 2 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 7 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 8 | 8 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS FULL | PRODUCT | 1 | 8 | 8 |00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<21)
3 - filter(ROWNUM<21)
Note
-----
- dynamic sampling used for this statement
已选择25行。
select rownum , a.* from (select * from product ORDER BY pro_info) a ;
select rownum , a.* from (select * from product ORDER BY pro_info) a where rownum<21;
两个语句得到的结果中 pro_info相同行,顺序不一样(红色部分)~
我觉得对于pro_info相同的行,返回的顺序应该是fetch的顺序,那么看执行计划
SORT ORDER BY 和 SORT ORDER BY STOPKEY 这两种排序,是不是fetch 结果集的顺序不同呢?
[本帖最后由 zergduan 于 2009-11-20 15:59 编辑]