Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the index in SQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Concept

Is a database object that accelerates querying data through pointers and reduces disk IO

A catalogue of similar books

Automatically use and maintain indexes-automatically created on primary key and unique columns

2. Create

Basic grammar

Create index emp_ename_idx on emp (ename)

B-tree index

1) unique index, key values are not duplicated. SQL > create unique index empno_idx on emp1 (empno)

2) non-unique index SQL > create index empno_idx on emp1 (empno)

3) Composite index (Composite): an index based on two or more columns. SQL > create index job_deptno_idx on emp1 (job,deptno)

4) reverse key index (Reverse): organize key values after inverting bytes. When the primary key index is generated by using the sequence, the hot block phenomenon in the leaf node can be prevented. The disadvantage is that index range scanning cannot be provided.

SQL > create index mgr_idx on emp1 (mgr) reverse

5) functional index (Function base): organize index SQL > create index fun_idx on emp1 (lower (ename)) with the function value of the index column value as the key value

6) Compress: repeat the key value only once, that is, the repeated key value is stored once in the leaf block, followed by all matching rowid strings.

SQL > create index comp_idx on emp1 (sal) compress

7) Ascending or descending (Ascending or descending): the arrangement of key values in leaf nodes is ascending by default.

SQL > create index deptno_job_idx on emp1 (deptno desc, job asc)

Functional index

3. Rebuild and delete

Alter index ind_test_id rebuild online

Drop index

4. Not available, not visible

Unusable is not available

Alter index ind_test_id unusable

Rebuild if you need to use: alter index ind_test_id rebuild

Invisible is not visible

Alter index ind_test_id invisible

Alter index ind_test_id visible

11g new feature, not visible only to the optimizer, but index maintenance is still normal

5. Index monitoring (check whether the index is used)

Alter index pk_dept monitoring usage

Select * from v$object_usage

Alter index pk_dept nomonitoring usage

6. Points for attention

7. View

User_indexes

User_ind_columns

The above is all the contents of the article "how to use the Index in SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.