Oracle database Objects--index __python

Indexes (Index)

a test in the university, because it is a course of examination, usually do not how to learn, but to do a test volume, so everyone panic, each prepared their own small copy, everyone has more than 20 pages (exaggeration), witty little Puma in the small copy of the first page wrote a directory, Marked separately, followed by knowledge points and page numbers , and silly is a bunch of stupid, and did not do the catalogue, and then the exam, in the teacher's head, the moment, small Puma View the table of contents found the corresponding page number , and then turn to this page, and then stabilize for a while, the teacher bowed again, I began to copy. However silly is not so lucky, still desperately in a page page , and finally as expected, ha ha ha, was the teacher received a small note, of course, there is a chance to test, after all, is the examination class.

-From the above scenario, you can see two questions 1. Silly classmate seems not so clever (* tactful);
2. The table of contents is very important when we make query information.

Mainly is too boring, so pull over the gossip, so long to introduce today's theme – index
1. Note:(1) The index in the database is like our lives in the directory, we can directly navigate to a row or a number of rows of data, greatly improve the speed of the query.
(2) The index is independent of the table we created, he can be stored in a different disk or table space, is managed by the database management system;
(3) If the index is deleted or destroyed, it will not affect the data table, but can only affect the query speed of the data;
(4) The use of the index is controlled by the database, we do not need to specify the index in the query ;
(5) When a table is deleted, its corresponding index will be deleted accordingly;
(6) The index directly points to the data through the pointer, and the disk IO is reduced by fast positioning.

2. Creation and maintenance of indexes
Index is divided into row index and compound index
(1) Single-line index
A single-line index refers to an index that points to only one column;
In Oracle, when we create a table, if the column is added a PRIMARY key constraint or a unique constraint, the database system automatically helps us create the sequence of columns in the column. No need for us to manually create;
If we want to create an index on a column that is not unique, you can use the
Create index index name on table name (column name);
Eg:create index User_index on users (uname);

(2) composite Index
A composite index is an index that is built on multiple columns at the same time
CREATE INDEX index_name on table_name (COL_NAME1,COL_NAME2,COL_NAME3);

But there are several caveats to using composite indexes, as seen from other blogs, here's a mention:
Create Index i_deptno_job on EMP (deptno,job); -> indexes the Deptno and job columns of the EMP table.
SELECT * from emp where deptno=66 and job= ' sals '-> go index.
The SELECT * from emp where deptno=66 OR job= ' sals '-> will perform a full table scan. Do not go to the index
SELECT * from emp where deptno=66-> go index.
SELECT * from emp where job= ' sals '-> for full table scans, no indexing.
If there is an OR operator in the WHERE clause or a separate reference to the job column ( the column after the index column ), the index will not go, and a full table scan will take place.

3. Query Index
You can use the data dictionary view user_indexes and User_ind_columns to view index information
eg
SELECT Ic.index_name, Ic.column_name,ic.column_position col_pos,ix.uniqueness
From User_indexes IX, User_ind_columns IC
WHERE Ic.index_name = Ix.index_name
and ic.table_name = users;

4. Delete Index
DROP INDEX index_name;
Special note: Only the owner of the index and users who have the drop any index permission can be deleted;

5 When to use Indexes (Index pros and cons)
To think about it or to put it to the last written, through the previous introduction, we are the index of the use of maintenance of the basic grasp, now introduce is also very important question, since we say that the index can improve query efficiency, then is not any time we are going to create an index, The answer must be no (generally the answer to any of these questions is wrong), the following is a brief introduction to the index usage rules.
Of course, for those unique columns, the system has been created for us, we generally do not delete, here is the main consideration or when to manually create the index.
(1) When to create an index.
A: When columns often appear in the WHERE clause or join conditions, the data in the column is distributed in a wide range, such as 1-1000000, and one traversal efficiency is too low; tables are often accessed and have large amounts of data, and the data accessed accounts for about 2% to 4% of the total data.

(2) when not to create an index.
-When the general table is smaller, it is definitely not created, that is, the table data volume is very small;
-Frequently updated for tables;
-For not being a join condition or an ORDER BY or WHERE clause;
-The amount of data accessed is greater than%4;
-None of the above 4 reasons are recommended for creating an index: first the query will be quick, but the database needs to maintain the index, in which case the cost of maintaining an index is very high. So do more harm than good, do not recommend the use of indexes.

If you have any questions, please note, thank you.