SELECT BB.* FROM (SELECT MM.* FROM (select distinct A.DOCID,A.DOCNAME as 文档名称,D.USERCNAME as 上传人,substr(A.UPLOADDATE,1,4)||'年'||substr(A.UPLOADDATE,5,2)||'月'||substr(A.UPLOADDATE,7,2)||'日' as 上传日期, A.PROJECTID,A.ACTIONID,A.DOCTYPE from (SELECT BBB. * FROM TABLEAD BBB WHERE BBB.MODEDOCID IS NULL OR BBB.MODEDOCID ='') A,TABLE06 C,TABLEREG005 D WHERE C.NORMATIVEVAL=A.DOCTYPE AND D.USERID=A.UPLOADMANID AND A.ISAUDITING!=2 AND A.DOCID NOT IN (SELECT DOCID FROM TABLEAD_1) ) MM LEFT JOIN TABLEAD07 F ON MM.ACTIONID=F.ACTIONID ) BB LEFT JOIN TABLEAD01 E ON BB.PROJECTID=E.PROCASEID例如,如上的一条SQL语句,在有的服务器上运行正常,在有的服务器(操作系统与数据库系统的版本都是一模一样的)上就是一执行就长时间无响应,但是在最外层在加一层select * from 再执行就可以正常执行了,为什么呢?
先谢了!
先谢了!
需要优化一下
select distinct A.DOCID,A.DOCNAME as 文档名称,D.USERCNAME as 上传人,
substr(A.UPLOADDATE,1,4)||'年'||substr(A.UPLOADDATE,5,2)||'月'||substr(A.UPLOADDATE,7,2)||'日' as 上传日期,
A.PROJECTID,A.ACTIONID,A.DOCTYPE,c.*,d.*,f.*,e.* from (
SELECT BBB. * FROM TABLEAD BBB WHERE
(BBB.MODEDOCID IS NULL OR BBB.MODEDOCID ='')
AND bbb.ISAUDITING!=2
and not exists (select 1 from TABLEAD_1 where docid=bbb.docid)) A,
TABLE06 C,TABLEREG005 D
WHERE C.NORMATIVEVAL=A.DOCTYPE AND
D.USERID=A.UPLOADMANID
LEFT JOIN TABLEAD07 F
ON a.ACTIONID=F.ACTIONID LEFT JOIN TABLEAD01 E
ON A.PROJECTID=E.PROCASEID
begin
dbms_stats.gather_table_stats
( ownname => 'user', --''里填用户名
tabname => 'tablename' --,填表名
--method_opt => 'for all indexed columns',
--cascade => TRUE
);
end; SELECT endpoint_number, endpoint_value,ENDPOINT_ACTUAL_VALUE
FROM(
SELECT endpoint_number, endpoint_value,ENDPOINT_ACTUAL_VALUE
FROM USER_HISTOGRAMS
WHERE table_name = 'tablename' and column_name = 'OWNER' --tablename和owner自己替换
ORDER BY endpoint_number)
WHERE rownum <=20;select num_rows,blocks,empty_blocks,avg_space,chain_cnt,
avg_row_len,avg_space_freelist_blocks,num_freelist_blocks,
sample_size,last_analyzed from user_tables where table_name=upper('tablename')
我只是想知道,为什么同样的SQL语句,为什么有的可以运行有的需要再加一层?而且我的查询中是多个表