oracle sql 语句优化--（难）高手请进 ( 积分: 200 )

SELECT ROWNUM RN, IT.*
FROM (SELECT IT.ID,
DECODE(IT.OPERTYPE,
1,
IT.OBJECTID1,
DECODE(IT.OBJECTTABLENAME1,
'EQUIPMENT',
(SELECT CODE
FROM EQUIPMENT EQ
WHERE EQ.ID = IT.OBJECTID1),
'PORT',
(SELECT CODE FROM PORT P WHERE P.ID = IT.OBJECTID1),
'MODULE',
(SELECT CODE
FROM MODULE M
WHERE M.ID = IT.OBJECTID1),
'SDHUNITE',
(SELECT CODE
FROM SDHUNITE SU
WHERE SU.ID = IT.OBJECTID1),
'SDHCHANNEL',
(SELECT CODE
FROM SDHCHANNEL SC
WHERE SC.ID = IT.OBJECTID1),
'CIRCUIT',
(SELECT CODE
FROM CIRCUIT C
WHERE C.ID = IT.OBJECTID1),
'LOOP',
(SELECT CODE FROM LOOP L WHERE L.ID = IT.OBJECTID1),
'EQSLOT',
(SELECT CODE
FROM EQSLOT ES
WHERE ES.ID = IT.OBJECTID1),
'ANTENNA',
(SELECT CODE
FROM ANTENNA A
WHERE A.ID = IT.OBJECTID1),
IT.OBJECTID1)) AS OBJECTID1,
(SELECT CHNAME
FROM INFDICTM
WHERE ENNAME = IT.OBJECTTABLENAME1) || '--' ||
DECODE((SELECT TYPECODE
FROM INFDICTM
WHERE ENNAME = IT.OBJECTTABLENAME1),
0,
'生产',
1,
'中间',
2,
'外部') AS OBJECTTABLENAME1,
DECODE(IT.OPERTYPE,
2,
DECODE(IT.OBJECTTABLENAME2,
'EQUIPMENT',
(SELECT CODE
FROM EQUIPMENT EQ
WHERE EQ.ID = IT.OBJECTID2),
'PORT',
(SELECT CODE FROM PORT P WHERE P.ID = IT.OBJECTID2),
'MODULE',
(SELECT CODE
FROM MODULE M
WHERE M.ID = IT.OBJECTID2),
'SDHUNITE',
(SELECT CODE
FROM SDHUNITE SU
WHERE SU.ID = IT.OBJECTID2),
'SDHCHANNEL',
(SELECT CODE
FROM SDHCHANNEL SC
WHERE SC.ID = IT.OBJECTID2),
'CIRCUIT',
(SELECT CODE
FROM CIRCUIT C
WHERE C.ID = IT.OBJECTID2),
'LOOP',
(SELECT CODE FROM LOOP L WHERE L.ID = IT.OBJECTID2),
'ANTENNA',
(SELECT CODE
FROM ANTENNA A
WHERE A.ID = IT.OBJECTID2),
'OUTERADM',
(SELECT DISTINCT CODE
FROM OUTERADM A
WHERE A.ID = IT.OBJECTID2),
'OUTERSHELF',
(SELECT DISTINCT CODE
FROM OUTERADM A
WHERE A.ID =
SUBSTR(IT.OBJECTID2,
0,
INSTR(IT.OBJECTID2, '|') - 1)) ||
SUBSTR(IT.OBJECTID2,
INSTR(IT.OBJECTID2, '|'),
LENGTH(IT.OBJECTID2)),
'OUTERSLOT',
(SELECT DISTINCT CODE
FROM OUTERADM A
WHERE A.ID =
SUBSTR(IT.OBJECTID2,
0,
INSTR(IT.OBJECTID2, '|') - 1)) ||
SUBSTR(IT.OBJECTID2,
INSTR(IT.OBJECTID2, '|'),
LENGTH(IT.OBJECTID2)),
'OUTERPORT',
(SELECT DISTINCT CODE
FROM OUTERADM A
WHERE A.ID =
SUBSTR(IT.OBJECTID2,
0,
INSTR(IT.OBJECTID2, '|') - 1)) ||
SUBSTR(IT.OBJECTID2,
INSTR(IT.OBJECTID2, '|'),
LENGTH(IT.OBJECTID2)),
'OUTERPANNEL',
(SELECT DISTINCT CODE
FROM OUTERADM A
WHERE A.ID =
SUBSTR(IT.OBJECTID2,
0,
INSTR(IT.OBJECTID2, '|') - 1)) ||
SUBSTR(IT.OBJECTID2,
INSTR(IT.OBJECTID2, '|'),
LENGTH(IT.OBJECTID2)),
IT.OBJECTID2)) AS OBJECTID2,
(SELECT CHNAME
FROM INFDICTM
WHERE ENNAME = IT.OBJECTTABLENAME2) || '--' ||
DECODE((SELECT TYPECODE
FROM INFDICTM
WHERE ENNAME = IT.OBJECTTABLENAME2),
0,
'生产',
1,
'中间',
2,
'外部') AS OBJECTTABLENAME2,
IT.OPERUSER,
DECODE(IT.OPERKIND,
0,
'中间表与厂商',
1,
'中间表与TP生产表',
2,
'TP生产表与厂商') AS OPERKIND,
DECODE(IT.OPERTYPE, 0, '增加', 1, '删除', 2, '修改') AS OPERTYPE,
DECODE(IT.ISSUCCEED, 0, '不成功', 1, '成功') AS ISSUCCEED,
IT.BEGINDATETIME,
IT.ENDDATETIME,
IT.OPERDESC,
DECODE(IT.OBJECTTABLENAME2,
'OUTERMS',
NVL((SELECT DISTINCT NAME
FROM OUTERMS A
WHERE A.ID = IT.OBJECTID2),
IT.OBJECTID2),
'SDHUNITE',
NVL((SELECT DISTINCT NAME
FROM SDHUNITE SU
WHERE SU.ID = IT.OBJECTID2),
IT.OBJECTID2),
'') AS OUTERMSNAME
FROM INFINTERFACELOG IT
WHERE REQUESTID = '1003134673'
and 1 = 1) IT

好長啊。都看沒了。。。。

我建议LZ使用存储过程来做,如果写这么长的SQL语句,是不对的

哈哈，不算长，但看的眼花啊，有没有看到30多页的SQL语句,像这样的，你能不能多写几个视
图，再将视图连接起来分析也容易点

这是以前一个人写的，现在我接手他的任务。
客户反映速度慢，得优化。
不过我不知道如何优化才好啊
jfyes。能不能给个例子啊
[]

如果INFINTERFACELOG的数据量很多，并且没有建立相应的索引，是很慢。

有些表有索引，不过有些表是连接到外部库的，象那些OUTER开头的
，不知道各位有没遇过类似的情况。
还有，把这些记录显示在LISTVIEW上，
2000多条差不多30秒多，但把找到的记录导到EXCEL，非常的慢
2000多条差不多要3分钟，为什么。
问下，TDATASET保存的数据集
是在本地机子上的吗，
还是每次DATASETV.NEXT都要从数据库里读一条记录 ？

是不是没有从页面出数据，从数据集往excel直接导的

做成视图，在视图上分析，看看是那个地方查询，在优化那儿，你这样的代码光看懂就要理清你的整个数据库。
在Oracle里isqlplus查询分
显示查询时间 set timing on; SQL执行计划,显示成本：set autotrace on;

set timing on;
set autotrace on;
select * from dual;


DUM
X


已用时间: 00: 00: 00.00

执行计划
----------------------------------------------------------


Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------

0 recursive calls
0 db block gets
3 consistent gets
0 physical reads --物理读写，读写的次数少，查询的效率高
0 redo size
404 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)


1 rows processed


这样分析容易多了吧

如果数据内容固定不经常更新，且数据相同的量大,建议用位图索引，我的2000多W的数据量，Oracle10G查询速度只要秒级速度.

位图索引的介绍：
http://www.delphibbs.com/keylife/iblog_show.asp?xid=28156
建好索引很重要，如果数据量像你的2000多条，可能是你的SQL代码太冗长，用的字符串function多了全表扫描处理慢，最好建function索引。

ffanpeng, 是的
问题还没解决，继续

貌似很复杂，同学习，接个小分，谢谢

建议写存储过程
用视图还是会慢的

貌似很复杂

在decode中执行查询，这样执行查询的次数比较多，把小表数据合并，减少decode如何函数的执行次数，我看到的基本都是id＝...，所以，可以先把这些小表合并，然后再处理