Oracle游标名称使用参数,游标名称后面加上参数，性能大幅提高,这是什么原因？...

刚做了一个实验，发现如果参数名称跟字段名一样的话，就相当于没有加条件，数据库会把参数当字段处理。这一点通过索引快速全扫描和索引范围扫描验证。

drop table test purge;

create table test as select * from dba_objects;

create index ind_t_object_id on test(object_id) nologging;

exec dbms_stats.gather_table_stats(user,'test',cascade => true);

create or replace procedure p_test(object_id number) as

V_COUNT    NUMBER;

begin

select count(object_id) into V_COUNT from test t where t.object_id = object_id;

end;

call p_test(10);

select * from v$sql s

where s.SQL_TEXT like '%SELECT COUNT(OBJECT_ID) FROM TEST T%';

select hash_value, child_number, sql_text from v$sql s

where s.SQL_ID = 'cd9w8ccj5y56f';

select * from table(dbms_xplan.display_cursor(576656590, 0, 'advanced'));

HASH_VALUE  576656590, child number 0

-------------------------------------

SELECT COUNT(OBJECT_ID) FROM TEST T WHERE T.OBJECT_ID = OBJECT_ID

Plan hash value: 3841213438

-----------------------------------------------------------------------------------------

| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                 |       |       |    57 (100)|          |

|   1 |  SORT AGGREGATE       |                 |     1 |     5 |            |          |

|*  2 |   INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 91335 |   445K|    57   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

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

-------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("T"."OBJECT_ID" IS NOT NULL)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=0) COUNT("OBJECT_ID")[22]

2 - "OBJECT_ID"[NUMBER,22]

create or replace procedure p_test(v_object_id number) as

V_COUNT    NUMBER;

begin

select count(object_id) into V_COUNT from test t where t.object_id = v_object_id;

end;

call p_test(10);

select * from v$sql s

where s.SQL_TEXT like '%SELECT COUNT(OBJECT_ID) FROM TEST T%';

select hash_value, child_number, sql_text from v$sql s

where s.SQL_ID = '88mfj2xffpjxx';

select * from table(dbms_xplan.display_cursor(1558890429, 0, 'advanced'));

HASH_VALUE  1558890429, child number 0

--------------------------------------

SELECT COUNT(OBJECT_ID) FROM TEST T WHERE T.OBJECT_ID = :B1

Plan hash value: 824481342

-------------------------------------------------------------------------------------

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |       |       |     1 (100)|          |

|   1 |  SORT AGGREGATE   |                 |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IND_T_OBJECT_ID |     1 |     5 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

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

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / T@SEL$1

Peeked Binds (identified by position):

--------------------------------------

1 - :B1 (NUMBER): 10

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T"."OBJECT_ID"=:B1)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=0) COUNT("OBJECT_ID")[22]

2 - "T"."OBJECT_ID"[NUMBER,22