如下sql,以前执行所需时间大概5-6分钟,昨天开始执行了整整一个晚上耗时近8个小时,还望各位大侠指点下
sjcj_rydjbxx r,sjcj_fwdjbxx f 两张表都为分区表 r表近1000万的数据,以rzf_xzdxzqh分区,f表近100万数据,以bih_regioncode分区
SELECT c.cd_name "街乡名称", r.rs "人数"
FROM (SELECT *
FROM cdg_regioncode
WHERE cd_id LIKE '110105___000'
AND cd_id <> '110105000000'
AND cd_availability = '1') c
LEFT JOIN
(SELECT substr(r.rzf_xzdxzqh,1,9)||'000' xzqh, COUNT (1) rs
FROM sjcj_rydjbxx r,sjcj_fwdjbxx f
WHERE f.bih_id = r.rzf_fwbh
AND f.fdj_sfzc = '0'
AND f.fwzjbxxdjb_fwzbh IS NOT NULL
AND f.bih_hou_constructiontype = '3'
AND f.fcz_czlx ='08'
AND r.rdj_sfzc = '0'
AND r.fwzjbxxdjb_fwzbh IS NOT NULL
AND r.rdj_sfyx = '1'
AND r.rzf_zslx = '02'
AND r.rzf_xzdxzqh like '110105%'
GROUP BY substr(r.rzf_xzdxzqh,1,9)) r ON c.cd_id = r.xzqh
ORDER BY c.cd_id
执行前对表进行过分析
SQL> EXEC DBMS_STATS.GATHER_table_STATS (OWNNAME => 'BJSLG', TABNAME => 'SJCJ_RYDJBXX',METHOD_OPT =>
'FOR ALL INDEXED COLUMNS',DEGREE =>4,CASCADE=>TRUE);
PL/SQL 过程已成功完成。
SQL> set timing on;
SQL> set pagesize 1000;
SQL> set linesize 1000;
SQL> set long 10000
SQL>
SQL> EXEC DBMS_STATS.GATHER_table_STATS (OWNNAME => 'BJSLG', TABNAME => 'SJCJ_FWDJBXX',METHOD_OPT =>
'FOR ALL INDEXED COLUMNS',DEGREE =>4,CASCADE=>TRUE)
PL/SQL 过程已成功完成。
已用时间: 00: 02: 48.87
SQL>
---------------------------------索引信息----------------------------------------------------------------------
SQL> select INDEX_NAME,TABLE_NAME,INDEX_TYPE,UNIQUENESS from user_indexes i where i.TABLE_NAME='SJCJ
_FWDJBXX';
INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES
------------------------------ ------------------------------ --------------------------- ---------
INDEX_BIH_HOWNERTYPE SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_FWDJBXX_BIH_HLOCUS_ADD SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_FWDJBXX_FDJ_DJRQ SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_FWDJBXX_FDJ_FWDJDBH SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_FWDJBXX_HOWNER_FZXM SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_FWZJBXXDJB_FWZBH SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_FWDJBXX_BIH_PROPERTYR SJCJ_FWDJBXX NORMAL NONUNIQUE
PK_SJCJ_FWDJBXX SJCJ_FWDJBXX NORMAL UNIQUE
INDEX_BIH_REGIONCODE SJCJ_FWDJBXX NORMAL NONUNIQUE
PK_SJCJ_FWDJBXH SJCJ_FWDJBXX NORMAL UNIQUE
INDEX_FCZ_CHZLX SJCJ_FWDJBXX NORMAL NONUNIQUE
INDEX_CONSTRUCTIONTYPE SJCJ_FWDJBXX NORMAL NONUNIQUE
已选择12行。
已用时间: 00: 00: 00.03
----------------------------------------
SQL> select INDEX_NAME,TABLE_NAME,INDEX_TYPE,UNIQUENESS from user_indexes i where i.TABLE_NAME='SJCJ
_RYDJBXX';
INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES
------------------------------ ------------------------------ --------------------------- ---------
INDEX_RDJ_HID SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_RZF_FWBH SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_RZF_XZDXZQH SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_RY_FWZJBXXDJB_FWZBH SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_RDJ_DJRQ SJCJ_RYDJBXX NORMAL NONUNIQUE
PK_SJCJ_RYDJBXH SJCJ_RYDJBXX NORMAL UNIQUE
INDEX_BIP_XM SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_BIP_SFZHM SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_RDJ_GLYBM SJCJ_RYDJBXX NORMAL NONUNIQUE
INDEX_BIP_XB SJCJ_RYDJBXX NORMAL NONUNIQUE
已选择10行。
已用时间: 00: 00: 00.07
[本帖最后由 jym2002 于 2011-7-20 11:38 编辑]