The index is an auxiliary object established on a column or multiple columns of the table to speed up the data in the access table;
The data structure of Oracle Storage Index is b * tree, and the bitmap index is also true, but it is only a different b * number index of the leaf node;
The index consists of root nodes, branch nodes, and leaf nodes. The higher-level index block contains the index data of the subordinate index block, and the leaf node contains index data and determines the RowID of the actual location.
Use of indexes
Accelerate query speed
Reduce I / O operation
Eliminate disk sort
When to use an index
Number of records returned by query
Ranking table <40%
Non-routing table <7%
More debris (frequently, delete)
Index species
Non-unique index (most common)
Single index
Bitmap index
Partially have a prefix partition index
Partially unproved partition index
Global prefix partition index
Hash partition index
Function-based index
The guidelines for managing indexes create an index after inserting data in the table
. After inserting or loading data with SQL * LoAder or Import tool, establish an index is more effective;
Index correct table and column
. Regular retrieval of 40% or non-routing table 7% of the row, it is recommended to build an index;
. In order to improve multi-table associations, the index column is used for linkage;
. The value in the column is relatively unique;
. Value range (large: b * tree index, small: bit map index);
. Date type is generally suitable for function-based indexing;
. There are many null values in the column, which is not suitable for establishing an index.
Arrange indexes for performance
. Frequently used multiple fields search records, combined indexes are more effective than single index;
. Place the most common list in the forefront, example: dx_groupid_serv_id (GroupID, serv_id), using GroupID or GroupID, serv_id in the where condition, the query will use the index, if only the serv_id field is used, the index is invalid;
. Unnecessary indexes for merge / split.
Limit the number of indexes of each table
. One table can have hundreds of indexes (do you do this?), But for frequent insertion and update tables, the more system CPUs, the I / O burden is the heavier;
. It is recommended that each table does not exceed 5 indexes.
Delete an index that no longer needs
. The index is invalid, and the concentration performance is used to use the function-based index or bitmap index, and use the B * tree index;
. The query in the application does not use the index;
. You must delete the index before rebuilding the index. If you rebuild the index with ALTER INDEX ... Rebuild, you don't have to delete the index.
Index data block space
. Creating an index specifying a table space, especially when establishing a primary key, you should clearly specify the table space;
. Set up PCTFRESS reasonably, pay attention: You cannot specify pctused to the index;
. The size and reasonable setting of the index is set, and the default is the same as the table space size, or INITIAL is set to be as large as NEXT.
Consider parallel to create an index
. For large tables, the memory parameters can be created in parallel. When the index is created in parallel, the storage parameters are used by each query server process, for example: initial is 1M, and the frequency is 8, then at least 8M space is consumed during the index;
Consider creating an index with NOLOGGING
. Creating an index to a large table can use nologging to reduce redo logs;
. Save the space for redo log files;
. Shorten the time to create an index;
. Improved performance of parallel creation of large symbols.
How to build the best index
Create an index clearly
Create index index_name on table_name (field_name)
TableSpace TableSpace_namepctFree 5
INITRANS 2
MaxTrans 255
STORAGE
(
Minextents 1
MaxExtents 16382
Pctincrease 0
);
Create a function-based index
. Commonly classified with Upper, Lower, To_Char (Date), Example:
CREATE INDEX IDX_FUNC ON EMP (Upper (Ename) TABLESPACE TABLESPACE_NAME;
Create a bitmap index
. When the base is smaller, and the base is established, the bitmap index, in case of the bitmap index, examples:
Create Bitmap Index IDX_bitm on class (classno) TableSpace TableSpace_name;
Create a unique index clearly
. You can create a unique index with the CREATE UNIQUE INDEX statement.
CREATE UNIQUE INDEX DEPT_UNIQUE_IDX On DEPT (DEPT_NO) TABLESPACE IdX_1;
Create an index associated with constraint
. You can use the USING INDEX sentence to configure indexes associated with Unique and Primary Key constraints, for example:
Alter Table Table_name
Add constraint pk_primary_keyname primary key (field_name)
USING INDEX TABLESPACE TABLESPACE_NAME;
How to create part of the partial area index
. The basic table must be a partition table;
. The number of partitions is the same as the base table;
. The number of subbands per index partition is the same as the corresponding basic table partition;
. The index item of the rows in the sub-partition of the base form is stored in the corresponding sub-partition of the index, for example:
Create Index TG_CDR04_SERV_ID_IDX ON TG_CDR04 (Serv_ID)
PCTFree 5
TABLESPACE TBS_AK01_IDX
STORAGE
MaxExtents 32768
Pctincrease 0
Freeelists 1
Freeelist groups 1
)
Local
/
How to create a global index of a range partition
. The base table can be a global watch and partition table.
CREATE INDEX IDX_START_DATE ON TG_CDR01 (START_DATE)
Global Partition by Range (Start_Date)
(Partition P01_IDX VLAUES LESS THAN ('0106')
Partition P01_IDX VLAUES LESS THAN ('0111')
...
Partition P01_IDX VLAUES LESS THAN ('0401'))
/
Reconstruction of existing indexes
The current moment of reconstructing existing indexes will not affect the query;
Reconstruction Index can delete additional data blocks;
Improve index query efficiency;
Alter index idx_name rebuild nologing;
For partition index:
Alter index idx_name rebuild partition partiton_name nologing;
The reason for deleting an index
. No longer needed index;
. The index does not provide the desired performance improvement for the query issued by its related table;
. The application does not use the index to query the data;
. This index is invalid, and the index must be deleted before rebuilding;
. The index has changed too broken, and the index must be deleted before rebuilding;
. Statement: DROP INDEX IDX_NAME; DROP INDEX IDX_NAME DROP Partition Partition_name; Establish an index price
When the basic table is maintained, the system should maintain the index at the same time, and the unreasonable index will seriously affect system resources, mainly in CPU and I / O;
Insert, update, delete data generate a large number of DB File Sequential Read lock waiting;
SQL optimizer profile based rule-based optimizer
. Always use indexes
. Always start from the drive table (the leftmost table of the FROM clause)
. Only full mete scan is used in inevitable
. Any index can
Cost-based optimizer
. Requires statistics for tables, indexes
Analyze Table Customer Compute Statistics;
Analyze Table Customer Estimate Statistics Sample 5000 Rows
. Set parallelism in the table, the table partition
Optimizer mode
Rule mode
. I always ignore CBO and statistics based on rules
Choose mode
. Oracle Select Rule or First_Rows or All_Rows as appropriate
FIRST_ROWS mode
. Based on cost, return records at the fastest speed, result in a decline in overall query speed or consume more resources, tend to index scanning, suitable for OLTP systems
All_ROWS mode
. Based on cost, ensure the shortest query time, tend to scan in parallel
E.g:
Select last_name from customer order by last_name
Adjust SQL table access
Full table scan
. Return record: Unlinked table> 40%, ranking meter> 7%, it is recommended to use a parallel mechanism to improve access speed, DDS;
Index access
. The most common method, including index unique scanning and index range scanning, OLTP;
Fast fully index scanning
. Access to all data blocks in the index, the result is equivalent to the full surface scan, can be scanned with the index scanning, for example:
Select serv_id, count (*) from tg_cdr01 group by serv_id;
Assess the legitimacy of full mete scanning
How to implement parallel scanning
. Permanent parallelization (not recommended)
ALTER TABLE CUSTOMER Parallel Degree 8;
. Single query parallelism
SELECT / * FULL (EMP) Parallel (EMP, 8) * / * FROM EMP;
Partitioned surface effect is obvious
Optimize SQL statement sort
Sort by:
. ORDER BY clause
. Group By clause
. SELECT DISTINCT clause
. Create an index
. Union or minus
. Sort merge connection
How to avoid sorting
. Add an index
. Use the Distinct clause in the index
. Avoid sorting merge connections
Use prompt to adjust
Principle of using prompts
. Grammar: / * hint * /
. Use a table name: SELECT / * INDEX (E DEPT_IDX) * / * from EMP E
. Inspection prompt
Commonly used tips
. Rule
. All_Rows
. First_ROWS
. USE_NL
. Use_hash
. USE_MERGE
. Index
. INDEX_ASC
. NO_Index
. INDEX_DESC (commonly used to use the MAX built-in function)
. Index_combine (forced bits index)
. INDEX_FFS (index fast and completely scan). Use_concat (using all Or conditions in the query UION ALL)
. Parallel
. Noparallel
. Full
. ORDERED
Adjustment table connection
Table connection
. Equal connection
WHERE condition is used in equation;
. External connection (left, right connection)
Place an ( ) to place a ( ) to place one ( ) in the equation of the WHERE condition clause, for example:
Select a.ename, B.comm from Emp A, Bonus b where a.ename = b.ename ( );
This statement returns a record of all EMP tables;
. Self-connection
SELECT A. Value Total, B.Value Hard, (A.Value - B.Value) Soft,
Round ((B.Value / a.value) * 100, 1) Perc
From v $ sysstat a, v $ sysstat b
WHERE a.statistic # = 179
And b.statistic # = 180;
Reverse connection
Reverse connection is often used in NOT IN or NOT EXISTS, which means that any records found in the query are not included in the subqueries in the result set; not recommended for not in or not exists;
. Semi-connection
Use exists in the query, meaning: Even if multiple repetitive records are returned in subqueries, the external query returns only one record.
Nested loop connection
. Used in the case where there is an index in the connection table;
. Use USE_NL.
Hash connection
. The HASH connection loads the drive table in memory and uses the Hash technology to connect the second table, improve the speed of connection.
. Suitable for large tables and small surface connections;
. Use use_hash.
Sort merge connection
. Sort merge connection does not use indexes
. Use principle:
There is no available index in the connection form segment;
The query returns most of the data in the two tables;
The CBO believes that the full table scanning ratio can be implemented faster.
. Use use_merge
Use temporary / intermediate table
When multiple large tables are associated, the results of the satisfaction condition can be stored in the intermediate table, and then associated with the intermediate table;
Adjustment of SQL subqueries
Association and non-related sub-inquiry
. Association: The internal reference of the subquery is the external table, and each line is executed once;
. Non-associated: subquery is only performed once, stored in memory.
Adjust NOT IN and NOT EXISTS statements
. You can use an external connection to optimize the NOT IN clause, for example:
SELECT ENAME FROM EMP Where DEPT_NO NOT IN
(Select Dept_no from dept where dept_name = 'math');
Change to:
SELECT ENAME FROM EMP, DEPT
WHERE EMP.DEPT_NO = Dept.DEPT_NO
And Dept.DEPT_NAME IS NULL;
Use index to adjust SQL
Why don't Oracle use an index?
. Check whether the first column of the index or combined index appears in the WHERE clause of the PL / SQL statement, this is the necessary condition for "execution plan" to use the relevant index.
. See which type of connection is used. There is a total of Oracle Sort Merge Join (SMJ), Hash Join (HJ), and NESTED LOOIN (NL). In two tables, only NESTED LOOP can effectively utilize the index when there is an index on the target column of the inner table. SMJ can only avoid data sorting procedures due to the existence of index even if they have an index on the relevant column. HJ must have little effect on data query speeds due to Hash operations. . See if the connection order allows the use of the relevant indexes. Assume that there is an index on the deptno column of the table EMP, and the list DEPT's column DEPTNO has no index. The where statement has EMP.DEPTNO = DePt.deptno. When doing NL connections, EMP is made as an appearance, first visited, due to connection mechanism, the data access method of the appearance is full table scan, the index on Emp.Deptno is clearly not used, up to it, index full scan Or index rapidly scanning.
. Whether to use a system data dictionary table or view. Since the system data dictionary table is not analyzed, it may result in a very poor "execution plan". But don't analyze the data dictionary table without authorization, otherwise it may cause a deadlock, or the system performance is lowered.
. The index column is the parameter of the function. If it is, the index cannot be used in the query.
. There is a potential data type conversion. If the characteristic data is compared to the numerical data, Oracle automatically converts the character type with the to_number () function, resulting in the occurrence of the previous phenomenon.
. Whether to collect enough statistics for the table and related indexes. The table is often increasing, deleted, and changing tables, and the table and index are analyzed regularly, and the SQL statement "Analyze Table XXXX Compute Statistics for All Indexes" can be used. Oracle mastered the full reflection of actual statistics or make the right choice.
. The index column is not selective. We assume typical situations, with Table EMP, a total of one million lines, but the EMP.DEPTNO column, data is only 4 different values, such as 10, 20, 30, 40. Although there are many EMP data, the value in Oracle Default Table is evenly distributed in all data lines, that is, each DEPTNO value has 250,000 data rows. Suppose SQL search criteria deptno = 10, using indexes on the DEPTNO column for data search efficiency, often not being high than full measuring.
. Whether the index column value can be empty (NULL). If the index column value can be a null value, those operations to return null values in the SQL statement will not be used, such as count (*), but scan with full mete. This is because the stored value in the index cannot be full space.
. See if it is useful to query (PQO). Parallel queries will not be indexed.
. If you can't find a reason from the above aspects, we have to force Oracle to use the optimal "Execution Plan" in a manner that uses Hint in the statement. Hint uses an annotated way, there are two ways to comment and paragraphs. If we want to use the IND_COL1 index of the table A, the following methods can be used: "SELECT / * INDEX (A Ind_col1) * / * from a where col1 = xxx;"
How to block the index
When there is a bad index in the execution plan of the statement, it is possible to manually shield the index.
. Value: Add 0 on the index field, for example
Select * from Emp where EMP_NO 0 = V_EMP_NO;
. Character: add '' on the index field, such as SELECT * from TG_CDR01 WHERE MSISDN || '' = v_msisdn;
