Six myths about using indexes: Indexing columns

The error of indexing using indexes six: indexing all columns
We know that indexing is an effort to improve query efficiency, but it should also be noted that indexes increase the cost of DML operations (INSERT, UPDATE, delete), and that most indexes are not used at all if there are too many indexes in them. On the other hand, we maintain these are not used, so we have to significantly reduce the performance of the system. Therefore, the more the index is not the better, but the right to use.



For example, some columns, because of the use of functions, it is not possible to use an existing index (such as some composite index) (see the previous "function index" in detail), then a separate function index must be established, if that function index is rarely applied (only in a few special SQL), We can try to rewrite the query without establishing and maintaining that function index, for example:

C:\>sqlplus Demo/demo



Sql*plus:release 9.2.0.1.0-production on Sun Oct 17 07:47:30 2004



Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.





Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0-production

With the OLAP and Oracle Data Mining options

Jserver Release 9.2.0.1.0-production



Sql> Show User

User is "demo"



Sql> desc EMP

Name Type Nullable Default Comments

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

EMPNO Number (4)

Ename VARCHAR2 (Y)

JOB VARCHAR2 (9) Y

MGR Number (4) Y

HireDate DATE Y

SAL number (7,2) Y

COMM number (7,2) Y

DEPTNO Number (2) Y



Sql> CREATE INDEX EMP_ID3 on EMP (hiredate);



Index created





1,trunc function

Sql> Select Empno,ename,deptno from emp where trunc (hiredate) = ' 2004-01-01 ';



No rows selected





Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' EMP '









Statistics

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

0 Recursive calls

0 db Block gets

3 Consistent gets

0 physical Reads

0 Redo Size

328 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Convert the above query to:

Sql> Select Empno,ename,deptno from emp

2 where HireDate >= to_date (' 2004-01-01 ', ' yyyy-mm-dd ')

3 and Hiredate<to_date (' 2004-01-01 ', ' yyyy-mm-dd ') +0.999;



No rows selected





Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' EMP '

2 1 INDEX (RANGE SCAN) of ' emp_id3 ' (non-unique)









Statistics

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

0 Recursive calls

0 db Block gets

1 consistent gets

0 physical Reads

0 Redo Size

328 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql>





2,to_char function

Sql> Select Empno,ename,deptno from emp

2 where To_char (HireDate, ' yyyy-mm-dd ') = ' 2003-09-05 ';



No rows selected





Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' EMP '









Statistics

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

0 Recursive calls

0 db Block gets

3 Consistent gets

0 physical Reads

0 Redo Size

328 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql> Select Empno,ename,deptno from emp

2 where Hiredate=to_date (' 2003-09-05 ', ' yyyy-mm-dd ');



No rows selected





Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' EMP '

2 1 INDEX (RANGE SCAN) of ' emp_id3 ' (non-unique)









Statistics

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

0 Recursive calls

0 db Block gets

1 consistent gets

0 physical Reads

0 Redo Size

328 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql>

Note that the conversion takes note of the precision of the field in the database, such as ' yyyymmyy ', or ' Yyyymmddhh24miss '



3,to_date function

See the above method





4,substr function

Sql> DESC Dept

Name Null? Type

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

DEPTNO Number (2)

Dname VARCHAR2 (14)

LOC VARCHAR2 (13)



Sql> CREATE INDEX DEPT_ID1 on dept (Dname);



Index created.



Sql> Select Dname from dept where substr (dname,1,3) = ' abc ';



No rows selected





Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' DEPT '









Statistics

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

0 Recursive calls

0 db Block gets

7 Consistent gets

0 physical Reads

0 Redo Size

221 Bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql> Select Dname from dept where dname like ' abc% ';



No rows selected





Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 INDEX (RANGE SCAN) of ' dept_id1 ' (non-unique)









Statistics

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

0 Recursive calls

0 db Block gets

1 consistent gets

0 physical Reads

0 Redo Size

221 Bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql>



In general, in order to balance the efficiency of the query and the efficiency of DML, we have to carefully analyze the application, to find a relatively high frequency, less field content (such as VARCHAR2 (1000) is not suitable for indexing, and VARCHAR2 (10) relatively suitable for indexing) of the column, reasonable to establish the index , like sometimes we want to build a composite index, and sometimes we prefer to build a single key index.

In fact, Oracle's index has many topics, such as monitoring the use of indexes, Oracle 9i launched the skip scan, and so on, in the next discussion, we will elaborate on these topics.