请教一个关于多条件查询的千万数量级的SQL查询问题

Question

我有2张千万数量级的表，即用户表和成绩表，“用户表”里有5个字段：学号XH（字符型）、姓名XM（字符型）、出生年月CS（日期型）、身份证号SFZH（字符型）、注册时间ZC（日期型）；
“成绩表”里有2个字段：学号XH（字符型）、成绩CJ（数字型）。现要求写一个查询SQL语句，要求最优化的，查询速度最快的：1、要求这两张表要关联；2、根据姓名、出生年月、身份证号、注册时间的多条件进行查询，其中姓名和身份证号是要全模糊查询的，我原来是这样写的：select * from 用户表 a, 成绩表 b where a.XH=b.XH and XM like ''%'+Edit1.text+'%'' and a.CS>=to_date('''+Edit2.text+''','YYYY-MM-DD') and a.CS<=to_date('''+Edit3.text+''','YYYY-MM-DD') and a.SFZH like ''%'+Edit4.text+'%'' and a.ZC>=to_date('''+Edit5.text+''','YYYY-MM-DD HH24:mi:ss') and a.ZC<=to_date('''+Edit6.text+''','YYYY-MM-DD HH24:mi:ss')可是，这样写执行效率非常低（前提已经把学号、姓名、身份证号、注册时间都加了索引），请问大侠还有什么更好的写法？可以写成分页的也行，如ROWNUM>=0 and ROWNUM<=100的也行，总之，一定要执行效率高的。谢谢！

Answer 1

把这个sql的执行计划先贴上来看看

Answer 2

不好直接说什么是最优化。
like '%x%' 的情况不走索引，走全表扫描，所以，你建立的所有索引都没用。先看看执行计划

Answer 3

如果 是同时按 “姓名、出生年月、身份证号、注册时间” 做条件 且“姓名 身份证号” 全部 用like就在  “注册时间 出生年月” 这两个列上建一个索引。 不是这两个字段各建一个索引。create index idx_test on 用户表(CS,ZC);create index idx_test on 成绩表(XH); 然后你看看你上面的查询有没有快。

Answer 4

like 语句全部转化为instr    如：XM like ''%'+Edit1.text+'%'' ---->instr(XM,Edit1.text)>0

Answer 5

如果 是同时按 “姓名、出生年月、身份证号、注册时间” 做条件 且“姓名 身份证号” 全部 用like 就在  “注册时间 出生年月” 这两个列上建一个索引。 不是这两个字段各建一个索引。 create index idx_test on 用户表(CS,ZC); create index idx_test on 成绩表(XH); 然后你看看你上面的查询有没有快。 

Answer 6

你每改了点sql,看下执行计划，看哪个最优

Answer 7

在PL/SQL DEELOP中如何查看其执行计划呢？

Answer 8

都加上索引，但是你SQL执行的路径并未都走索引！如你所说，如果不做任何处理，
你只会走一个索引！那边是CS、ZC其中的一个！
如果CS和ZC已经可以将结果集限制到比较小，那么你可以去了其他两个列的索引！
如果你是在要在like列加索引：
只能加：
create index 索引名 on 表(列名[,列名]) indextype is CTXSYS.CONTEXT;
将like改写为：contains(列名,:paramete)>0
利用hint:提示下！可以让查询走到所有索引！

Answer 9

索引列加多了反而很慢，尤其是你like '%+sle_1.text'这种用法会破坏索引，不如instr(col,sle_1.text)>0速度快，就像这种列的你就不用加索引了。可以吧zc和cs这两个字段加索引，这样能利用索引。