观点:
1)如果字段类型是数字型,那么在做等值条件查询的时候,即使=后面的值给的是字符型,也不会发生隐式数据类型转换。
test表是我基于dba_objects建立的一张表。object_id是数值型,且是索引列,索引类型为普通索引。
以下为测试过程。
————注意Predicate Information (identified by operation id)部分,没有发生转换
select * from test where object_id='431'
通过v$sql得到这个语句的SQLID.
SQL> select * from table(dbms_xplan.display_cursor('6pgd93uz7vamb',null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6pgd93uz7vamb, child number 0
-------------------------------------
select * from test where object_id='431'
Plan hash value: 3513931890
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 354 | 4
|* 2 | INDEX RANGE SCAN | TEST_IND_OBJECT_ID | 2 | | 1
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=431)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement
2)如果字段类型为字符型,那么在做等值查询的时候,如果=后面给的是数字型的,那么会发生隐式数据类型转换。(注意Predicate Information (identified by operation id):部分,发生了隐式数据类型转换TO_NUMBER("ID")=1)
X表的id字段为字符型。
SQL> select * from x where id=1;
执行计划
----------------------------------------------------------
Plan hash value: 2941724873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 112 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| X | 16 | 112 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=1)
Note
-----
- dynamic sampling used for this statement
1)如果字段类型是数字型,那么在做等值条件查询的时候,即使=后面的值给的是字符型,也不会发生隐式数据类型转换。
test表是我基于dba_objects建立的一张表。object_id是数值型,且是索引列,索引类型为普通索引。
以下为测试过程。
————注意Predicate Information (identified by operation id)部分,没有发生转换
select * from test where object_id='431'
通过v$sql得到这个语句的SQLID.
SQL> select * from table(dbms_xplan.display_cursor('6pgd93uz7vamb',null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6pgd93uz7vamb, child number 0
-------------------------------------
select * from test where object_id='431'
Plan hash value: 3513931890
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 354 | 4
|* 2 | INDEX RANGE SCAN | TEST_IND_OBJECT_ID | 2 | | 1
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=431)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement
2)如果字段类型为字符型,那么在做等值查询的时候,如果=后面给的是数字型的,那么会发生隐式数据类型转换。(注意Predicate Information (identified by operation id):部分,发生了隐式数据类型转换TO_NUMBER("ID")=1)
X表的id字段为字符型。
SQL> select * from x where id=1;
执行计划
----------------------------------------------------------
Plan hash value: 2941724873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 112 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| X | 16 | 112 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=1)
Note
-----
- dynamic sampling used for this statement
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-662236/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-662236/