Test Environment Simulation: runtime; createtablet_col_type (idvarchar2 (20), col2varchar2 (20), col3varchar2 (20); runtime, abc, efgfromdualconnectbylevel10000; commit; createindexidx_idont_c
Test Environment Simulation: SQL drop table t_col_type purge; create table t_col_type (id varchar2 (20), col2 varchar2 (20), col3 varchar2 (20); insert into t_col_type select rownum, abc, efg from dual connect by level = 10000; commit; create index idx_id on t_c
Test Environment Simulation:
SQL> drop table t_col_type purge;
Create table t_col_type (id varchar2 (20), col2 varchar2 (20), col3 varchar2 (20 ));
Insert into t_col_type select rownum, 'abc', 'efg' from dual connect by level <= 10000;
Commit;
Create index idx_id on t_col_type (id );
Set linesize 1000
Set autotrace traceonlydrop table t_col_type purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from t_col_type where id = 6;
Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------------
| 0 | select statement | 1 | 36 | 8 (0) | 00:00:01 |
| * 1 | table access full | T_COL_TYPE | 1 | 36 | 8 (0) | 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (TO_NUMBER ("ID") = 6)
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
64 consistent gets
0 physical reads
0 redo size
640 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
It seems a bit strange here. index [create index idx_id on t_col_type (id);] is clearly created, but why is it not used?
--- Check whether the table has an index
SQL> select index_name, table_name, column_name from all_ind_columns where table_name = 't_ COL_TYPE ';
INDEX_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
IDX_ID
T_COL_TYPE
ID
---- View the table structure
SQL> desc scott. T_COL_TYPE
Name Null? Type
-----------------------------------------------------------------------------
ID VARCHAR2 (20) ---------- note the character type here
COL2 VARCHAR2 (20)
COL3 VARCHAR2 (20)
Pay attention to the predicates in the execution plan again:
1-filter (TO_NUMBER ("ID") = 6) ---------- type conversion occurs here
Therefore, the existing indexes cannot be used in the execution plan. How can we make the indexes take place correctly?
Select * from t_col_type where id = '6'; ------ note that the difference here is enclosed in single quotes, indicating that this is a character,
Execution Plan
----------------------------------------------------------
Plan hash value: 3998173245
Bytes ------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 36 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | T_COL_TYPE | 1 | 36 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IDX_ID | 1 | 1 (0) | 00:00:01 |
Bytes ------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("ID" = '6 ')
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive cballs
0 db block gets
39 consistent gets
1 physical reads
0 redo size
640 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed