Oracle Index Detailed

Oracle Index Details (i) # # #--Index introduction

?? Indexing is very important for Oracle learning, and using just the right index in the case of a huge amount of data will result in a significant reduction in data query time and a general understanding of the indexes in Oracle in 2017/12/25.

• Creation syntax for indexes
• Features of the index
• Lack of indexes
• Compare the characteristics of columns that are suitable for indexing
• Characteristics of columns that are not suitable for indexing
• restricting indexes (indexed, but not available)
• Query index
• Combined index
• Oracle rowID
• Selective
• Cluster factor
• Dual height
• Fast Full table Scan
• Skip Scan

Creation syntax for indexes

create  or  replace  unique|bitmap index  <schema>.<index_name > on  <schema>.<table_name> (<column_name>|<expression> asc|desc    , <column_name>|<expression> Asc|desc ...) tablespace  <tablespace_name> storage  <storage_settings > logging|nologging compute  statistics  nocompress|compress< nn> nosort|reverse partition|global partition<partition_setting>  

• Unique|bitmap: unique represents a uniqueness index, bitmap represents a bitmap index, or null defaults to B-tree index
• column_name|expression Asc|desc, ...: can be a single-column index, or multiple columns for a federated index, when
• tablespace: Make Table space for indexes (more efficient when tables and indexes are in different table spaces)
• Storage: You can set storage parameters for table spaces
• logging|nologging: whether to generate Redolog for the index (can be set to nologging for large tables to reduce space consumption and increase efficiency)
• COMPUTE Statistics: collect statistics when setting to create an index
• NOCOMPRESS|COMPRESSNN: Whether to use "key compression" (using key compression to remove duplicate values in a key column)
• nosort|reverse: nosort means creating an index in the same order as the table, reverse means creating an index using the reverse order in the table
• Partition|nopartition|global Partition: You can partition created indexes on partitioned tables and on non-partitioned tables

Features of the index

1. Dramatically speed up data retrieval
2. By creating a unique index, you can guarantee the uniqueness of each row of data in a database table
3. Accelerating the connection between a table and a table
4. Faster when a query statement summarizes statements that contain grouping or sorting
5. Use the index to improve the performance of the system by using an optimized hidden device during the query

Lack of indexes

1. Create and maintain indexes, which are time-consuming and grow as data volumes increase
2. Create an index that takes up a certain amount of physical space (clustered index, which takes up more space)
3. When adding additions and deletions to the table, the index should also be updated dynamically.

Compare the characteristics of columns that are suitable for indexing

1. Often need to search for columns on
2. Primary key, which generally establishes a unique index to keep the uniqueness of the data
3. Foreign keys to increase the speed of connections between tables and tables
4. The columns that need to be sorted
5. Fields that often appear behind the WHERE clause
6. A column that often needs to be searched by scope, such as a date

Characteristics of columns that are not suitable for indexing

1. On columns that are rarely searched
2. Columns with fewer values
3. On a column of BLOB type
4. On a column with a high frequency of modification

restricting indexes (indexed, but not available)

1. Use not equal to <>,! =, (does not equal the operator must be full table scan)
2. Use is NULL, was NOT NULL (as long as there is a null in the index, the index is discarded.) So when indexing, be sure to set the column to be indexed to NOT NULL)
3. The SQL optimizer automatically ignores indexes when using functions (WHERE clauses contain trunc (), Add_months (), and so on)
4. In the WHERE clause, a comparison of data type mismatches was made, such as (where row_num = ' 1 '), when the optimizer would limit the use of the index

Query index

• Dba_indexes
• User_indexes
• Uesr_ind_columns

Combined index

1. The index contains more than one column.
2. Before oracle9i, you need to use a leading index before you can use a composite index.

Oracle rowID

?? In the entity table, each row has rowid, and the ability to access a single line of data is provided through the rowid,oracle of each row.

Selective

?? User_indexes in Distinct_keys, the higher the selectivity, the fewer values the index will return.

Cluster factor

?? The closer the clustering factor in user_indexes to the value of the leaf block, the more orderly the data in the table is.

Dual height

?? Dba_indexes's blevel column looks at the two-dollar height of the corresponding index, and the two-dollar height varies depending on the size of the table and the extent of the value in the column being indexed. Rebuilding an index can reduce the height of two yuan.

Fast Full table Scan

?? Allows Oracle to perform a scan of a global index, and a fast full table scan can quickly read all the leaf blocks on the B-tree index.

Skip Scan

createindexon TT(teamid,areacode);select/*+ index(tt TT_index )*/countfrom tt;

Oracle Index Detailed