sql语句优化[树]

[coooliang]

select t.*
                  from p_employee t,
                       (select distinct (t1.employee_id)
                          from p_employee_sta_relat t1,
                               (select org_id
                                  from (select org_id, parent_id
                                          from o_org
                                         where USED_STATE = 1)
                                connect by prior org_id = parent_id
                                 start with org_id = #orgId#) t2
                         where t1.org_id = t2.org_id
                         and t1.recond_state='1') t3
                 where t.employee_id = t3.employee_id
                  and t.property_c <> '4'


<isNotEmpty>......</isNotEmpty>

之所以会这样写是因为现在的系统是用xtree当点击左边树中的一个节点,就会根据org_id查询出这个部门下面所有员工的列表。
其中o_org这个表是人力资源表，有很多部门所以有3万条左右，所以根据org_id进行树查询会很慢，数据库不在本地，一般要等6~7秒左右吧。
可是数据库字段里没有parent_id所以只能通过oracle的树查询所到内容。因为这条语句是写在ibatis中的所以我不会用存储过程因为：分页类会加上这条SQL语句得到总条数：select count(*) as totailCount from {call queryList}......//就会出错。只能优化现有SQL了。

请问：如果使用原来的树查询数据的方式如何优化这条SQL语句。

[BenChiM888]

--试试这个
SELECT T.*
  FROM P_EMPLOYEE T,
       (SELECT /*+ FIRST_ROWS */
               T1.EMPLOYEE_ID
          FROM P_EMPLOYEE_STA_RELAT T1, O_ORG T2,
         WHERE T2.USED_STATE = 1
           AND T1.ORG_ID = T2.ORG_ID
           AND T1.RECOND_STATE = '1'
        CONNECT BY PRIOR T2.ORG_ID = T2.PARENT_ID
         START WITH T2.ORG_ID = #ORGID#
         GROUP BY T1.EMPLOYEE_ID) T3
 WHERE T.EMPLOYEE_ID = T3.EMPLOYEE_ID
   AND T.PROPERTY_C <> '4';

[BenChiM888]

执行计划贴出来。

[coooliang]

[Quote=引用 1 楼 benchim888 的回复:]
执行计划贴出来。
[/Quote]
请问你说的执行计划是指什么？
这和普通的查询是一样的，只是比较慢而已。如何优化呢。

[Rotel-刘志东]

具体的执行计划分析一下了。

[BenChiM888]

http://topic.csdn.net/u/20110728/10/a35523c9-7769-4b8a-91fe-998db1533cff.html
上面的问题里面的东西就叫执行计划，没有执行计划没办法判断优化方法。

[Quote=引用 2 楼 cl61917380 的回复:]
引用 1 楼 benchim888 的回复:
执行计划贴出来。

请问你说的执行计划是指什么？
这和普通的查询是一样的，只是比较慢而已。如何优化呢。
[/Quote]

[Kobayashi]

这个要看你具体的数据库设计了。
你这样只把SQL搞出来根本看不明白要哪里优化。
你先把索引建好

[Dean_Deng]

建議多用表連接，少用子查詢

[Dean_Deng]

你的distinct在查詢時是很耗時，效率很低，如果可以用表與表之間的連接條件去掉重復的話就不要用distinct了

[coooliang]

[Quote=引用 14 楼 dean_deng 的回复:]
你的distinct在查詢時是很耗時，效率很低，如果可以用表與表之間的連接條件去掉重復的話就不要用distinct了
[/Quote]

哦~~

[coooliang]

[Quote=引用 12 楼 benchim888 的回复:]
SQL code


--试试这个
SELECT T.*
FROM P_EMPLOYEE T,
(SELECT /*+ FIRST_ROWS */
T1.EMPLOYEE_ID
FROM P_EMPLOYEE_STA_RELAT T1, O_ORG T2,
WHERE T2.USED_STATE……
[/Quote]

请问能教一下这个执行计划怎么看吗，以后别人看到也可以学习一下！

[BenChiM888]

看执行计划的执行顺序的原则就是先从最开头一直往右看，直到看到最右边的并列的地方。
对于不并列的，靠右的先执行;如果见到并列的，就从上往下看，对于并列的，靠上的先执行。

因此你的执行计划的执行顺序是：

9>8>12>11>14>13>10>15>7>6>16>5>4>3>2>18>17>1>0

简单说一下：2和17并列(就是左对齐的)，因此2先执行。
6和16并列，因此6先执行。
8和10和15并列，因此8>10>15

而执行8的时候，9是靠右的因此9>8,以此类推。

[coooliang]

[Quote=引用 17 楼 benchim888 的回复:]
看执行计划的执行顺序的原则就是先从最开头一直往右看，直到看到最右边的并列的地方。
对于不并列的，靠右的先执行;如果见到并列的，就从上往下看，对于并列的，靠上的先执行。

因此你的执行计划的执行顺序是：

9>8>12>11>14>13>10>15>7>6>16>5>4>3>2>18>17>1>0

简单说一下：2和17并列(就是左对齐的)，因此2先执行。
6和16并列，因此6先……
[/Quote]

谢谢你热心的回答。我会看执行的顺序了。
然后呢，根据这个顺序你是如何优化我的SQL的，说说你的思路吧！

[BenChiM888]

从你的执行计划上看（我不敢保证我重写的sql效率，因为没有看到你的后续反馈）
1、扫描O_ORG的次数过多。
2、NESTED LOOPS的次数也过多
3、同时看到下面的过滤条件中存在 to_number(O_ORG)=1 的隐士类型转换，这会导致不走索引
4、同时看到 P_EMPLOYEE_STA_RELAT 上是有索引的

因此我的想法是将你的内存嵌套查询改造成直接关联的查询，同时将1改为'1'防止隐式类型转换
然后对内层查询使用 first_rows 提示，让其将查询结果反馈给最外面的 P_EMPLOYEE，使EMPLOYEE_ID上的索引较快的得到结果反馈出来。

思路大致如上，可能有不恰当的地方，欢迎大家指正。

[BenChiM888]

表结构索引和测试数据粘出来看看。

[coooliang]

恩，思路我懂了。非常感谢


不过有个问题就是我用了你写的查不出数据

--新的，T3这个表查出内容为空。
SELECT T.*
  FROM P_EMPLOYEE T,
       (SELECT /*+ FIRST_ROWS */
               T1.EMPLOYEE_ID
          FROM p_employee_sta_relat  T1, o_org T2
         WHERE T2.USED_STATE = 1
           AND T1.ORG_ID = T2.ORG_ID
           AND T1.RECOND_STATE = '1'
        CONNECT BY PRIOR T2.ORG_ID = T2.PARENT_ID
         START WITH T2.ORG_ID = '1'
         GROUP BY T1.EMPLOYEE_ID) T3
 WHERE T.EMPLOYEE_ID = T3.EMPLOYEE_ID
   AND T.PROPERTY_C <> '4';


--原来的
   select t.*
                  from p_employee t,
                       (select distinct (t1.employee_id)
                          from p_employee_sta_relat t1,
                               (select org_id
                                  from (select org_id, parent_id
                                          from o_org
                                         where USED_STATE = 1)
                                connect by prior org_id = parent_id
                                 start with org_id = '1') t2
                         where t1.org_id = t2.org_id
                         and t1.recond_state='1') t3
                 where t.employee_id = t3.employee_id
                  and t.property_c <> '4'

[狂想者]

学习了，顶一下！！！！

[coooliang]

[Quote=引用 21 楼 benchim888 的回复:]
表结构索引和测试数据粘出来看看。
[/Quote]

字段有点多。

create table P_EMPLOYEE_STA_RELAT
(
  ORG_ID           VARCHAR2(20) not null,
  EMPLOYEE_ID      VARCHAR2(20) not null,
  JOB_TYPE         NUMBER default 1 not null,
  MATCH_GRADE      NUMBER default -1,
  RESULT_AVAIL     NUMBER default 0,
  MATCH_D          DATE,
  PARTY_CHIEF_FLAG VARCHAR2(8),
  MANAGER_TYPE_C   VARCHAR2(8),
  CONFIGURE_TYPE_C VARCHAR2(8),
  RECEDE_FLAG      NUMBER,
  MANAGER_WORK     VARCHAR2(512),
  CHANGE_D         DATE,
  ASSIGN_WORK      VARCHAR2(255),
  AUTHORIZE_CORP   VARCHAR2(20),
  AUTHORIZE_FILE   VARCHAR2(100),
  AUTHORIZE_D      DATE,
  REMARK           VARCHAR2(300),
  CHANGE_REASON    VARCHAR2(255),
  JOB_LEVEL_C      VARCHAR2(8),
  DATA_MODIFY      DATE,
  NAME             VARCHAR2(100),
  CORP_ID          VARCHAR2(20),
  DEPT_ID          VARCHAR2(20),
  GROUP_ID         VARCHAR2(20),
  ERP_CORP_ID      VARCHAR2(42),
  ERP_DEPT_ID      VARCHAR2(42),
  ERP_STATION_ID   VARCHAR2(42),
  SYNCH_DATE       DATE,
  RECOND_STATE     VARCHAR2(1) default 1,
  ERP_RECOND_ID    VARCHAR2(100),
  DEPT_ID2         VARCHAR2(20)
)


create table P_EMPLOYEE
(
  EMPLOYEE_ID        VARCHAR2(20) not null,
  EMPLOYEE_NO        VARCHAR2(20),
  SERIATE_NUMBER     NUMBER default 999,
  IDCARD_NUMBER      VARCHAR2(20),
  NAME               VARCHAR2(20),
  ADMIN_CORP         VARCHAR2(20),
  ARCHIVES_CORP      VARCHAR2(20),
  SALARY_CORP        VARCHAR2(20),
  LABOR_STAT_CORP    VARCHAR2(20),
  IDENTITY_C         VARCHAR2(18),
  WORKING_FORM_C     VARCHAR2(18),
  PROPERTY_C         VARCHAR2(18),
  SERVICE_STATE_C    VARCHAR2(18),
  SOCIAL_SECURITY_NO VARCHAR2(18),
  PASSPORT_NO        VARCHAR2(30),
  NAME_SPELL         VARCHAR2(100),
  USED_NAME          VARCHAR2(30),
  SEX_C              VARCHAR2(18),
  NATIONALITY_C      VARCHAR2(18),
  BIRTHDAY           DATE,
  NATIVEPLACE        VARCHAR2(100),
  REG_PERM_PROP_C    VARCHAR2(18),
  HOMEPLACE          VARCHAR2(100),
  HABITATION         VARCHAR2(100),
  REG_PERM_LOCUS     VARCHAR2(100),
  PARTY_C            VARCHAR2(18),
  JOIN_CLAN_D        DATE,
  BEGIN_WORK_D       DATE,
  ENTER_EP_D         DATE,
  ENTER_CUR_CORP_D   DATE,
  RETIRED_FROM_ARMY  VARCHAR2(18),
  ADD_REASON_C       VARCHAR2(18),
  O_CORP_NAME        VARCHAR2(100),
  SUB_REASON_C       VARCHAR2(18),
  TO_CORP_NAME       VARCHAR2(100),
  SUB_DATE           DATE,
  ADD_PASS_FILE      VARCHAR2(100),
  SUB_PASS_FILE      VARCHAR2(100),
  MOBILE             VARCHAR2(30),
  OFFICE_TEL         VARCHAR2(30),
  EMAIL              VARCHAR2(100),
  FAX                VARCHAR2(30),
  HOME_TEL           VARCHAR2(30),
  HOME_ADDR          VARCHAR2(100),
  HOME_POSTALCODE    VARCHAR2(20),
  TAG                NUMBER,
  PHOTO              VARCHAR2(100),
  CON_LEN_SERVICE    NUMBER default 0,
  DISCON_LEN_SERVICE NUMBER default 0,
  REMARK             VARCHAR2(300),
  TYPES_C            VARCHAR2(18),
  EDUCATION_TIME     NUMBER default 0,
  DATA_MODIFY        DATE,
  DATA_CHANGE        DATE,
  STATE_C            VARCHAR2(18),
  MARRIED_C          VARCHAR2(18),
  DIRECT_C           VARCHAR2(18),
  SUPPORT_C          VARCHAR2(18),
  HEALTH_STATUS      VARCHAR2(10),
  PPID               VARCHAR2(100),
  SELF_PASSWORD      VARCHAR2(40) default '888888',
  CHECK_FLAG         VARCHAR2(10) default '0',
  NEW_EMPLOYEE_FLAG  VARCHAR2(1),
  PHOTOS             BLOB,
  ERP_EMPLOYEE_ID    VARCHAR2(50),
  DIRECT_MANAGE_C    VARCHAR2(20),
  RECOND_STATE       VARCHAR2(1) default 1,
  ERP_RECOND_ID      VARCHAR2(50),
  SYNCH_DATE         DATE,
  DATE_INSERT        DATE default sysdate,
  O_CORP_ID          VARCHAR2(10),
  ERP_PERSG1         VARCHAR2(20),
  ERP_PERSK          VARCHAR2(20)
)


create table O_ORG
(
  ORG_ID         VARCHAR2(20) not null,
  PARENT_ID      VARCHAR2(120),
  ORG_ID_EXT     VARCHAR2(20),
  NAME           VARCHAR2(100) not null,
  BRIEF_NAME     VARCHAR2(40),
  ORG_TYPE_C     VARCHAR2(8),
  ORG_LEVEL_C    VARCHAR2(8),
  ORG_ATTRIB_C   VARCHAR2(8),
  ORG_SEQ_C      VARCHAR2(8),
  USED_STATE     NUMBER default 1,
  SETUP_D        DATE,
  REPEAL_D       DATE,
  SETUP_FILE_NO  VARCHAR2(100),
  REPEAL_FILE_NO VARCHAR2(100),
  SETUP_FILE     VARCHAR2(200),
  REPEAL_FILE    VARCHAR2(200),
  DUMMY_ORG      NUMBER default 0,
  PLAN_NUM_P     NUMBER default 0,
  PLAN_NUM_ORG   NUMBER default 0,
  REMARK         VARCHAR2(300),
  SORT           NUMBER default 888888,
  DATA_MODIFY    DATE,
  LAOZI_ID       VARCHAR2(20),
  PPID           VARCHAR2(100),
  IS_LEGAL       VARCHAR2(1),
  ERP_ID         VARCHAR2(50),
  SYNCH_DATE     DATE,
  RECOND_STATE   VARCHAR2(1),
  FLAG           VARCHAR2(20)
)

[BenChiM888]

--你试试这个能出数据么。看看效率如何。
SELECT T.*
  FROM P_EMPLOYEE T,
       (SELECT /*+ FIRST_ROWS */
         T1.EMPLOYEE_ID
          FROM P_EMPLOYEE_STA_RELAT T1
         RIGHT O_ORG T2 ON T1.ORG_ID = T2.ORG_ID
                       AND T2.USED_STATE = 1
         WHERE T2.USED_STATE = 1
        CONNECT BY PRIOR T2.ORG_ID = T2.PARENT_ID
         START WITH T2.ORG_ID = '1'
         GROUP BY T1.EMPLOYEE_ID) T3
 WHERE T.EMPLOYEE_ID = T3.EMPLOYEE_ID
   AND (T.PROPERTY_C > '4' OR T.PROPERTY_C < '4');

[coooliang]

[Quote=引用 24 楼 benchim888 的回复:]
SQL code


--你试试这个能出数据么。看看效率如何。
SELECT T.*
FROM P_EMPLOYEE T,
(SELECT /*+ FIRST_ROWS */
T1.EMPLOYEE_ID
FROM P_EMPLOYEE_STA_RELAT T1
RIGHT O_ORG T2 ON T1.O……
[/Quote]
是我没有办法提供测试数据，因为数据太多了。
你的能出数据，我用本地的数据库，原来的比这个会快。

[BenChiM888]

--试试下面这几个语句

SELECT T.*
  FROM P_EMPLOYEE T,
       (SELECT /*+ FIRST_ROWS */
               T1.EMPLOYEE_ID
          FROM P_EMPLOYEE_STA_RELAT T1,
               (SELECT ORG_ID
                  FROM O_ORG
                 WHERE USED_STATE = 1
                CONNECT BY PRIOR ORG_ID = PARENT_ID
                 START WITH ORG_ID = '1') T2
         WHERE T1.ORG_ID = T2.ORG_ID
           AND T1.RECOND_STATE = '1'
          GROUP BY T1.EMPLOYEE_ID) T3
 WHERE T.EMPLOYEE_ID = T3.EMPLOYEE_ID
   AND (T.PROPERTY_C > '4' OR T.PROPERTY_C < '4');

--OR
CREATE TABLE O_ORG_TMP AS
  SELECT ORG_ID
    FROM O_ORG
   WHERE USED_STATE = 1
  CONNECT BY PRIOR ORG_ID = PARENT_ID
   START WITH ORG_ID = '1';

CREATE INDEX O_ORG_TMP_IDX ON O_ORG_TMP(ORG_ID);

SELECT T.*
  FROM P_EMPLOYEE T,
       (SELECT /*+ FIRST_ROWS */
               T1.EMPLOYEE_ID
          FROM P_EMPLOYEE_STA_RELAT T1,
               O_ORG_TMP T2
         WHERE T1.ORG_ID = T2.ORG_ID
          GROUP BY T1.EMPLOYEE_ID) T3
 WHERE T.EMPLOYEE_ID = T3.EMPLOYEE_ID
   AND (T.PROPERTY_C > '4' OR T.PROPERTY_C < '4');

--OR
CREATE TABLE O_ORG_TMP AS
  SELECT ORG_ID
    FROM O_ORG
   WHERE USED_STATE = 1
  CONNECT BY PRIOR ORG_ID = PARENT_ID
   START WITH ORG_ID = '1';

CREATE INDEX O_ORG_TMP_IDX ON O_ORG_TMP(ORG_ID);

SELECT T.*
  FROM P_EMPLOYEE T
 WHERE (T.PROPERTY_C > '4' OR T.PROPERTY_C < '4')
   AND EXISTS (SELECT 1
          FROM P_EMPLOYEE_STA_RELAT T1, O_ORG_TMP T2
         WHERE T1.ORG_ID = T2.ORG_ID
           AND T1.EMPLOYEE_ID = T.EMPLOYEE_ID);

--OR
CREATE TABLE O_ORG_TMP AS
  SELECT ORG_ID
    FROM O_ORG
   WHERE USED_STATE = 1
  CONNECT BY PRIOR ORG_ID = PARENT_ID
   START WITH ORG_ID = '1';

CREATE INDEX O_ORG_TMP_IDX ON O_ORG_TMP(ORG_ID);

SELECT DISTINCT T.*
  FROM P_EMPLOYEE T, P_EMPLOYEE_STA_RELAT T1, O_ORG_TMP T2
 WHERE T.EMPLOYEE_ID = T2.EMPLOYEE_ID
   AND T1.ORG_ID = T2.ORG_ID
   AND (T.PROPERTY_C > '4' OR T.PROPERTY_C < '4');