25. Improve efficiency with index
Index is a concept part of the table, used to improve the efficiency of retrieval data. In fact, Oracle uses a complex self-balance B-Tree structure. Usually, the query data is scanned than full menu. When Oracle is found The Oracle Optimizer will use index when querying the best path for the Update statement. The same use index is also increasing efficiency when connecting multiple answers. Another benefit of using an index is that it provides a uniqueness of primary keys. verification.
In addition to those LONG or long RAW data types, you can index almost all columns. Typically, use indexes in large tables. Of course, you will also find that when scanning small tables, use indexes can also improve efficiency.
Although the use of indexes can be improved in query efficiency, we must also pay attention to it. Index requires space
Storage, you also need regular maintenance, and whenever there is a record in the table, the index itself will be modified. This means that every record of INSERT, DELETE, UPDATE will pay more 4, 5 Supreme disk I / O. Because index requires additional storage space and processing, those unnecessary indexes will slow down the query response time.
Translator presses:
Regular reconstruction indexes are necessary.
Alter Index
26. Operation of the index
Oracle has two access modes for the index.
Index unique Scan (Index Unique Scan)
In most cases, the optimizer accesses Index through the WHERE clause.
E.g:
Table Lodging has two indexes: the uniqueness index lodging_pk, which is built on the LODGING column, and the non-unique index that is built on the Manager column Lodging $ Manager.
SELECT *
From lodging
WHERE LODGING = 'Rose Hill';
Internally, the above SQL will be divided into two steps. First, the LODGING_PK index will be accessed by the index unique scan, obtain the corresponding RowID, perform the next search method through the RowID access table.
If the column returned by the retrieved returned is included in the index column, Oracle will not perform the second step (via the RowID access table). Because the retrieval data is saved in the index, single access index can fully meet the query results.
The following SQL only needs Index Unique Scan operations.
SELECT LODGING
From lodging
WHERE LODGING = 'Rose Hill';
Index range query (Index Range Scan)
Suitable for two cases:
1. Based on a range of retrieves
2. Retrieval based on unique index
example 1:
SELECT LODGING
From lodging
WHERE LODGING LIKE 'M%';
WHERE clause conditions include a series of values, Oracle will query lodging_pk by inquiry through the index range. Since the index range query will return a set of values, its efficiency is the only scan
Low.
Example 2:
SELECT LODGING
From lodging
WHERE manager = 'bill Gates';
This SQL execution is divided into two steps, and the index range of Lodging $ Manager is queried (get all the requirements of the ROWID) and the next step of the RowID access table to get the value of the Lodging column. Because Lodging $ Manager is an unique index, The database cannot perform an index unique scan.
Since SQL returns the LodGing column, it does not exist in the Lodging $ Manager index, so an operation through the RowID access table is performed after the index range query.
In the WHERE clause, if the first character of the value corresponding to the column begins by wildcard (Wildcard), the index will not be adopted.
SELECT LODGING
From lodging
Where manager like '% hanman';
In this case, Oracle will use a full mete to scan.
