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.
– / / I see this content to SQL tuning – guide. PDF. 21 c F31828-03 another awarding 2020. My tests at the time didn’t want to be like this. –// It was my tests:
1. Environment: SCOTTtest01p> ver1
PORT_STRING VERSION BANNER CON_ID
Oracle Database 12C Enterprise Edition Release 12.2.0.1.0-64bit Production
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; select * from dept where deptno=10 order by 2; –// In another way:
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=’ dept ‘order by 1,2; Select * from dept where dname=’ dept ‘order by 2,1; Select * from dept where dname=’ dept ‘order by 2,3; Select * from dept where dname=’ dept ‘order by 3,2;
–// is just as effective, but generates a lot of child cursors.