oracle索引查询步骤,oracle创建使用索引,查询索引状态

SQL> conn tudou/111;

//插入测试数据

SQL> insert into temp1 values('z壮','23');

SQL> insert into temp1 values('z','25');

SQL> insert into temp1 values('a','28');

SQL> insert into temp1 values('奇才','29');

SQL> insert into temp1 values('才子、','12');

SQL> insert into temp1 values('二飘落','14');

SQL> insert into temp1 values('脾用','18');

SQL> select * from temp1;

NAME                AGE

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

土土                22

z壮                 23

z                   25

a                   28

奇才                29

才子、              12

二飘落              14

脾用                18

SQL> insert into temp1 values('fdffs','18');

SQL> insert into temp1 values('ppppp','18');

SQL> insert into temp1 values('大规模 ','13');

SQL> select * from temp1;

NAME                AGE

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

土土                22

z壮                 23

z                   25

a                   28

奇才                29

才子、              12

二飘落              14

脾用                18

fdffs               18

ppppp               18

大规模              13

//创建索引,索引建立在name之上。

SQL> create index ix_name on temp1(name);

Index created

//监视索引

SQL> alter index ix_name monitoring usage;

Index altered

//查看索引状态，发现USED这列显示为no，说明索引尚未使用。

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING

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

IX_NAME                        TEMP1                          YES        NO   02/21/2011 11:44:26

//查询字段，模糊查询的字段正是建立索引的字段。

SQL> select * from temp1 where name like '%z%';

NAME                AGE

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

z壮                 23

z                   25

//再次查询索引状态，发现used列还是no。说明索引在使用like模糊查询时未起作用。不过好像like '%z%';写成like 'z%'索引就会起作用。

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING

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

IX_NAME                        TEMP1                          YES        NO   02/21/2011 11:44:26

//不要紧，我们这次用精确查询

SQL> select name from temp1;

NAME

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

土土

z壮

z

a

奇才

才子、

二飘落

脾用

fdffs

ppppp

大规模

11 rows selected

//再次查询看发现索引还是不起作用，这说明直接查询字段名称索引不起作用。

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING

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

IX_NAME                        TEMP1                          YES        NO   02/21/2011 11:44:26

//最后我们用条件查询

SQL> select * from temp1 where name ='a';

NAME                AGE

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

a                   28

//OK，索引在条件查询时起作用了。这时索引才真正发挥它的作用。

SQL> select * from v$object_usage; INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING ------------------------------ ------------------------------ ---------- ---- ------------------- ------------------- IX_NAME                        TEMP1                          YES        YES  02/21/2011 11:44:26