Go: Oracle Index Introduction and usage

1. What is an index
An index is a helper object that is built on one or more columns of a table in order to speed up access to the data in the table;
Oracle Storage index data structure is b* tree, bitmap index is the same, but the leaf node is different b* number index;
The index consists of the root node, the branch node, and the leaf node, and the ancestor index block contains the index data of the subordinate index block, and the leaf node contains the index data and the ROWID that determines the actual position of the row.
Purpose of using indexes
Speed up queries
Reduce I/O operations
Eliminate disk sorting
When to use an index
Number of records returned by the query
Sort Table <40%
Non-sorted table <7%
More fragmentation of tables (frequent additions, deletions)

2. Types of indexes
Non-unique index (most commonly used)
Unique index
Bitmap index
Locally prefixed Partition Index
Local prefix partition index
Globally prefixed partition Index
Hash Partition Index
Function-based indexing
3. Guidelines for managing Indexes
　　
Create an index after inserting data into a table
After inserting or loading data with the Sql*loader or import tool, it is more effective to build the index;
3.1 Indexing the correct tables and columns
Frequently retrieving rows of 40% or non-sorted table 7% in a large table, it is recommended to build an index;
。 In order to improve the multi-table association, index columns are used for junctions;
。 The values in the column are relatively unique;
。 Value range (large: b* tree index, Small: Bitmap index);
。 A date column is generally suitable for function-based indexing;
。 There are many null values in the column that are not suitable for indexing
　　
3.2 Arranging indexed columns for performance
　　
。 Multiple fields are often used together to retrieve records, and a composite index is more efficient than a single index;
。 Put the most commonly used columns first, for example: dx_groupid_serv_id (groupid,serv_id), use 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;
3.3 Merging/splitting of unnecessary indexes.
　　
3.4 Limit the number of indexes per table
　　
。 A table can have hundreds of indexes (would you do that?). ), but for frequently inserting and updating tables, the more indexes the system cpu,i/o the heavier the burden;
。 It is recommended that no more than 5 indexes per table.
　　
3.5 Delete an index that is no longer needed
　　
。 Invalid index, concentrated on the use of the function-based index or bitmap index, and the use of the b* tree index;
。 Queries in the app do not use indexes;
。 You must delete the index before rebuilding the index, and you do not have to delete the index if you rebuild the index with ALTER index ... rebuild.
　　
3.6 Index data Block space usage
。 Specify a tablespace when creating an index, especially when establishing a primary key, explicitly specifying a table space;
。 Reasonable set pctfress, note: The index can not be specified pctused;
。 Estimate the size of the index and reasonably set the storage parameters, either by default to the table space size, or initial as large as next.
3.7 Consider creating indexes in parallel
。 For large tables, you can create indexes in parallel, and when you create indexes in parallel, the storage parameters are used by each query server process, for example: initial is 1M and the degree of parallelism is 8, which consumes at least 8M space during index creation;
3.8 Consider creating an index with nologging
。 Creating indexes on large tables can use nologging to reduce redo logs;
。 Save space for redo log files;
。 Shorten the time to create the index;
。 Improved performance when creating large indexes in parallel.

4. How to build the best index
　　
Explicitly create an index
CREATE INDEX index_name on table_name (FIELD_NAME)
Tablespace Tablespace_name
Pctfree 5
Initrans 2
Maxtrans 255
Storage
(
Minextents 1
Maxextents 16382
Pctincrease 0
);
　　
Create a function-based index
　　
。 Commonly used with upper, LOWER, To_char (date) and other functions of classification, example:
CREATE INDEX Idx_func on EMP (UPPER (ename)) tablespace tablespace_name;
　　
Create a bitmap index
　　
。 When you index a column with a smaller cardinality and a relatively stable cardinality, you should first consider the bitmap index, for example:
Create Bitmap index Idx_bitm on class (Classno) tablespace tablespace_name;
　　
Explicitly create a unique index
　　
。 You can use the Create unique index statement for creating unique indexes, for example:
Create unique index DEPT_UNIQUE_IDX on dept (DEPT_NO) tablespace idx_1;
　　
To create a constraint-related index
　　
。 Use index words can be used for indexes related to the 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;
　　
5. How to create a partial index of a local area
　　
。 The base table must be a partitioned table;
。 The number of partitions is the same as the base table;
。 The number of sub-partitions per index partition is the same as the corresponding base table partition;
。 The index entry for the row in the child partition of the underlying table, which 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
Freelists 1
FreeList Groups 1
)
Local
/
　　
6. How to create a global index for a range partition
　　
。 The underlying table can be a global table and a partitioned 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 '))
/
Rebuilding an existing Index
The current moment of rebuilding an existing index does not affect the query;
　　
Rebuilding an index can delete additional chunks of data;
Improve the efficiency of index query;
Alter index idx_name rebuild nologging;
　　
For partitioned indexes:
ALTER index IDX_NAME rebuild partition partiton_name nologging;
　　
7. Reasons to delete an index
　　
。 Indexes that are no longer needed;
。 The index does not provide the desired performance improvement for queries published by its related tables;
。 The application does not use the index to query the data;
。 The index is invalid and must be deleted before rebuilding;
。 The index has become too broken and must be deleted before rebuilding;
。 Statement: Drop INDEX Idx_name;drop index idx_name drop partition partition_name;
　　
8. The cost of establishing an index
　　
When the basic table is maintained, the system should maintain the index at the same time, the unreasonable index will seriously affect the system resources, mainly in the CPU and I/O;
　　
Inserting, updating, deleting data generates a large number of DB file sequential read lock waits;

There are millions of of data in a table, indexes a field, but there is no improvement in query performance, which may be due primarily to Oracle's index limit.

Oracle's index has some index limits, and if these index limits occur, Oracle performs a full table scan even if an index is already indexed, and the performance of the query is no better than no index, but may be worse because of the overhead of database maintenance indexes.

The following are some common index throttling issues.

9. Use not equal to operator (<>! =)

In this case, even if there is an index in column dept_id, the query statement performs a full table scan

SELECT * FROM dept where Staff_num <> 1000;

However, the development does require such a query, there is no way to solve the problem?

Yes!

By querying with or syntax instead of an equal sign, you can use the index to avoid a full table scan: The above statement is changed to the following so that the index can be used.

SELECT * FROM dept Shere Staff_num < or dept_id > 1000;

10. Using is null or is not NULL

Using is null or is Nuo null also restricts the use of the index because the database does not define a null value. If there is a lot of NULL in the indexed column, the index will not be used (unless the index is a bitmap index and the bitmap index will be explained in detail in a later blog post). Using NULL in an SQL statement can cause a lot of trouble.

The solution to this problem is to define the columns that need to be indexed as non-null (NOT NULL) when the table is built

11. Using Functions

If you do not use a function-based index, using a function in the WHERE clause for columns that have indexes causes the optimizer to ignore those indexes. The following query will not use the index:

SELECT * from the staff where trunc (birthdate) = ' 01-may-82 ';

However, the function is applied to the condition, the index can be effective, the above statement is changed to the following statement, you can search by index.

SELECT * from the staff where birthdate < (to_date (' 01-may-82 ') + 0.9999);

12. Comparing data types that do not match

Comparing data types that do not match is also one of the hard-to-find performance issues. In the following example, dept_id is a VARCHAR2 field with an index on this field, but the following statement performs a full-table scan.

SELECT * FROM dept where dept_id = 900198;

This is because Oracle automatically converts the WHERE clause to To_number (dept_id) = 900198, which is what 3 says, which limits the use of the index. You can use the index by changing the SQL statement to the following form

SELECT * FROM dept where dept_id = ' 900198 ';

13. Use the LIKE clause

When querying using the LIKE clause, the data needs to be judged by traversing all the records, and the index does not work, and this situation should be avoided as much as possible.

The first character in a like string is not indexed if it is '% '
Column1 like ' aaa% ' is possible.
Column1 like '%aaa% ' can't be used

14. Using the In

Although the in notation is simpler than exists, exists generally performs much higher than in
With in or with exists time
When the set of in is relatively small, or use exists can not use the high-selectivity index, use in good, otherwise you will use exists
Example: SELECT COUNT (*) from Person_info where XB in (select xb_id from Dic_sex);
Select COUNT (*) from N_acntbasic a where shbxdjm =:a and exists (select 1 from Person_info where pid=a.pid and ...);

Select * from Person_info where zjhm=3101 .... will be scanned for person_info full table
Select * from person_info where zjhm = ' 3101 ... ' to use the index

Assume that the DT field of the test table is of type date and that the DT is indexed.
If you want to check the data for ' 20041010 ' Day. The following method does not use the index
Select * FROM test where to_char (DT, ' yyyymmdd ') = ' 20041010 ';
While the SELECT * from test where DT >=to_date (' 20041010 ', ' YYYYMMDD ') and DT < to_date (' 20041010 ', ' YYYYMMDD ') + 1 will be used for the index.

15. If you can not use the sort, try to avoid sorting.
Used to sort the case there are
Collection operation. Union, minus, intersect, etc., note: UNION ALL is unordered.
Order by
Group by
Distinct
In sometimes the sort is used
Do try to sort small amounts of data when you want to sort
, try to make the sort in memory execution, there is the article said that the speed of memory sorting is 10,000 times times the hard disk sort.

Go: Oracle Index Introduction and usage