关于一个SQL语句的优化

显示全部楼层 · 2014-2-19 11:55:14

高手们,我有一个问题想请教大家,
帮我看看这个 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 <= 2000

2000 rows selected.
Elapsed: 00:02:39.78
Execution 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
----------------------------------------------------------

0recursive calls

0db block gets
740366consistent gets
731647physical reads

0redo size
136707bytes sent via SQL*Net to client
1848bytes received via SQL*Net from client
135SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)
2000rows processed

千问 · 2014-2-19 11:55:14

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
ORDER BY PUBLISHDATE DESC

跟这么写有什么区别吗？

千问 · 2014-2-19 11:55:14

该表建立了索引了吗？？？
你的网络是否正常呢？？？？

千问 · 2014-2-19 11:55:14

SELECT count(*)
FROM LX_DOCS WHERE GROUPID is NULL
OR ISMAIN is NULL OR GROUPID='3391' OR
ISMAIN='1' ;
select count(*) from LX_DOCS;
分别返回多少记录

千问 · 2014-2-19 11:55:14

原帖由 qingfengjushi1 于 2008-10-31 16:43 发表
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
业务理解上不同写出来的代码肯定就不同了！！

千问 · 2014-2-19 11:55:14

原帖由 qingfengjushi1 于 2008-10-31 16:43 发表
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

SELECT SYNID, TITLE, DOCSRC, DOCFLAG, TNAME
FROM WHERE (GROUPID is NULL OR ISMAIN is NULL OR GROUPID = '3391' OR

ISMAIN = '1')
and ROWNUM <= 2000
ORDER BY PUBLISHDATE DESC

千问 · 2014-2-19 11:55:14

原帖由 棉花糖ONE 于 2008-10-31 16:44 发表
SELECT count(*)
FROM LX_DOCS WHERE GROUPID is NULL
OR ISMAIN is NULL OR GROUPID='3391' OR
ISMAIN='1' ;
select count(*) from LX_DOCS;
分别返回多少记录

SELECT count(*)
FROM LX_DOCS WHERE GROUPID is NULL
OR ISMAIN is NULL OR GROUPID='3391' OR
ISMAIN='1' ;

COUNT(*)
----------
3710755
Elapsed: 00:01:46.78
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 198K(1)|
| 1 |SORT AGGREGATE| | 1 | 9 |
|
| 2 | TABLE ACCESS FULL| LX_DOCS |3590K|30M| 198K(1)|
-------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
740431consistent gets
733953physical reads

0redo size
414bytes sent via SQL*Net to client
385bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

select count(*) from LX_DOCS;

COUNT(*)
----------
7457541
Elapsed: 00:00:06.25
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT|
| 1 |5769 (3)|
| 1 |SORT AGGREGATE |
| 1 |
|
| 2 | INDEX FAST FULL SCAN| LX_DOCS_PK11216775680828 |7256K|5769 (3)|
-------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
23123consistent gets
14753physical reads

0redo size
414bytes sent via SQL*Net to client
385bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

千问 · 2014-2-19 11:55:14

create index i_LX_DOCS_1 on LX_DOCS(PUBLISHDATE desc);
SELECT SYNID,TITLE,DOCSRC,DOCFLAG,TNAME
FROM (SELECT /*+ index(t i_LX_DOCS_1) */ SYNID,TITLE,DOCSRC,DOCFLAG,
TNAME FROM LX_DOCS t WHERE (GROUPID is NULL
OR ISMAIN is NULL OR GROUPID='3391' OR
ISMAIN='1' ) and LX_DOCS is not null ORDER BY PUBLISHDATE DESC) WHERE
* ROWNUM <= 2000
试试这个

千问 · 2014-2-19 11:55:14

不过想不明白你这sql怎么会这样感觉怪怪的,我觉得应该是
(GROUPID is NULL or GROUPID='3391') and
ISMAIN is NULL OR
ISMAIN='1' )
这样比较对吧

千问 · 2014-2-19 11:55:14

七百多万笔数据中取三百多万笔数据, 还建什么索引哦, 全表扫描还快些.
最多在order by字段上建个索引.