On Oracle Database Index

[What is an index]

In a relational database, an index is a data structure associated with a table that makes it faster to execute a query's SQL statement. In fact, the index is like a book directory, we can quickly find the content according to the page number in the catalog.

Write a book, only the chapter content is set and then the directory, database index is the same, only first insert good data, and then build the index.

[Why indexing can improve query efficiency]

Because the DB executes an SQL statement, the default method is to perform a full table scan based on the search criteria and join the search result collection when the match condition is met. If we add an index to a field, the query will first go to the number of rows in the index list once to a particular value, greatly reducing the number of rows to be traversed, so it can significantly increase the speed of the query.

[How to create an index]

To create a table:

CREATE TABLE person (id int, sex char (1), name char (10));

To create an index:

Create index person_index on person (ID);

To view the tables and indexes created:

Select Object_name, object_type from User_objects;

In addition to creating an index based on a single field, you can create an index from multiple columns:

Create index person_index2 on person (sex, name);

To delete an index:

Drop index Person_index;

[Considerations for creating indexes]

1. The purpose of indexing is to improve query efficiency, but too many indexes can affect the speed of inserting and deleting data, because when we modify the table data, the index is modified as well, so when we create the index we need to weigh the number of queries or modifications.

2. The indexed fields should be created:

① fields that are frequently used as query criteria

② columns that are often used in multiple table joins, such as FOREIGN Key

③ fields that you often need to sort

3. If the index should be less built or not built:

① There is too little data in the table, and increasing the index basically does not lead to an increase in query speed, but it wastes storage space.

② tables that often require inserting, modifying, deleting operations

③ fields in the table where the data is duplicated and distributed evenly (such as "gender")

④ rarely used in queries, indexes should not be created

⑤ columns defined as text, image, bit data type should not be indexed

4. Some SQL notation restricts the use of indexes:

in the WHERE clause if you use in, or, like, ! =, <> Will cause the index to not work properly, <> Replace > and <, Replace is not null with >= chr (0)

On Oracle Database Index