ORACLE递归查询优化

Question

SELECT /*RULE*/ T2.COMPCODE,
       T2.USERNAME,
       T2.NAME,
       T2.EMAIL,
       T2.CREATIONDATE,
       T2.MODIFICATIONDATE,
       T2.DEPTCODE,
       SUBSTR(T1.DEPTNAME, 3),
       T2.PRIORITY,
       T1.PRIORITY
  FROM (SELECT DISTINCT A2.COMPCODE,
                        A2.DEPTCODE,
                        A2.PRIORITY,
                        SYS_CONNECT_BY_PATH(A2.DEPTNAME, '::') AS DEPTNAME
          FROM (SELECT COMPCODE, DEPTCODE
                  FROM OFDEPARTMENT
                 WHERE PARENTDEPTCODE IS NULL) A1,
               OFDEPARTMENT A2
         START WITH A2.DEPTCODE = A1.DEPTCODE
        CONNECT BY A2.PARENTDEPTCODE = PRIOR A2.DEPTCODE) T1,
       OFUSER T2
 WHERE T2.COMPCODE = 'test1865'
   AND T2.USERTYPE = 0
   AND T1.COMPCODE = T2.COMPCODE
   AND T1.DEPTCODE = T2.DEPTCODE
   and rownum <= 150这个语句很慢，谁能帮我优化一下，
其中SELECT DISTINCT A2.COMPCODE,
                        A2.DEPTCODE,
                        A2.PRIORITY,
                        SYS_CONNECT_BY_PATH(A2.DEPTNAME, '::') AS DEPTNAME
          FROM (SELECT COMPCODE, DEPTCODE
                  FROM OFDEPARTMENT
                 WHERE PARENTDEPTCODE IS NULL) A1,
               OFDEPARTMENT A2
         START WITH A2.DEPTCODE = A1.DEPTCODE
        CONNECT BY A2.PARENTDEPTCODE = PRIOR A2.DEPTCODE
这个递归语句很慢 如果数据量很大 的话-- Create table
create table OFUSER
(
  USERNAME          VARCHAR2(64) not null,
  PLAINPASSWORD     VARCHAR2(32),
  ENCRYPTEDPASSWORD VARCHAR2(255),
  NAME              VARCHAR2(100),
  EMAIL             VARCHAR2(100),
  CREATIONDATE      CHAR(15) default 0 not null,
  MODIFICATIONDATE  CHAR(15) default 0 not null,
  COMPCODE          VARCHAR2(32) default -1 not null,
  DEPTCODE          VARCHAR2(32) default -1 not null,
  USERCODE          VARCHAR2(32),
  USERTYPE          VARCHAR2(2) default 0 not null,
  COMMTYPE          VARCHAR2(2),
  AUTHTYPE          VARCHAR2(2),
  CAPATH            VARCHAR2(255),
  PRIORITY          VARCHAR2(16) default -1 not null,
  BIZUSERID         VARCHAR2(32),
  SYNCDATE          VARCHAR2(16),
  SYNCFLAG          VARCHAR2(2),
  OPERATORID        VARCHAR2(2),
  REMARK            VARCHAR2(1024),
  HIDE              VARCHAR2(2) default 0 not null,
  LASTLOGINDATE     VARCHAR2(16),
  ENABLED           VARCHAR2(2) default 1 not null
)
tablespace OPENFIRE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table OFUSER
  add constraint OFUSER_PK primary key (USERNAME)
  using index 
  tablespace OPENFIRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table OFUSER
  add constraint OFUSER_FK1 foreign key (COMPCODE)
  references OFCOMPANY (COMPCODE) on delete cascade;
alter table OFUSER
  add constraint OFUSER_FK2 foreign key (COMPCODE, DEPTCODE)
  references OFDEPARTMENT (COMPCODE, DEPTCODE) on delete cascade;
-- Create/Recreate indexes 
create index OFUSER_CDATE_IDX on OFUSER (CREATIONDATE)
  tablespace OPENFIRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
用户表结构
-- Create table
create table OFDEPARTMENT
(
  COMPCODE       VARCHAR2(32) not null,
  DEPTCODE       VARCHAR2(32) not null,
  DEPTNAME       VARCHAR2(128) not null,
  DEPTDESC       VARCHAR2(255),
  DEPTTYPE       VARCHAR2(2),
  AREACODE       VARCHAR2(2),
  STATUS         VARCHAR2(2),
  HIDE           VARCHAR2(2) default 0 not null,
  REMARK         VARCHAR2(255),
  PRIORITY       VARCHAR2(16) default -1 not null,
  OPERATORID     VARCHAR2(32),
  CREATEDATE     VARCHAR2(15),
  MODIFYDATE     VARCHAR2(15),
  BIZDEPTCODE    VARCHAR2(32),
  SYNCDATE       VARCHAR2(16),
  SYNCFLAG       VARCHAR2(2),
  ENABLED        VARCHAR2(2) default 1 not null,
  PARENTDEPTCODE VARCHAR2(32)
)
tablespace OPENFIRE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table OFDEPARTMENT
  add constraint OFDEPARTMENT_PK primary key (COMPCODE, DEPTCODE)
  using index 
  tablespace OPENFIRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table OFDEPARTMENT
  add constraint OFDEPARTMENT_FK foreign key (COMPCODE)
  references OFCOMPANY (COMPCODE) on delete cascade;
部门表结构谁能帮我优化一下，谢谢

Answer 1

１．DISTINCT 在这里对结果没有影响，考虑不要．２．WHERE T2.COMPCODE = 'test1865'
  AND T2.USERTYPE = 0
  AND T1.COMPCODE = T2.COMPCODE
  AND T1.DEPTCODE = T2.DEPTCODE
  and rownum <= 150 改为
WHERE  T1.COMPCODE = T2.COMPCODE
  AND T1.DEPTCODE = T2.DEPTCODE
  and rownum <= 150
　and T2.COMPCODE = 'test1865'
  AND T2.USERTYPE = 0 试试看．
３．这个ｓｑｌ，貌似可以不用表连接．楼主再考虑考虑，一个建议：SELECT DISTINCT A2.COMPCODE,
                        A2.DEPTCODE,
                        A2.PRIORITY,
                        SYS_CONNECT_BY_PATH(A2.DEPTNAME, '::') AS DEPTNAME
          FROM (SELECT COMPCODE, DEPTCODE
                  FROM OFDEPARTMENT
                WHERE PARENTDEPTCODE IS NULL) A1,
              OFDEPARTMENT A2
        START WITH A2.DEPTCODE = A1.DEPTCODE
        CONNECT BY A2.PARENTDEPTCODE = PRIOR A2.DEPTCODE