orade by数据的整理If a statement uses an ORDER BY clause, then th

If a statement uses an ORDER BY clause, then the database does not perform literal replacement in the clause because it is not semantically correct to consider the constant column number as a literal. The column number in the ORDER BY clause affects the query plan and execution, so the database cannot share two cursors having different column numbers.

--//我看到的这个内容来之sql-tuning-guide.pdf. 21c F31828-03 December 2020.我当时的测试就是不想上面说的情况. --//以前是我的测试:

1.环境: SCOTTtest01p> ver1

PORT_STRING VERSION BANNER CON_ID

IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2.手游测试: SCOTTtest01p> alter session set cursor_sharing=force ; Session altered.

SCOTTtest01p> select * from dept where deptno=10 order by 1;

DEPTNO DNAME LOC

10 ACCOUNTING           NEW YORK

SCOTTtest01p> dpc '' '' PLAN_TABLE_OUTPUT

SQL_ID f2jf1h54abkzu, child number 0

select * fromwww.diuxie.com dept where deptno=:"SYS_B_0" order by :"SYS_B_1" Plan hash value: 2852011669

| 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |

Query Block Name / Object Alias (identified by operation id):

1 - SEL $9FB2EC53 / DEPTSEL$ 1 2 - SEL $9FB2EC53 / DEPTSEL$ 1 Predicate Information (identified by operation id):

2 - access("DEPTNO"=:SYS_B_0)

--//替换发生,并没有链接介绍的情况. --//执行如下也是一样 select * from dept where deptno=10 order by 2; --//换一种方式:

SCOTTtest01p> select * from dept where dname='ACCOUNTING' order by 2;

DEPTNO DNAME LOC

10 ACCOUNTING           NEW YORK

SCOTTtest01p> dpc '' '' PLAN_TABLE_OUTPUT

SQL_ID 087vcgdqz87g9, child number 0

select * from dept where dname=:"SYS_B_0" order by :"SYS_B_1"如果相对electron有更多直观理解的, 也可以参考其格式如下:

手游：www.diuxie.com

Plan hash value: 3383998547

| 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |

SCOTTtest01p> select * from dept where dname='ACCOUNTING' order by 1;

DEPTNO DNAME LOC

10 ACCOUNTING           NEW YORK

SCOTTtest01p> dpc '' '' PLAN_TABLE_OUTPUT

SQL_ID 087vcgdqz87g9, child number 1

select * from dept where dname=:"SYS_B_0" order by :"SYS_B_1" Plan hash value: 3103054919

| 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | Query Block Name / Object Alias (identified by operation id): 1 - SEL $1 / DEPTSEL$ 1 2 - SEL $1 / DEPTSEL$ 1 Predicate Information (identified by operation id):

1 - filter("DNAME"=:SYS_B_0)

--//还是有效. select * from dept where dname='ACCOUNTING' order by 1,2; select * from dept where dname='ACCOUNTING' order by 2,1; select * from dept where dname='ACCOUNTING' order by 2,3; select * from dept where dname='ACCOUNTING' order by 3,2;

--//一样有效,自不过产生许多子光标罢了.