109.Oracle数据库SQL开发之 索引
欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50043069
通常在需要从包含很多行的表中检索少数几行时，都应该对列创建索引。有一条基本的准则是：当任何当个查询要检索的行少于或等于整个表行数的10%时，索引就非常有用。
1. 创建B-树索引创建索引使用CREATE INDEX语句可以用来创建B-数索引
语法如下：
CREATE [UNIQUE] INDEX index_name ONtable_name( column_name,[,column_name …])
TABLESPACE tab_space;
由于性能方面的原因，通常应该将索引与表存储到不同的表空间中。
执行如下查询：
store@PDB1> selectcustomer_id,first_name,last_name from customers where last_name='Brown';
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
1 John Brown
如果last_name列的值都是唯一的，以使任何在WHERE子句中使用last_name列的查询所返回的行数都小于该表总行数的10%. 就意味着last_name列非常适合创建索引。
store@PDB1> create index i_customers_last_name oncustomers(last_name);
Index created.
此外，可以使用唯一索引可以实现列值的唯一值。例如：
store@PDB1> create unique index i_customers_phoneon customers(phone);
Index created.
也可以对多列创建复合索引。
store@PDB1> create indexi_employees_first_last_name on employees(first_name,last_name);
Index created.
2. 创建基于函数的索引执行如下：
store@PDB1> select first_name,last_name fromcustomers where last_name=UPPER('BROWN');
no rows selected
这个查询使用了一个函数UPPER()，因此不会使用索引i_employees_last_name.
如果想让索引可以基于函数的结果使用，就必须创建基于函数的索引。
例如下：
store@PDB1> create indexi_func_customers_last_name on customers(UPPER(last_name));
Index created.
为了利用基于函数的索引，DBA必须将初始化参数QUERY_REWRITE_ENABLED设置为TRUE。例如：
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE；
3. 从数据字典中获取有关索引的信息从user_indexes视图中可以获得有关索引的信息。
查询all_indexes视图可以获得有关所有要访问的索引信息。
store@PDB1> selectindex_name,table_name,uniqueness,status from user_indexes where table_name in('CUSTOMERS','EMPLOYEES') order by index_name;
INDEX_NAME
----------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------
UNIQUENES STATUS
--------- --------
CUSTOMERS_PK
CUSTOMERS
UNIQUE VALID
EMPLOYEES_PK
EMPLOYEES
UNIQUE VALID
I_CUSTOMERS_LAST_NAME
CUSTOMERS
NONUNIQUE VALID
I_CUSTOMERS_PHONE
CUSTOMERS
UNIQUE VALID
I_EMPLOYEES_FIRST_LAST_NAME
EMPLOYEES
NONUNIQUE VALID
I_FUNC_CUSTOMERS_LAST_NAME
CUSTOMERS
NONUNIQUE VALID
6 rows selected.
4. 获取列索引的信息通过查询user_ind_columns视图可以获得列索引的信息。
store@PDB1> selectindex_name,table_name,column_name from user_ind_columns where table_name in('CUSTOMERS','EMPLOYEES') order by index_name;
INDEX_NAME
----------------------------------------------------------------------------------------------------
TABLE_NAME COLUMN_NAME
--------------- ---------------
CUSTOMERS_PK
CUSTOMERS CUSTOMER_ID
EMPLOYEES_PK
EMPLOYEES EMPLOYEE_ID
I_CUSTOMERS_LAST_NAME
CUSTOMERS LAST_NAME
I_CUSTOMERS_PHONE
CUSTOMERS PHONE
I_EMPLOYEES_FIRST_LAST_NAME
EMPLOYEES LAST_NAME
I_EMPLOYEES_FIRST_LAST_NAME
EMPLOYEES FIRST_NAME
I_FUNC_CUSTOMERS_LAST_NAME
CUSTOMERS SYS_NC00006$
7 rows selected.
5. 修改索引ALTER INDEX语句可以用来修改索引。
store@PDB1> alter index i_customers_phone renameto i_customers_phone_number;
Index altered.
6. 删除索引DROP INDEX可以用来删除索引，如下：
store@PDB1> drop index i_customers_phone_number;
Index dropped.
7. 创建位图索引位图索引一般用于数据仓库中，数据仓库是包含大量数据的数据库。数据仓库中的数据一般使用很多查询来读取，但数据并不被很多并发事务所修改。数据仓库一般被组织机构用来进行商业智能分析，例如监控销售趋势。
位图索引的候选列是在很多查询中被引用但只包含小范围值的列。
位图索引一般用来包含大量数据且内容不常修改的表中。而且位图索引只应该在包含少量不同值的列上创建。如果某列的不同值数量少于表中行数的1%,或者如果某列的值的重复次数多于100次，那么此列就是位图索引的候选列。
创建位图索引如下：
store@PDB1> create bitmap index i_order_status onorder_status(status);
Index created.
