测试索引访问途径（number与varchar2的隐式转换）_把number设置为varchar类型,如何设置number索引-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangqian24/article/details/11251143

本文通过具体案例演示了如何创建索引并利用索引来优化SQL查询效率，包括不同类型的索引扫描及其对查询性能的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> desc testidx
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(128)

SQL> set autot on
SQL> set lines 200
SQL> run
1* select * from testidx where id='4' ID字段是number类型

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
4 a

Execution Plan
----------------------------------------------------------
Plan hash value: 1351909795

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| testidx | 1 | 5 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ID | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("ID"=4)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> create index idx_name on testidx(name);

Index created.

SQL> select * from testidx;

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
4 a
3 a
2 a

Execution Plan
----------------------------------------------------------
Plan hash value: 1926688018

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 15 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| testidx | 3 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> select * from testidx where name='a';

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
4 a
3 a
2 a

Execution Plan
----------------------------------------------------------
Plan hash value: 2415424842

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

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

2 - access("NAME"='a')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> select * from testidx;

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
4 a
3 a
2 a

Execution Plan
----------------------------------------------------------
Plan hash value: 1926688018

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 15 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| testidx | 3 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> update testidx set name=100 where id=2;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3382260835

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | UPDATE | testidx | | | | |
|* 2 | INDEX UNIQUE SCAN| PK_ID | 1 | 5 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=2)

Statistics
----------------------------------------------------------
3 recursive calls
5 db block gets
6 consistent gets
0 physical reads
776 redo size
837 bytes sent via SQL*Net to client
793 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> update testidx set name=103 where id=3;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3382260835

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | UPDATE | testidx | | | | |
|* 2 | INDEX UNIQUE SCAN| PK_ID | 1 | 5 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=3)

Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
1 consistent gets
0 physical reads
728 redo size
837 bytes sent via SQL*Net to client
793 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> update testidx set name=13 where id=4;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3382260835

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | UPDATE | testidx | | | | |
|* 2 | INDEX UNIQUE SCAN| PK_ID | 1 | 5 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=4)

Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
2 consistent gets
0 physical reads
776 redo size
837 bytes sent via SQL*Net to client
792 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> commit;

Commit complete.

SQL> select * from testidx;

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
4 13
3 103
2 100

Execution Plan
----------------------------------------------------------
Plan hash value: 1926688018

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 15 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| testidx | 3 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
667 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> select * from testidx where name=103;

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
3 103

Execution Plan
----------------------------------------------------------
Plan hash value: 1926688018

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

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

1 - filter(TO_NUMBER("NAME")=103)
此处发生了隐式转换走的全表扫描

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
590 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select * from testidx where name='103';

ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
3 103

Execution Plan
----------------------------------------------------------
Plan hash value: 2415424842

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

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

2 - access("NAME"='103')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>