Oracle Implicit Convert

最新推荐文章于 2025-01-08 10:00:00 发布

转载最新推荐文章于 2025-01-08 10:00:00 发布 · 171 阅读

文章标签：

#数据库

本文探讨了在Oracle数据库中使用EXPLAIN PLAN时需要注意的三个关键点，特别是关于绑定变量如何影响执行计划以及隐式数据类型转换如何影响索引使用的案例研究。

/*绑定变量对执行计划的影响及隐式转换对索引的影响探索

There are three things to keep in mind about using EXPLAIN PLAN that make it susceptible to producing plan output that won’t match the actual execution plan:

1.EXPLAIN PLAN produces plans based on the environment at the moment you use it.

2.EXPLAIN PLAN doesn’t consider the datatype of bind variables (all binds are VARCHAR2).

3.EXPLAIN PLAN doesn’t “peek” at bind variable values.

[@more@]

/*绑定变量对执行计划的影响及隐式转换对索引的影响探索

There are three things to keep in mind about using EXPLAIN PLAN that make it susceptible to producing plan output that won’t match the actual execution plan:

1.EXPLAIN PLAN produces plans based on the environment at the moment you use it.

2.EXPLAIN PLAN doesn’t consider the datatype of bind variables (all binds are VARCHAR2).

3.EXPLAIN PLAN doesn’t “peek” at bind variable values.

HR@lipei> create table regions2

2 (region_id varchar2(10) primary key,

3 region_name varchar2(25));

Table created.

Elapsed: 00:00:00.46

HR@lipei> insert into regions2

2 select*from regions;

4 rows created.

Elapsed: 00:00:00.07

HR@lipei> commit;

Commit complete.

Elapsed: 00:00:00.01

HR@lipei> var regid number

HR@lipei> exec :regid :=1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

HR@lipei> set autotrace traceonly explain

HR@lipei> select*from regions2

2 where region_id=:regid;

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 4017480884

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

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

| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| REGIONS2 | 1 | 21 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C005654 | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("REGION_ID"=:REGID)

HR@lipei> set autotrace off

HR@lipei> select*from regions2

2 where region_id=:regid;

REGION_ID REGION_NAME

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

1 Europe

Elapsed: 00:00:00.00

HR@lipei> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7ub0nj0gc6vqy, child number 0

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

select*from regions2 where region_id=:regid

Plan hash value: 670750275

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

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

|* 1 | TABLE ACCESS FULL| REGIONS2 | 1 |

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

Predicate Information (identified by operation id):

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

1 - filter(TO_NUMBER("REGION_ID")=:REGID)

Note

-----

- dynamic sampling used for this statement

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.

Elapsed: 00:00:00.27

HR@lipei> create index number_ix on regions2(to_number(region_id));

Index created.

HR@lipei> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7ub0nj0gc6vqy, child number 0

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

select*from regions2 where region_id=:regid

Plan hash value: 856326169

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| REGIONS2 | 1 |

|* 2 | INDEX RANGE SCAN | NUMBER_IX | 1 |

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

Predicate Information (identified by operation id):

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

2 - access("REGIONS2"."SYS_NC00003$"=:REGID)

Note

-----

- dynamic sampling used for this statement

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

25 rows selected.

Elapsed: 00:00:00.11

Conclusion:the predicate must match the index definition exactly or else the index will not be used，这个测试中我定义的变量的数据类型是number，但table里面字段定义的是字符类型，所以导致了隐式转换，字符型被转换成number类型，因为number类型比string类型有更高的优先级。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1052607/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1052607/