SQL Basics-Index

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.