如何优化我的这条统计SQL语句？以解决TEMP表增长过快？

Question

目前我做了一个电业局的教育培训管理系统
主表有三张：
PXGL_CAMPAIGN_DA 培训档案项目表（约4000+)
PXGL_CAMPAIGN_DA_LIST 人员参训信息表(约60000+)
PXGL_USERS 人员档案表（每月15000+条数据）
附表（代码库表）有：
PXGL_UNIT 单位名称表(26个单位）
PXGL_TYPES 培训类别表（10类培训）
表字段:
PXGL_UNIT 字段
  UNITNAME VARCHAR2(50) default '',（单位名称）
  UNITID   VARCHAR2(10) default ''（单位编号）PXGL_CAMPAIGN_DA 主要字段
  ID              NUMBER(8) not null, （ID编号）
  CAM_TYPE        VARCHAR2(20) default '', （培训类别）
  CAM_NAME        VARCHAR2(600) default '',（培训名称）
  PXSTARTDATE     DATE,(培训开始时间 to_char(PXSTARTDATE,'yyyy')='?' 那个年度执行的培训项目的条件)
  PXENDDATE       DATE,（培训结束时间)
  CAM_UNITID      VARCHAR2(10) default '',(主办单位编号）PXGL_CAMPAIGN_DA_LIST表主要字段：
  ID            NUMBER(8) not null,
  CAM_UNITID    VARCHAR2(10) default '',（参训时人员所在的单位编号）
  IDENTITY_ID   VARCHAR2(18) default '',（参训人员身份证号）
  USERNAME      VARCHAR2(50) default '',（参训人员姓名）
  CAM_ID        NUMBER(8) default 0,（培训项目ID）PXGL_USERS 表（该表数据由全民及非全民人员组成，全民人员每个月导入一次约15000条，记录存储已做分区，如一月存在Pt_1分区，二月存在Pt_2分区,非他民存在Pt_13分区）
  ID          NUMBER(20) default 0 not null,
  YEARS       NUMBER(8) default 0,（导入数据库的年份）
  MONTHS      NUMBER(8) default 0,（导入数据库的月份）
  RYSF        VARCHAR2(10) default '',（人员身份，全民非全民）
  UNITID      VARCHAR2(3) default '',（单位编号）
  UNITNAME    VARCHAR2(50) default '',（单位名称）
  USERNAME    VARCHAR2(20) default '',（姓名）
  IDENTITY_ID VARCHAR2(21) default '',（身份证号）
　PT_ID       NUMBER(2) default 1,（存储分区）
注：导入数据库的年份、月份只有全民人员有，非全民人员都为0
表关联关系：
Pxgl_unit(unitid)-Pxgl_users(unitid)
Pxgl_unit(unitid)-PXGL_CAMPAIGN_DA(unitid)
Pxgl_unit(unitid)-PXGL_CAMPAIGN_DA_LIST(cam_unitid)
PXGL_CAMPAIGN_DA(id)-PXGL_CAMPAIGN_DA_LIST(cam_id)
PXGL_CAMPAIGN_DA_LIST(IDENTITY_ID)-PXGL_USERS(IDENTITY_ID)
要求做到：统计每年每个单位参加的各类培训全民人数有多少人？

Answer 1

以下是我写的SQL语句
v_years  in number-2006,v_months in number-6
select        pxgl_unit.unitid,
              pxgl_unit.unitname,
              gqpx.gqpx_count,
              czsgpx.czsgpx_count,
              gwpx.gwpx_count,
              gwxx.gwxx_count,
              stpx.stpx_count,
              lgpx.lgpx_count,
              zyzgpx.zyzgpx_count,
              gjhzpx.gjhzpx_count,
              zzxlpx.zzxlpx_count,
              qtpx.qtpx_count
         from (select unitid, unitname from pxgl_unit) pxgl_unit,
              (select b1.unitid, count(*) as gqpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id, cam_unitid, cam_cm
                                 from pxgl_campaign_da
                                where cam_type = '岗前培训') b
                        where a.cam_id = b.id
                          and (a.cam_unitid = b.cam_unitid and
                              b.cam_cm in ('部门', '单位') or
                              cam_cm = '单位')
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) gqpx,
              (select b1.unitid, count(*) as czsgpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '持证上岗培训') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) czsgpx,
              (select b1.unitid, count(*) as gwpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '岗位培训') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) gwpx,
              (select b1.unitid, count(*) as gwxx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '岗位学习') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) gwxx,
              (select b1.unitid, count(*) as stpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '师徒培训') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) stpx,
              (select b1.unitid, count(*) as lgpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '离岗轮训') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) lgpx,
              (select b1.unitid, count(*) as zyzgpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '职业资格培训') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) zyzgpx,

Answer 2

(select b1.unitid, count(*) as gjhzpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '国际合作培训') b
                        where a.cam_id = b.id
                          and length(a.identity_id) > 0) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) gjhzpx,
              (select b1.unitid, count(*) as zzxlpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '在职学历学位教育') b
                        where a.cam_id = b.id) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) zzxlpx,
              (select b1.unitid, count(*) as qtpx_count
                 from (select a.identity_id, a.cam_unitid
                         from (select identity_id, cam_id, cam_unitid
                                 from pxgl_campaign_da_list) a,
                              (select id
                                 from pxgl_campaign_da
                                where cam_type = '其它培训') b
                        where a.cam_id = b.id) a1,
                      (select identity_id, unitid
                         from pxgl_users
                        where months = v_months
                          and years = v_years
                          and gwzt != '内退') b1
                where a1.identity_id = b1.identity_id
                  and a1.cam_unitid = b1.unitid
                group by b1.unitid) qtpx
        where pxgl_unit.unitid = gqpx.unitid(+)
          and pxgl_unit.unitid = czsgpx.unitid(+)
          and pxgl_unit.unitid = gwpx.unitid(+)
          and pxgl_unit.unitid = gwxx.unitid(+)
          and pxgl_unit.unitid = stpx.unitid(+)
          and pxgl_unit.unitid = lgpx.unitid(+)
          and pxgl_unit.unitid = zyzgpx.unitid(+)
          and pxgl_unit.unitid = gjhzpx.unitid(+)
          and pxgl_unit.unitid = zzxlpx.unitid(+)
          and pxgl_unit.unitid = qtpx.unitid(+));如何提高执行速度并不会使TEMP表空间增长过快？？

Answer 3

这样看行否
  select 
  (select col_name from table_yy where col_id=a.col_id)
   from table_xx a

Answer 4

可以试试decode或case处理你的不同培训类别,再累加一下.
例如:    select  pxgl_unit.unitid,
            pxgl_unit.unitname,
            sum(decode(cam_type,'岗前培训',1,0) '岗前培训',
            sum(decode(cam_type,'持证上岗培训') '持证上岗培训',
            ....
            sum(decode(cam_type,'它培训') '它培训',
     from   ....
      where ....
  group by  pxgl_unit.unitid,
            pxgl_unit.unitname

Answer 5

楼主能先大体介绍一下这么长的语句是用来统计什么的么
然后整理或者简化一下SQL语句
看不清了