oracle索引的建立以及使用

以下内容是整理的网上的一些资料：

创建索引：

用户登录：
SQL> conn as1/as1
Connected.

创建表：
SQL> create table dex (id int,sex char(1),name char(10));
Table created.

向表中插入1000条数据
SQL> begin
 for i in 1..1000
 loop
 insert into dex values(i,'M','chongshi');
 end loop;
 commit;
 end;
 /

PL/SQL procedure successfully completed.

查看表记录
SQL> select * from dex;
        ID SE NAME
---------- -- --------------------
       ... . .....
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
rows selected.

创建索引：
SQL> create index dex_idx1 on dex(id);
Index created.
注：对表的第一列（id）创建索引。

查看创建的表与索引
SQL> select object_name,object_type from user_objects;

OBJECT_NAME                  OBJECT_TYPE
--------------------------------------------------------------------------------
DEX                           TABLE
DEX_IDX1                      INDEX

索引分离于表，作为一个单独的个体存在，除了可以根据单个字段创建索引，也可以根据多列创建索引。 Oracle 要求创建索引最多不可超过 32 列。

SQL> create index dex_index2 on dex(sex,name);
Index created.

SQL>  select object_name,object_type from user_objects;

OBJECT_NAME                           OBJECT_TYPE
--------------------------------------------------------------------------------
DEX                                       TABLE
DEX_IDX1                                 INDEX
DEX_INDEX2                               INDEX

这里需要理解：

　　编写一本书，只有章节页面定好之后再设置目录；数据库索引也是一样，只有先插入好数据，再建立索引。那么我们后续对数据库的内容进行插入、删除，索引也需要随之变化。但索引的修改是由oracle自动完成的。

SQL> select * from dex where id>23 and id<32;

        ID SE NAME
---------- -- --------------------
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
M  chongshi
rows selected.

如上面查找的列子，通过索引的方式先找到第 23 条数据，再找到第 32 条数据，这样就能快速的锁定一个查找的范围，如果每条数据都要从根节点开始查找的话，那么效率就会非常低下。

提问：

在网上就找不到一个入门的例子 都是一些高深的 看不懂的 好多参数根本不懂 也没有解释....找了一个...这样的改了一下
create index abook2_idx on abook2(name)
tablespace user
pctfree 10
initrans 2
maxtrans 255
storage
(
  initial 64K
  minextents 1
  maxextents unlimited
)
 他提示我 ORA-02216: 需要表空间名
 那个user是我瞎写的...应该写什么啊 还有下面那几个参数能解释一下吗?

回答：

create index abook2_idx on abook2(name)：是指你创建的索引名是“ abook2_idx ”，该索引是基于并且在abook2表的name列上。
tablespace user ：是指你创建的这个索引放在哪个表空间下面。
pctfree 10：是指你创建的索引至少要保留10%作为空闲空间，以便插入与删除。
initrans 2：支持并发操作的初始事务量
maxtrans 255：支持并发操作的最大事务量。
initial 64K：一个区得大小为64K。
minextents 1：是指初始区的个数为1个。区是oracle的最小单位，对应的就是磁盘空间。
maxextents unlimited：这个当然就是最大区的个数无限制。

“他提示我 ORA-02216: 需要表空间名
 那个user是我瞎写的...应该写什么啊 还有下面那几个参数能解释一下吗?”
上面的意思是叫你把你创建的索引放在oracle已有的一个表空间中，一般为了保证系统系能，建议你专门创建一个索引表空间，用来存放索引。

回答：

create index abook2_idx on abook2(name)
只要这句，其余删掉

追问

create index abook2_idx on abook2(name)
 不行 显示这个..
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源

回答

建议重启服务，环境问题

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

提问：

oracle数据库：

1、select * from table where a=a and b=b and c=c and d=d;
这里我建立了一个a,b,c,d的复合索引，但如果使用：
select * from table where a=a and b=b;
进行查询时第一次很慢，第二次之后再查就快了，应该是使用了数据库的缓冲区进行的查询，那这个abcd的复合索引对于where a=a and b=b;就不起作用了么？如果有若干个SQL：
select * from table where a=a;
select * from table where a=a and b=b;
select * from table where a=a and b=b and c=c;
select * from table where a=a and b=b and c=c and d=d;
等等，总不能对每个where条件建立一个索引？

2、有没有SQL可以查询某个SQL执行时是否使用索引了？

谢谢！

where a=变量a;
where a=变量a and b=变量b;
where a=变量a and b=变量b and c=变量c;
where a=变量a and b=变量b and c=变量c and d=变量d;
这些都可以用abcd这个复合索引？
但从现在执行的时间来看，a=变量a这里是没有用到索引的，不知道有什么方法可以让它走索引么？

提问者采纳

1，第一次查询慢，以后就快了，主要是因为第一次要进行磁盘操作，以后数据被cache到内存中了，不在操作磁盘，所以就快了。
2，对于你说的这四种查询，where条件中的a=a估计你是举例子这样写的吧。实际上应该是a=变量A。其他的b，c，d也是这样。那么这种语句都是可以利用你说的复合索引的。如果是RBO优化器，这四句都应该用索引。但是oracle现在推荐的CBO优化器不能保证你都走索引。
3，到底用没用索引，你可以从v$sqlaera中找到你的语句对应的hash_value，然后从v$sql_plan中找到语句的执行计划，通过执行计划确认你的语句是不是使用了索引。
具体语句你可以类似如下写法：

select hash_value,sql_text from v$sqlarea where upper(sql_text) like '%你需要查找的sql语句的特征片段%'

select * from v$sql_plan where hash_value = 上一句查到的hash_value

参考资料：http://www.cnblogs.com/fnng/archive/2012/10/10/2719221.html

索引应该注意的：http://liusuper.iteye.com/blog/219013