共计 2437 个字符，预计需要花费 7 分钟才能阅读完成。
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
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
| 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″
Plan hash value: 3383998547
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
| 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
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
| 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;
–// 一样无效, 自不过产生许多子光标罢了.
