Oracle Index ____oracle

A table if the amount of data is very large, if the table is not indexed, then any records in this table can only be scanned sequentially, which results in a large amount of IO input output, thus reducing execution efficiency. To speed up the query, this time we need to use the index. What is an index? For example, you read a book, but you want to look at a certain point in the book, if there is no catalogue in this book, then you can only go one page to find what you want to see. This is not very slow. If you have a table of contents, you will first go to the directory, find a directory, and then according to the page number that the directory points to, Then read directly what you want to read below the page number. In this case, the directory is the equivalent of an index.

In Oracle, indexes are also records that are stored in the order of the index keys, also known as data structures. There are index keys in the index record and pointers (addresses) to the real data in the table. Oracle systems use algorithms to quickly locate records on the index, and use the pointer to find the data you need. The Oracle index stores only the index keys and pointers to the real data in the table, so it is much smaller, so the IO volume that operates on the index is much smaller than the IO of the Operation table, which is equivalent to speeding up the query speed.

In Oracle, an index is a table-independent object that can be stored on a different disk than the table, even if the index crashes, It does not affect tables that actually have data. The index is automatically maintained by Oracle after it is established. and Oracle automatically decides when to use the index. You do not need to specify an index in the query statement. If the table is deleted, all indexes based on the table are automatically deleted.Establish an index Automatic System EstablishmentWhen a primary key (primary key) is established on a table, or when a unique constraint (unique) is created, Oracle automatically creates a unique index.Manually BuildManually create a nonunique index on one or more columns of a table by using the statement that creates the index. The syntax for creating an index is as follows, create INDEX (index name) on table name. It can be understood to create an index named XXX on which column of the table (on). It is important to note that Oracle's recommended index naming is "table name _ Column Name _ Object Type", which is easier to maintain for Oracle, but it is not mandatory but a recommendation.
View IndexOracle's data dictionary user_indexes can be used to view the index information under the currently logged-on user. A simple query will take care of the names of the fields from the SELECT * from User_indexes query, with only a few attention points, as follows.
More detailed information can be queried user_ind_columns this table to obtain, you can query to more specific information.
issues to be aware of using indexesIndex Keywords: An individual is understood to be the index name. Although indexing may speed up the query, this may slow down the DML operation. Because each DML statement, as long as the index keyword is involved, Oracle adjusts the index. and the index as a stand-alone object is required disk space. If the table is large, Then the index consumes a lot of disk space. You should try to avoid creating too many indexes on one table. and by indexing, Oracle does not necessarily guarantee that it will be used. In order to use the index, The index keyword should be placed in the WHERE clause of the SELECT statement as much as possible. This does not guarantee that Oracle will necessarily use indexes. For example, put an index key for a composite index in the where word in the SELECT statement. The index keyword is not the index key to the left of the crime.Oracle's recommendations on indexingThe table is large and most queries have very little return data (Oracle recommends 2% to 4% less than the total number of rows). Because if you return a large amount of data, you might as well scan the table sequentially. This column has a wide range of values. It is generally random. As in most employee tables, the age of one column (18--65) is available. It can be considered a random distribution. But if the range is small, it's not randomly distributed. One or more columns often appear in the WHERE clause or join condition. There are very few DML operations on the table. This column contains a large number of NULL values (NULL) This column is not often used as part of an expression in a SELECT statement.Functional IndexA generic index is a column, whereas a functional index is an expression. Suppose that if you use an expression in a WHERE clause in a SELECT statement, how do you speed up such a query? We can use functional indexes to solve such problems. It is created in the same way as a normal index, except that the column name is replaced with an expression.
Once created, the query can see the following
Delete IndexDelete index syntax to drop INDEX index name second, when you delete a table, some of the indexes that you want to associate with the table are deleted together.