关于一个SQL语句的优化

Question

关于一个SQL语句的优化
高手们,我有一个问题想请教大家,
帮我看看这个 SQL 语句应该怎么优化,现在数据库这个SQL 语句的使用频率很高,但是?
执行效果却不是很理想,以下是这个语句生成的报告,
请大家帮我分析分析::SELECT SYNID,TITLE,DOCSRC,DOCFLAG,TNAME
FROM (SELECT SYNID,TITLE,DOCSRC,DOCFLAG,
TNAME FROM LX_DOCS WHERE GROUPID is NULL
OR ISMAIN is NULL OR GROUPID='3391' OR
ISMAIN='1' ORDER BY PUBLISHDATE DESC) WHERE
* ROWNUM <= 20002000 rows selected.Elapsed: 00:02:39.78Execution Plan
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |  2000 |  1644K|   198K  (1)|
|   1 |  COUNT STOPKEY          |         |       |       |            |
|   2 |   VIEW                  |         |  3590K|  2883M|   198K  (1)|
|   3 |    SORT ORDER BY STOPKEY|         |  3590K|   287M|   198K  (1)|
|   4 |     TABLE ACCESS FULL   | LX_DOCS |  3590K|   287M|   198K  (1)|
------------------------------------------------------------------------Note
-----
   - 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     740366  consistent gets
     731647  physical reads
          0  redo size
     136707  bytes sent via SQL*Net to client
       1848  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2000  rows processed

Answer 1

有建索引么？把where条件的字段都建个索引吧

Answer 2

/--order by PUBLISHDATE DESC,重要吗？如果不是，改一下：
 
SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
  FROM (SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
          FROM LX_DOCS
         WHERE GROUPID is NULL
            OR ISMAIN is NULL
            OR GROUPID = '3391'
            OR ISMAIN = '1')
 WHERE * ROWNUM <= 2000;
 
 
/--还可以试一下：SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
  FROM (SELECT rownum rn,SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
          FROM LX_DOCS
         WHERE GROUPID is NULL
            OR ISMAIN is NULL
            OR GROUPID = '3391'
            OR ISMAIN = '1'
         ORDER BY PUBLISHDATE DESC) tab
 WHERE tab.rn <= 2000;
 

Answer 3

还有GROUPID='3391' OR
ISMAIN='1'
如果GROUPID和ISMAIN字段不是字符型的，是int型的话就不要查询字符串，去掉单引号查询，当然，如果是字符类型的就不能优化了

Answer 4

3590K的表，走全表扫描
groupid 和 isman 这2个字段上有索引吗？
据字段函义分析，ismain 这个字段不需要建索引。
考虑到按publishdate排序，取大的2000条，所以在这个字段上建索引create index idx_lx_docs_x1 ON lx_docs(publishdate);--分析这个索引
dbms_stats.GATHER_INDEX_STATS(user, 'idx_lx_docs_x1');SELECT SYNID,TITLE,DOCSRC,DOCFLAG,TNAME 
  FROM (
SELECT /*+ index(idx_lx_docs_x1) */
   SYNID,TITLE,DOCSRC,DOCFLAG, TNAME 
  FROM LX_DOCS 
 WHERE (GROUPID is NULL OR GROUPID='3391')
    OR /*这个or 会不会是and? */
     (ISMAIN is NULL  OR ISMAIN='1')
   ORDER BY PUBLISHDATE DESC
  ) 
 WHERE ROWNUM <= 2000 

Answer 5

请把我写的，建了索引并加了hint的执行计划帖出来。

Answer 6

这个 publishdate 有个索引 是 "LX_DOCS_PUBLISHDATE"但是您这个是这么执行吗:
dbms_stats.GATHER_INDEX_STATS(trswcm, 'LX_DOCS_PUBLISHDATE');不能执行

Answer 7

--分析这个索引
dbms_stats.GATHER_INDEX_STATS(user, 'idx_lx_docs_x1');单独执行这一句，不需要改user的，要不就是：
dbms_stats.GATHER_INDEX_STATS('你的用户名', 'idx_lx_docs_x1');

Answer 8

那就把idx_lx_docs_x1 改成 LX_DOCS_PUBLISHDATEsql语句也里的也一样，然后看看执行计划

Answer 9

SQL> create index idx_lx_docs_x1 ON lx_docs(publishdate);
create index idx_lx_docs_x1 ON lx_docs(publishdate)
                               *
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.08然后找到了已经存在的索引是： LX_DOCS_PUBLISHDATE  -----   PUBLISHDATE
分析这个索引 
dbms_stats.GATHER_INDEX_STATS('trswcm', 'LX_DOCS_PUBLISHDATE');
SQL> dbms_stats.GATHER_INDEX_STATS('trswcm', 'LX_DOCS_PUBLISHDATE');
SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored.

Answer 10

sql>exec dbms_stats.GATHER_INDEX_STATS('trswcm', 'LX_DOCS_PUBLISHDATE'); 

Answer 11

为什么要用个临时表?SELECT SYNID,TITLE,DOCSRC,DOCFLAG, 
TNAME FROM LX_DOCS WHERE GROUPID is NULL 
OR ISMAIN is NULL OR GROUPID='3391' OR 
ISMAIN='1' and ROWNUM <= 2000 
ORDER BY PUBLISHDATE DESC

Answer 12

先：alter session set optimizer_mode=first_rows；
然后执行4楼的：
SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
  FROM (SELECT rownum rn,SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
          FROM LX_DOCS
         WHERE GROUPID is NULL
            OR ISMAIN is NULL
            OR GROUPID = '3391'
            OR ISMAIN = '1'
         ORDER BY PUBLISHDATE DESC) tab
 WHERE tab.rn <= 2000;
看看是不是会快点？

Answer 13

试试下面的方法:
 CREATE INDEX i_test1 ON lx_docs(nvl(groupid,'x'),nvl(ismain,'x')); SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
   FROM (SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
           FROM LX_DOCS
          WHERE GROUPID IN ('3391', 'x')
             OR ISMAIN IN ('1', 'x')
          ORDER BY PUBLISHDATE DESC)
  WHERE ROWNUM <= 2000 ;

Answer 14

拉下点东西,修改后:
 CREATE INDEX i_test1 ON lx_docs(nvl(groupid,'x'),nvl(ismain,'x'));
 
 SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
   FROM (SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
           FROM LX_DOCS
          WHERE nvl(GROUPID) IN ('3391', 'x')
             OR nvl(ISMAIN) IN ('1', 'x')
          ORDER BY PUBLISHDATE DESC)
  WHERE ROWNUM <= 2000 ;