关闭

Oracle基础学习笔记(二)(Index)

144人阅读 评论(0) 收藏 举报

Oracle automatically create a B-tree Index for Primary key or the column which has UNIQUE constraint.

 

CREATE [UNIQUE] INDEX index_name ON

tab_name(col_name[, col_name ...])

TABLESPACE tab_space;

 

Creating a Funtion-Based Index

ex :

CREATE INDEX i_name ON tab_name(UPPER(col_name));

 

note : need to enable the parameter QUERY_REWRITE_ENABLED (it is false by default)

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;

 

ALTER INDEX i_name RENAME TO i_name;

 

Bitmap Index

Good Candidate for Bitmap Index is a column that is referenced in many queries, but it contains only a small range of values;

If the number of distinct values of a column is less than 1 percent of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.

 

CREATE BITMAP INDEX i_name ON tab_name(col_name);

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:18694次
    • 积分:405
    • 等级:
    • 排名:千里之外
    • 原创:24篇
    • 转载:3篇
    • 译文:0篇
    • 评论:0条