function base index

最新推荐文章于 2021-10-18 16:00:16 发布

Kevin_ora

最新推荐文章于 2021-10-18 16:00:16 发布

阅读量955

点赞数

分类专栏： oracle 文章标签： index ORACLE dba database sql

本文链接：https://blog.csdn.net/ora_raymond/article/details/22892461

版权

oracle 专栏收录该内容

56 篇文章 0 订阅

订阅专栏

create table t1(id number, idd varchar2(10));
insert into t1 values( 1, '11');
insert into t1 values(2, '22');


commit;




SQL> create index idx_idd_t1 on t1(to_number(idd));


Index created.


SQL> select * from t1 where idd='11';


     ID IDD
---------- ----------
      1 11




Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    20 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1      |     1 |    20 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("IDD"='11')


Note
-----
   - dynamic sampling used for this statement (level=2)


SQL>  select * from t1 where to_number(idd)=11;


     ID IDD
---------- ----------
      1 11




Execution Plan
----------------------------------------------------------
Plan hash value: 1463967975


------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     1 |    33 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |    33 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN         | IDX_IDD_T1 |     1 |      |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access(TO_NUMBER("IDD")=11)


Note
-----
   - dynamic sampling used for this statement (level=2)






SQL> select * from  t1 where to_number(idd)=to_number('11');


     ID IDD
---------- ----------
      1 11




Execution Plan
----------------------------------------------------------
Plan hash value: 1463967975


------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     1 |    33 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |    33 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN         | IDX_IDD_T1 |     1 |      |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access(TO_NUMBER("IDD")=11)


Note
-----
   - dynamic sampling used for this statement (level=2)






SQL> select * from  t1 where to_number(idd)=to_number('11');


     ID IDD
---------- ----------
      1 11




Execution Plan
----------------------------------------------------------
Plan hash value: 1463967975


------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     1 |    33 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |    33 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN         | IDX_IDD_T1 |     1 |      |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access(TO_NUMBER("IDD")=11)


Note
-----
   - dynamic sampling used for this statement (level=2)