一个关于查询效率的探讨！

Question

一个表结构如下：CREATE TABLE Testtemp (
firstname varchar (15) null,
lastname varchar (15)  null,
sex char (1) null,
csrq datetime NULL ,
sfzh varchar (18) NULL)
解释：姓名有firstname和lastname组成（sql server为例，oracle修改一下即可）
有如下两个查询语句为了完成查询名字以'李明'开头的所有人：
1，
select * from testadv where ((firstname+lastname) like '李明%') or(firstname is null and lastname like '李明')
2，select * from testadv where (firstname='李' and lastname like '明%') or (firstname like '李明%') or (firstname is null and lastname like '李明%')请问上面两个sql语句是否需要优化？更重要的是上面两个哪个效率比较高？或者谁有更好的办法？(oracle修改上面的‘+’为‘||’即可)

Answer 1

sorry，上面的
CREATE TABLE Testtemp (
firstname varchar (15) null,
lastname varchar (15)  null,
sex char (1) null,
csrq datetime NULL ,
sfzh varchar (18) NULL)
应该为
CREATE TABLE testadv(
firstname varchar (15) null,
lastname varchar (15)  null,
sex char (1) null,
csrq datetime NULL ,
sfzh varchar (18) NULL)

Answer 2

select * from testadv where (firstname||lastname) like '李明%'

Answer 3

select * from testadv where ((firstname||lastname) like '李明%') or(firstname is null and lastname like '李明%')如果创建了如下索引，上面这句的效率会高一些create index iname on testadv(firstname||lastname);

Answer 4

首先不能创建索引，大家能否告知理由，因为，我用sql server 测试了30多万的记录，没有明显差别。谢谢大家！

Answer 5

hnews(抓哇)兄的有个问题，如果firstname为null，就不可能查出lastname等于‘李明’的人。

Answer 6

可以的
我也以为不可以
试了一下，没问题14:14:39 SQL> select * from tcc;AAA        BBB
---------- ----------
abc
 abc
 abc
abc        abc
%          /实际：200
14:14:47 SQL> insert into tcc values(null,'aaa');已创建 1 行。实际：400
14:15:14 SQL> select * from tcc where aaa||bbb like 'aa%';AAA        BBB
---------- ----------
           aaa实际：170
14:15:32 SQL>

Answer 7

不可能吧，因为||运算遇到null就为null了，我在sql server上是不行，null是不能通过你老兄的方式吧，直接去insert 一个null，其实你的是个'null'字符串，而不是null

Answer 8

1, insert into 可以插入null
2, ||运行操作符的操作数可以是null，结果为其余的操作数代表的字符串.
   若||的所有的操作数都为 null, 结果为null
3, 即使在(firstname||lastname)上建立index,oracle 的优化器也不会用到它.
   因为 where中是 “like”操作符.

Answer 9

To：cocolala(阿汤)老大，到底哪个效率好些？急切！

Answer 10

据我所知这种情况使用索引
where colname like 'XXX%'这种情况则不使用
where colname like '%XXX'不知道老兄在哪儿看到的资料，
我实在网上的某篇文章里面看的

Answer 11

可以这样：
create index ind_testadv_firstname on testadv(substr(firstname,1,2));
create index ind_testadv_firstname on testadv(substr(lastname,1,2));analyze table testadv compute statistics;
analyze index ind_testadv_firstname compute statistics; 
analyze index ind_testadv_lastname compute statistics;select * from testadv where substr(firstname,1,2) ='李明' or
                            substr(lastname,1,2) = '李明';

Answer 12

抓哇的好
null值参与||，只要有非空值，是不会得null的
此外如果where中用了is null则用不了索引了，因为索引中不记录键值全为null的列

Answer 13

若不建索引，仍然用
substr(firstname,1,2) ='李明' or substr(lastname,1,2) = '李明';原因：
1, substr是内置字符函数,取前面字串速度较快.
2, 利用or操作符, 当前面结果为true，不再计算后面条件.
3, '='操作，避免模糊查询.4, 以后若还需调速，可如前述建基于函数的索引(fuction-based index)

Answer 14

cocolala(阿汤)：
你的方法很好，我这里只是举个特例而已，因为中国人的名字还是比较复杂的，这里first和lastname组合起来才是这个人的完全名字，那么当查询者，输入一个'李明'，然后按照模糊查询，那么应该列出所有以李明开头的，并且fistname和lastname加起来含'李明'的所有人，用户也可能输入'%李明'等等...而这里就是想通过一个sql语句搞定，所以必须模糊。实在是比较麻烦。

Answer 15

呵呵...不是我不建立索引，而是用户不给建立索引，没办法，因为人家的表还要做其他事，我们只有查询权利，我刚才比较了，是我的第二个方法比较快，据说是数据库中'＋'（sql server中），'||'（Oracle中）操作比较慢，不知各位老大有何看法？

Answer 16

刚才重新搞了一下，TNND，两个速度又一样了，真是搞不懂啊

Answer 17

like 可以使用索引的（假定在fistname建立了索引 ），例如
fistname like '李％'
但如果这样就不可以了
fistname like '%李'
就用不了索引，而是全表扫描。

Answer 18

tnnd,花这么大劲，还不如把那边搞定，拿把刀让他建索引！！

Answer 19

下面方法能查出来firstname = '李'   &   lastname= '明';????? cocolala(阿汤) ( ) 信誉：100  2003-06-12 16:25:00  得分:0 
 
 
  若不建索引，仍然用
substr(firstname,1,2) ='李明' or substr(lastname,1,2) = '李明';原因：
1, substr是内置字符函数,取前面字串速度较快.
2, 利用or操作符, 当前面结果为true，不再计算后面条件.
3, '='操作，避免模糊查询.4, 以后若还需调速，可如前述建基于函数的索引(fuction-based index)  
 

Answer 20

原来我没有建立索引，这次，分别对firstname和lastname建立了索引，结果两种查询速度一样，更奇怪的是，和我不建索引时所进行的第二种查询，即select * from testadv where (firstname='李' and lastname like '明%') or (firstname like '李明%') or (firstname is null and lastname like '李明%')花费时间一样，看来我们不建立索引，数据库对我们的sql语句进行了特殊处理，得出结论第二种查许方法要优啊。

Answer 21

查询速度主要根据index来得额。
如果你index(firstname ,lastname),这种sql比较快点：
select * from table where firstname ='firstname' and lastname like 'lastname%';
如果你index(firstname||lastname)，这种sql比较快：
select * from table where firstname||lastname like 'name%'；