The concept of an index:
Indexing in a database is a means to improve the performance of database query operations, but on frequently updated tables, indexes degrade performance.
Common index Structure:
- b* Tree Index
- Bitmap index
B-Tree Index:
The B-book index is the most basic index structure, and the index type established by default in Oracle is this type of index.
The general B-Number index provides high-performance retrieval when retrieving a high-cardinality sequence, which has less or no duplication of content
B book index is used by the two-tree arrangement, in the leaf node has rowid value, with ROWID this way query efficiency is the highest.
B-Tree Index build syntax:
Create index [user name.] Index name on [ user name.] Table name (column name [Asc|desc])
--The default is ASC
Demonstrate:
--There are no indexed queries:
SQL>seton # #开启自动跟踪功能 sql>Select* fromwhere sal<;
Results:
Time used:xx:xx:00.02Execution Plan----------------------------------------------------------PlanHash value:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost (%CPU)|Time|--------------------------------------------------------------------------| 0 | SELECTSTATEMENT| | 7 | 252 | 3(0)| xx:xx: on ||* 1 | TABLE ACCESS full|Emp| 7 | 252 | 3(0)| xx:xx: on |--------------------------------------------------------------------------predicate information (identified byoperation ID):--------------------------------------------------- 1 -Filter ("SAL"< the) Statistical information---------------------------------------------------------- 1Recursive calls0db block gets8consistent gets0Physical reads0Redo Size1220Bytes sent via SQL*Net toClient520Bytes received via SQL*Net fromClient2Sql*Net roundtrips to/ fromClient0sorts (memory)0Sorts (Disk) 5Rows processed
You can see the whole scan.
--Building a B-number index
SQL>Createindex on EMP (SAL);
Tracking again:
Time used:xx:xx:00.01Execution Plan----------------------------------------------------------PlanHash value:317084801-------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost (%CPU)|Time|-------------------------------------------------------------------------------------------| 0 | SELECTSTATEMENT| | 7 | 252 | 2(0)| xx:xx: on || 1 | TABLEACCESS by INDEXROWID|Emp| 7 | 252 | 2(0)| xx:xx: on ||* 2 | INDEX RANGE SCAN |Emp_sal_ind| 7 | | 1(0)| xx:xx: on |-------------------------------------------------------------------------------------------predicate information (identified byoperation ID):--------------------------------------------------- 2 -Access ("SAL"< the) Statistical information---------------------------------------------------------- 0Recursive calls0db block gets4consistent gets0Physical reads0Redo Size1238Bytes sent via SQL*Net toClient520Bytes received via SQL*Net fromClient2Sql*Net roundtrips to/ fromClient0sorts (memory)0Sorts (Disk) 5Rows processed
Compared to the above index is not indexed, you can see the time consuming and the CPU is reduced.
Querying the user's indexed view
--user_indexes
SQL>Select index_name,index_type,status from user_indexes; index name index type STATUS--------------------------------- --------------------pk_mid normal validpk_emp normal validemp_sal_ind Normal validpk_dept normal VALID
The primary key is automatically indexed.
--user_ind_columns
See which field the index is built on
Sql> Select * fromUser_ind_columnswhereIndex_name='Emp_sal_ind'; INDEX_NAME table_name column_name column_position column_length char_length descend------------------------------ ------------------------------ -----------------------------------------------Emp_sal_ind EMP SAL1 A 0 ASC
Bitmap index
For low cardinality columns, multiple data duplication
Create syntax:
Create BITMP index [user name.] Index name on [ user name.] Table name (column name [Asc|desc])
Instance:
--Create a bitmap index on the Deptno
SQL>Createindex on EMP (DEPTNO);
To delete an index:
Because the index itself is for data maintenance, it typically consumes large disk space, and as the table grows, it should be deleted for those infrequently used indexes.
Delete syntax:
Drop index index name;
SQL Basics-Index