1、 普通索引
create index my_index on test (col_1);
可创建合并两列或多列的索引，最多可将32列合并在一个索引中（位图索引最多可合并30列）
create index my_index on test (col_1,col_2);
crate index my_index on test（col_1,col_2） tablespace my_indexes storage (initial 10K next 10K pctfree 10)
通过使用create index 命令的compute statistics来为索引计算统计值。也可以使用compress键来对索引进行压缩
create unique index my_index1 on test (col_1,col_2) tablespace my_indexes compress storage (initial 10k next 10k pctfree 10) compute statistics
2、 位图索引
create bitmap index bit_my_index on my_tab(col_1,col_2) tablespace my_tbs;
3、 使用oracle并行查询来创建索引
create index ix_parts on parts(id) tablespace parts_tablespace parallel 4;
4、 分区索引
在范围分区表上创建本地分区索引,因为本地索引将底层的表分为相等的分区，所以不需要为本地索引定义分区范围信息。
create index ix_part_my_tab_01 on my_tab (col_1,col_2,col_3)
local (partition tbs_part_01 tablespace part_tbs_01,
partition tbs_part_02 tablespace part_tbs_02,
partition tbs_part_03 tablespace part_tbs_03,
partition tbs_part_04 tablespace part_tbs_04
);
在hash分区表，则可用store in参数来创建索引
create index ix_part_my_tab_01 on my_tab (col_1,col_2,col_3)
local store in (part_tbs_01,part_tbs_02,part_tbs_03,part_tbs_04);
每一个独立的分区都有自己的属性，下面是关于在hash分区表上创建本地索引的例子
create index ix_part_my_tab_01 on my_tab (col_1,col_2,col_3)
local store in (part_tbs_01 storage (initial 10m next 10m maxextents 200),
part_tbs_02,
part_tbs_03 storage (initial 100m next 10m maxextents 200),
part_tbs_04 storage (initial 100m next 100m maxextents 200)
);
混合分区上创建本地管理索引的例子
create table store_sales --组合分区表
(store_id number(6),invoice_number number,time_id date,invoice_sale_amt number (10,2))
partition by range (time_id) subpartiiton by hash (invoice_number)
(partition sales_q1_2008 values less than (to_date(’01-apr-2008’,’dd-mon-yyyy’)),
partition sales_q2_2008 values less than (to_date(’01-jul-2008’,’dd-mon-yyyy’)),
partition sales_q3_2008 values less than (to_date(’01-oct-2008’,’dd-mon-yyyy’)),
(subpartition ch_1,subpartition ch_2,subpartition ch_3,subpartition ch_4,subpartition ch_5),
partition sales_q4_2008 values less than (to_date(’01-jan-2009’,’dd-mon-yyyy’))
subpartition 8,
partition sales_overflow values less than (maxvalue) subpartition 4
);
create index sales_ix on store_sales (time_id,store_id)
storage (initial 1m maxextents unlimited)
local (partition q1_2008,
partition q2_2008,
partition q3_2008
(subpartition pq3200801,subpartition pq3200802,subpartitoin pq3200803,
subpartition pq3200804,subpartition pq3200805),
partition q4_2008
(subpartition pq4200801,subpartition pq4200802,subpartition pq4200803,
subpartition pq4200805,subpartition pq4200805,subpartition pq4200806,
subpartition pq4200807,subpartition pq4200808),
partition sales_overflow
(subpartition pqflow01,
subpartition pqflow02,
subpartition pqflow03,
subpartition pqflow04
)
);
创建全局分区索引,全局索引不像本地索引那样需要将表分为相等的分区
create table store_sales --组合分区表
(store_id number(6),invoice_number number,time_id date,invoice_sale_amt number (10,2))
partition by range (time_id) subpartiiton by hash (invoice_number)
(partition sales_q1_2008 values less than (to_date(’01-apr-2008’,’dd-mon-yyyy’)),
partition sales_q2_2008 values less than (to_date(’01-jul-2008’,’dd-mon-yyyy’)),
partition sales_q3_2008 values less than (to_date(’01-oct-2008’,’dd-mon-yyyy’)),
partition sales_q4_2008 values less than (to_date(’01-jan-2009’,’dd-mon-yyyy’))
partition sales_overflow values less than (maxvalue)
);
create index ix_part_my_tab_01 on store_sales (invoice_number)
global partition by range (invoice_number)
(partition part_001 values less than (1000),
partition part_002 values less than (10000),
partition part_003 values less than (maxvalue)
);
当然也可以对多列的全局分区索引进行分区
create index ix_part_my_tab_02 on store_sales (store_id,time_id)
global partition by range (store_id,tition_id)
(partition part_001 values less than (1000,to_date(’01-apr-2008’,’dd-mon-yyyy’))
tablespace partition_001 storage (initial 100m next 10m pctincrease 0),
partition part_002 values less than (10000,to_date(’01-jun-2008’,’dd-mon-yyyy’))
tablespace partition_002 storage (initial 100m next 10m pctincrease 0),
partition part_003 values less than (maxvalue,maxvalue)
tablespace partition_003
);
5、 基于函数索引的例子
create table emp_info (last_name varchar (40),first_name varchar2(40),
hire_date date,salary number);
create index fb_upper_last_name_emp on emp_info (upper(last_name));
6、 重建普通索引
alter index pk_mytable_01 rebuild online tablespace new_pk_tbs pctfree 20 storage (initial 1m next 1m)parallel;
7、 启动并行查询
alter index id_test_01 parallel;
8、 重命名索引
alter index id_test_01 rename to ix_test_01;
9、 改变日志记录子句
alter index ix_test_01 nologging;
alter index ix_test_01 logging;
10、 改变监测属性
alter index ix_test_01 monitoring usage;
alter index ix_test_01 nomouitoring usage;
11、 合并索引
alter index ix_test_01 coalesce;
12、 从索引中释放未使用的空间deallocate unused space子句用于删除索引中不需要的空间
alter index ix_test_01 deallocate unused;
也可以给oracle数据库指定需要保留的空间大小：
alter index ix_test_01 deallocate unused keep 1m;
13、 重新创建分区或子分区
alter index ix_part_my_tab_01 rebuild partition tbs_part_01;
下面是重新创建混合分区索引的子分区的例子
alter idex sales_ix rebuild subpartition pq4200806;
14、 合并分区或子分区
alter index ix_part_my_tab_01 coalesce partition tbs_part_01;
此句为把tbs_part_01分区合并到ix_part_my_tab_01分区索引中。
15、 修改分区或子分区的存储属性
alter index ix_part_my_tab_02 modify partition part_01 storage (freelysts 5 buffer_pool default);
下例：仅当索引在字典管理表空中才会工作
alter index ix_part_my_tab_02 modify partition part_001 storage (next 10m freelysts 5 buffer_pool default);
16、 修改分区索引的默认属性
alter index ix_part_my_tab_02 modify default attributes pctfree 10 storage (initial 200k next 200k)
17、 重命名分区
alter index ix_part_my_tab_02 rename partition part_001 to partition_001
重命名子分区
alter index sales_ix rename subpartition pq3200801 to pq320801;
18、 分割分区或子分区
alter index ix_part_my_tab_01 split partition part_002 at (5000) into (partition partition_002)a,partition partition_002_b);
19、 删除索引分区或子分区
alter index ix_part_my_tab_01 drop partition partition_002_b;
20、 监控索引是否使用
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;
21、 有关索引的视图
dba_indexes 列出每个单独的索引
dba_ind_partitons 列出分区索引中的每一个单独的分区或子分区
dba_ind_columns 列出给定索引中所有列的详细信息
dba_ind_experssions 提供所有与基于函数索引相关联的表达式
dba_ind_subpartitions 为每个分区索引提供子分区信息
dba_join_ind_columns 描述数据库中将位图加入索引中的加入条件
dba_part_indexes 描述数据库中的分区索引
22、oracle创建索引的一些个人见解
基于合理的数据库设计，经过深思熟虑后为表建立索引，是获得高性能数据库系统的基础。而未经合理分析便添加索引，
则会降低系统的总体性能。索引虽然说提高了数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。
是否要为表增加索引、索引建立在那些字段上，是创建索引前必须要考虑的问题。解决此问题的一个比较好的方法，
就是分析应用程序的业务处理、数据使用，为经常被用作查询条件、或者被要求排序的字段建立索引。
基于优化器对SQL语句的优化处理，我们在创建索引时可以遵循下面的一般性原则：
oracle创建索引的一些见解
1、总体原则：使用索引返回的数据量不应该超过总表的 5％
2、需要平衡query合DML的需要，常用于(子)查询的表应建立索引；
3、限制表上的索引数目，建议一张表上不应该超过四个索引，最多最多不超过5个。
4、在主键上创建索引，对于有允许有空值的列不应作为索引列，也不要在有大量相同取值的字段上建立索引
5、第一：在表中的column1列上有一个单索引
第二：在表中有多索引，但是column1是第一个索引的列
避免定义多索引而column1是第二个或后面的索引，这样的索引不能优化服务器性能
6、在经常用于连接的列上创建索引,也就是说在外键上创建索引
7、避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。
8、避免在可选项列建索引，如性别：男，女
9、对有分区的表建立索引时，应创建本地分区索引
10、创建的函数索引，在where时，必需使用函数
11、在创建多列索引时，索引列不应超过表列的三分之一
12、对复合索引，按照字段在查询条件中出现的频度建立索引。
13、删除不再使用，或者很少被使用的索引。
22、 清除Oracle中无用索引 改善DML性能
Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。
下面是一段脚本，它能够打开一个系统中所有索引的监视功能：
Spool run_monitor.sql
Select ‘alter index ‘||owner||’.’||index_name||’ monitoring usage;’
From dba_indexes
Where owner not in(‘SYS’,’SYSTEM’,’PERFSTAT’)
Spool off
@run_monitor
你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图:
|
select index_name,table_name,mon,used from v$object_usage; |
在V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO，它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。
转载：http://lizhichun68.blog.163.com/blog/static/86238492009529196785/