Use the indexed misunderstanding: establish an index for all columns
We know that establishing an index is to improve the efficiency of the query, but it should also be noted that the index increases the price of the DML operation (INSERT, UPDATE, DELETE), and if there are too many indexes, then most indexes It is not to be used at all, and on the other hand, we maintain these unused uses, so it is necessary to significantly reduce the performance of the system. So, the index is not, the better, but it is necessary to use it just right.
For example, some columns are impossible to use existing indexes (such as some composite indexes) because of the functions (see "Function Index" in detail), then a separate function index must be established. If this is said Function indexes will be applied (only in several special SQLs), we can try to rewrite queries without establishing and maintaining the function index, for example:
C: /> SQLPLUS DEM
O / 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 (10) 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) of 'EMP'
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
3 Consistent Gets
0 Physical READS
0 redo size
328 BYTES SENT VIA SQL * NET to Client
372 BYtes Received Via SQL * Net from Client1 SQL * NET ROUNDTRIPS TO / FROM Clom Clom Clom Clism
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 hidate 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 Get 1 Consistent Gets 0 Physical READS 0 redo size 328 BYTES SENT VIA SQL * NET to Client 372 BYtes Received Via SQL * Net from Cliant 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) of 'EMP' Statistics -------------------------------------------------- ------------ 0 Recursive Calls 0 DB Block Get 3 Consistent Gets 0 Physical READS 0 redo size 328 BYTES SENT VIA SQL * NET to Client 372 BYtes Received Via SQL * Net from Cliant 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 success 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 Get 1 Consistent Gets 0 Physical READS 0 redo size 328 BYTES SENT VIA SQL * NET to Client 372 BYtes Received Via SQL * Net from Cliant 1 SQL * NET ROUNDTRIPS TO / FROM Client 0 Sorts (Memory) 0 Sorts (Disk) 0 Rows Processed SQL> Note, pay attention to the accuracy of the field in the field during the transition, such as 'YYYYMMMYY', or 'YYYYMMDDHH24MISS' 3, TO_DATE function See the method above 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) of 'DEPT' Statistics -------------------------------------------------- ------------ 0 Recursive Calls 0 DB Block Get 7 Consistent Gets 0 Physical READS 0 redo size 221 BYTES SENT VIA SQL * NET to Client 372 BYtes Received Via SQL * Net from Cliant 1 SQL * NET ROUNDTRIPS to / from Clom Clom Clom Clom Clom Clom Clom Clom CLIENT0 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 Get 1 Consistent Gets 0 Physical READS 0 redo size 221 BYTES SENT VIA SQL * NET to Client 372 BYtes Received Via SQL * Net from Cliant 1 SQL * NET ROUNDTRIPS TO / FROM Client 0 Sorts (Memory) 0 Sorts (Disk) 0 Rows Processed SQL> Usually, in order to balance the efficiency of the query and the efficiency of DML, we have to analyze the application, find out more frequent, less content, such as varchar2 (1000) is not suitable for establishing an index, and varchar2 (10) is relatively It is suitable for the column that is suitable for establishing an index, reasonable establishment index, such as sometimes we want to establish a composite index, sometimes we prefer to establish a single-button index. In fact, Oracle's indexes have a lot of topics, such as the use of indexes, Oracle 9i's SKIP SCAN, etc. In the next discussion, we will explain in detail.
