★★100分，为什么建了索引的效果不明显?★★

Question

我有一个表,大概是600万的纪录,其中有一个字段是字符型我需要用like '%关键词%' 的方式去查询,查一次要很长时间,我在sql developer里建了索引,选择的就是这个字段,建完了以后我再去查询发现速度一点都没有变化,是不是检索语句需要调整??还是哪个地方需要设置一下把索引起用??
谢谢大家,我刚开始接触oracle.

Answer 1

可以试着用instr(字段,'关键字') > 0或考虑创建此函数index,谢谢

Answer 2

用Like '%字段%'，字段上的索引时不起作用的。

Answer 3

like 无法使用索引，因为要一个一个去比对，自然需要全表扫描才合适 。

Answer 4

准确的说如楼上几位兄弟的说的
LIKE只有'关键词%'时才能用上索引

Answer 5

like 只在 ‘关键字%’时候才起作用，'%关键字%‘ 不起作用的。

Answer 6

谢谢大家，我改了一下表达式，果然有所改善
但是如果我想模糊查询的话，请问instr()函数的原理和'%关键词%'有什么区别呢？？从哪里创建一个函数的索引呢？

Answer 7

instr是建立了函数索引，所以在你查询的时候，子要匹配的话，都会走索引扫描，index scan。而 like '%关键词%'由于 oracle机制的设定，所以无法走索引，只能全表扫描， table scan

Answer 8

首先在执行like '%关键词%'查询时，同时跟踪查询的执行过程，看有没有使用到需要的索引，不过多半索引都没有用上的。
重新写select命令，在查询时指定oracle使用索引（网上有很多查询时指定索引的文章）。

Answer 9

请问高手如何创建 instr(字段,'关键字') 函数的索引呢？

Answer 10

create index on tablename(instr(字段,'关键字'))

Answer 11

create index indexname on tablename(instr(字段,'关键字'))

Answer 12

一群人说了半天都没有让别人走上索引·
1、LZ你建立函索索引instr，用instr替换like来做操作
2、建立  indextype is CTXSYS.CONTEXT; 利用inter Media来进行索引扫描·
3、也许你都不要建立索引，如果主机CPU开销不打，尝试下Oracle的并行.

Answer 13

drop table test ; --删除表create table test --创建表
(
 tname varchar2(20),
 p  varchar2(20)
);
insert into test select '中国人','中国' from dual union all --插入数据
select '日本人' ,'日本' from dual union all
select '美国人' ,'美国' from dual union all
select '荷兰人' ,'荷兰' from dual union all
select '西班牙人' ,'西班牙' from dual union all
select '中国广东人' ,'中国' from dual union all
select '中国香港人' ,'中国' from dual union all
select '中国台湾人' ,'中国' from dual;
commit;create index t_index on test(tname);  --创建索引select * from test where test.tname='中国人'--执行索引
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (RANGE SCAN) OF 'T_INDEX' (NON-UNIQUE)select * from test where test.tname like '%中国%'
--不走索引
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'select * from test where test.tname like '中国%' or test.tname like '%中国'--也不走索引
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'select * from test where regexp_like(tname,'中国') --oracle 10G的正则表达式，模糊查询的一些比较疑难的可以用次方法
--还是不走索引
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'
   
   
   
PS:以上是给大家开个玩笑的哈，无聊——————别见怪
此
  礼！
   

Answer 14

drop table test ; --删除表create table test --创建表
(
 tname varchar2(20),
 p  varchar2(20)
);insert into test select '中国人','中国' from dual union all --插入数据
select '日本人' ,'日本' from dual union all
select '美国人' ,'美国' from dual union all
select '荷兰人' ,'荷兰' from dual union all
select '西班牙人' ,'西班牙' from dual union all
select '中国广东人' ,'中国' from dual union all
select '中国香港人' ,'中国' from dual union all
select '中国台湾人' ,'中国' from dual;
commit;CREATE INDEX test1  on test(instr(p,'国'));
INSERT INTO test SELECT * FROM test;--循环n次，数据量到20w+
SELECT /*+index p*/ * FROM test WHERE instr(p,'国')<>0 ;
--查看explain plan
SELECT STATEMENT, GOAL = ALL_ROWS 29 806254 19350096
 TABLE ACCESS BY INDEX ROWID TEST 29 806254 19350096
  INDEX FULL SCAN TEST1 28 64496

Answer 15

 1.先对要模糊查询的字段建索引。
 2.在写入数据时在数据前后都多加一个空格
 3. 使用 LIKE ' %关键字% ' 就可以用到索引的。
  非关键性数据可以这么做，比如商品名称，用户姓名等等，如果数据比较关键，要不要这么做就要考虑下了。

Answer 16

谢谢大家，我实验了一些，用instr（）是可以走索引，但是只是根据某一个关键词见索引，换一个词就不行了，我现在的问题是，这个词是用户随便输入的，所以这种方法恐怕是不行了。
我用了CREATE INDEX doc_index ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT; 后现在系统跑了半天没有反应，可能是正在建立全文索引。

Answer 17

索引建完了，用CONTAINS (tm, '系统') > 0;果然快了不少，但是就是比 like '%系统%' 检索后 记录少了几条，呵呵，还在查原因。不管怎么说是进了一步。