我的表里 有两个字段需要联合查询 导致索引无效
语句是这样的:
select * from user u where  u.xing||u.ming like '%张%'
表里把 姓名字段分成两个字段 姓 和 名  ,不知道怎么建立索引了
结果模糊查询时候 8W条记录 用40秒
哪位兄弟指点一下

解决方案 »

  1.   


    在xing,和 ming 两个字段上建个普通索引,在用执行计划看看SQL,效率怎么样...create index index_name on table_name(col_name);------------------------------------------------------------------------------ 
    Blog: http://blog.csdn.net/tianlesoftware 
    网上资源: http://tianlesoftware.download.csdn.net 
    相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx 
    Q Q 群:62697716 
      

  2.   

    create index index_user_xing on user(xing); 
    create index index_user_ming on user(ming); 
      

  3.   

    模糊查询的时候 最好不要这样查询...like '%张%' 第一个字符写%的话,效率很低...select * from user u where  u.xing like '张%' || u.ming like '张%'以类似的方式查询的话,会快很多...
      

  4.   

    先谢过各们的回复
    分别加索引没有效果
    执行计划中没有使用索引。复合索引 试着也不行
    创建函数索引 还是没好使(函数索引不太会用,不知道用的对不对)
    create index pk_xm on TB_EMP CONCAT(ZW_XING,ZW_MING);
    select * from TB_EMP where  CONCAT(ZW_XING,ZW_MING) like'张%' 4楼说的 效率是能上去  但是结果集有很大的出入。
    郁闷中。。  大家再帮忙想想
      

  5.   

    不太明白你的表为什么这么设计(姓和名分开),不知可否更改
    另外最好不要用select *...这样的语句,试着去除一些与业务无关的列......
      

  6.   

    select * from user u where  u.xing like '张%' or u.ming like '张%' 
      

  7.   

    我做个实验,用了三种方法,如不用索引,怎样用索引。要具体根据你的数据库环境而定,比如数据量,参数设置等等。下面的内容供参考。OPER@tl> select * from test;AAA                  BBB                         CCC
    -------------------- -------------------- ----------
    张                   一                            1
    一                   张                            2
    张                   张                            3
    多少                 张                            4
    少                   发                            5OPER@tl> create index ind_test1 on test(aaa);索引已创建。OPER@tl> create index ind_test2 on test(bbb);索引已创建。OPER@tl> set autot traceonly
    第一种index_full_scan。6个逻辑读:
    OPER@tl> select * from test
      2  where rowid in(select rn from (
      3  (select /*+ index(test ind_test1) */
      4  aaa,rowid rn
      5  from test
      6  where aaa like '%张%'
      7  union
      8  select /*+ index(test ind_test2) */
      9  bbb,rowid rn
     10  from test
     11  where bbb like '%张%')))
     12  /
    执行计划
    ----------------------------------------------------------
    Plan hash value: 4114598729-----------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |     1 |    20 |     6  (50)| 00:00:01 |
    |   1 |  NESTED LOOPS               |           |     1 |    20 |     6  (50)| 00:00:01 |
    |   2 |   VIEW                      | VW_NSO_1  |     2 |    24 |     4  (50)| 00:00:01 |
    |   3 |    HASH UNIQUE              |           |     1 |    24 |            |          |
    |   4 |     VIEW                    |           |     2 |    24 |     4  (50)| 00:00:01 |
    |   5 |      SORT UNIQUE            |           |     2 |    16 |     4  (75)| 00:00:01 |
    |   6 |       UNION-ALL             |           |       |       |            |          |
    |*  7 |        INDEX FULL SCAN      | IND_TEST1 |     1 |     8 |     1   (0)| 00:00:01 |
    |*  8 |        INDEX FULL SCAN      | IND_TEST2 |     1 |     8 |     1   (0)| 00:00:01 |
    |   9 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |     8 |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
    ---------------------------------------------------   7 - filter("AAA" LIKE '%张%')
       8 - filter("BBB" LIKE '%张%')
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              6  consistent gets
              0  physical reads
              0  redo size
            602  bytes sent via SQL*Net to client
            400  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              4  rows processed第二种,index_ffs,8个逻辑读:
    OPER@tl> select * from test
      2  where rowid in(select rn from (
      3  (select /*+ index_ffs(test ind_test1) */
      4  aaa,rowid rn
      5  from test
      6  where aaa like '%张%'
      7  union
      8  select /*+ index_ffs(test ind_test1) */
      9  bbb,rowid rn
     10  from test
     11  where bbb like '%张%')))
     12  /
    执行计划
    ----------------------------------------------------------
    Plan hash value: 2460625359-----------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |     1 |    20 |     7  (43)| 00:00:01 |
    |   1 |  NESTED LOOPS               |           |     1 |    20 |     7  (43)| 00:00:01 |
    |   2 |   VIEW                      | VW_NSO_1  |     2 |    24 |     5  (40)| 00:00:01 |
    |   3 |    HASH UNIQUE              |           |     1 |    24 |            |          |
    |   4 |     VIEW                    |           |     2 |    24 |     5  (40)| 00:00:01 |
    |   5 |      SORT UNIQUE            |           |     2 |    16 |     5  (60)| 00:00:01 |
    |   6 |       UNION-ALL             |           |       |       |            |          |
    |*  7 |        INDEX FAST FULL SCAN | IND_TEST1 |     1 |     8 |     2   (0)| 00:00:01 |
    |*  8 |        INDEX FULL SCAN      | IND_TEST2 |     1 |     8 |     1   (0)| 00:00:01 |
    |   9 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |     8 |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
    ---------------------------------------------------   7 - filter("AAA" LIKE '%张%')
       8 - filter("BBB" LIKE '%张%')
    统计信息
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              8  consistent gets
              0  physical reads
              0  redo size
            602  bytes sent via SQL*Net to client
            400  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              4  rows processed第三种,fts,8个。等同于你原来未改造的sql
    OPER@tl> select * from test where aaa like '%张%' or  bbb like '%张%';
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1357081020--------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     8 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter("AAA" LIKE '%张%' OR "BBB" LIKE '%张%')
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              8  consistent gets
              0  physical reads
              0  redo size
            602  bytes sent via SQL*Net to client
            400  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              4  rows processed
      

  8.   

    首先这种语句, 在没有说明有无主健的情况下,不可能简单的通过union 或union all来改造, 会有错误的结果产生:tb:
    f1 f2 f3
    a  a  b
    a  b  c
    a  a  b
    a  b  d原语句
    select f1,f2 from from tb where f2 like '%b%' or f3 like '%b%'
    结果
    a a 
    a b 
    a a 
    a b
    用union ,不用说,排重了。 结果行数少了两行。用union all, 也会有类似的情况, 具体数据例子就不举了。如果楼主做了这样的表结构,姓、名 列长度应该不很长, 你需要考查一下你表结构及数据存储空间占用的情况,可行的话,加一列, 把,姓+名 存储起来
    以增加数据冗余为代价换取性能。
      

  9.   

    你的表数据量大了 不管你用什么索引 只要你用%% 就会慢 还是做下fulltext吧
      

  10.   

    谢谢大家指点  学到不少东西  哈哈。
    感觉改造SQL的话 实在是麻烦 还是把姓+名存起来省事一点 
    分不多 包含