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语句。
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语句。
解决方案 »
- 两个数据库的相同表数据查询，求大神解决
- 关于float型数据如何处理
- 请问：hibernate里的session如何正确关闭
- 这样的查询如何实现？
- 数据泵导出Unable to open the log file
- self join的效率的问题
- sql语句优化和索引问题，高手，急！
- 请教一个安装Oracle的问题
- 请教各位高手一个关于oracle10g silent安装的问题啊，主管催的急死了，给分100
- ORACLE在P4系统windows2000 Professional 的安装问题（高分求解）
- 强力推荐超方便高效的 Oracle 开发／操作的工具 - Oracle SQL Handler
- 请问：32位虚拟机上装着一个开发出来的网站的相关文件，我把32位虚拟机上的Oracle9i中的数据导入到了64位虚拟机Oracle11g
上面的问题里面的东西就叫执行计划，没有执行计划没办法判断优化方法。
你这样只把SQL搞出来根本看不明白要哪里优化。
你先把索引建好
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 2430 | 28 (4)| 00:00:01 |
| 1 | NESTED LOOPS | | 9 | 2430 | 28 (4)| 00:00:01 |
| 2 | VIEW | | 9 | 108 | 19 (6)| 00:00:01 |
| 3 | HASH UNIQUE | | 9 | 225 | 19 (6)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | P_EMPLOYEE_STA_RELAT | 1 | 17 | 2 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 9 | 225 | 18 (0)| 00:00:01 |
| 6 | VIEW | | 8 | 64 | 4 (0)| 00:00:01 |
|* 7 | CONNECT BY WITH FILTERING | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | TABLE ACCESS FULL | O_ORG | 8 | 152 | 4 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | BUFFER SORT | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| O_ORG | 8 | 152 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_ORG_PARENT_STATE | 8 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | O_ORG | 8 | 152 | 4 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PK_P_EMPLOYEE_STA_RELAT | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | P_EMPLOYEE | 1 | 258 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_P_EMPLOYEE | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."RECOND_STATE"='1')
7 - filter(TO_NUMBER("ORG_ID")=1)
8 - filter(TO_NUMBER("ORG_ID")=1)
9 - access("PARENT_ID"=NULL)
filter("USED_STATE"=1)
14 - access("PARENT_ID"=NULL AND "USED_STATE"=1)
15 - access("PARENT_ID"=NULL)
filter("USED_STATE"=1)
16 - access("T1"."ORG_ID"="T2"."ORG_ID")
17 - filter("T"."PROPERTY_C"<>'4')
18 - access("T"."EMPLOYEE_ID"="T3"."EMPLOYEE_ID")
Note
-----
- 'PLAN_TABLE' is old version
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
这个对几万条的数据进行树排序最耗时，其它都不用一秒。
所以我想有没有办法把过滤条件放在前面，不要让它对整个表进行树排序呢。
Plan hash value: 1055775884
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 2430 | 28 (4)| 00:00:01 |
| 1 | NESTED LOOPS | | 9 | 2430 | 28 (4)| 00:00:01 |
| 2 | VIEW | | 9 | 108 | 19 (6)| 00:00:01 |
| 3 | HASH UNIQUE | | 9 | 225 | 19 (6)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | P_EMPLOYEE_STA_RELAT | 1 | 17 | 2 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 9 | 225 | 18 (0)| 00:00:01 |
| 6 | VIEW | | 8 | 64 | 4 (0)| 00:00:01 |
|* 7 | CONNECT BY WITH FILTERING | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | TABLE ACCESS FULL | O_ORG | 8 | 152 | 4 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | BUFFER SORT | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| O_ORG | 8 | 152 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_ORG_PARENT_STATE | 8 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | O_ORG | 8 | 152 | 4 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PK_P_EMPLOYEE_STA_RELAT | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | P_EMPLOYEE | 1 | 258 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_P_EMPLOYEE | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."RECOND_STATE"='1')
7 - filter(TO_NUMBER("ORG_ID")=1)
8 - filter(TO_NUMBER("ORG_ID")=1)
9 - access("PARENT_ID"=NULL)
filter("USED_STATE"=1)
14 - access("PARENT_ID"=NULL AND "USED_STATE"=1)
15 - access("PARENT_ID"=NULL)
filter("USED_STATE"=1)
16 - access("T1"."ORG_ID"="T2"."ORG_ID")
17 - filter("T"."PROPERTY_C"<>'4')
18 - access("T"."EMPLOYEE_ID"="T3"."EMPLOYEE_ID")
Note
-----
- 'PLAN_TABLE' is old version
--试试这个
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';
对于不并列的，靠右的先执行;如果见到并列的，就从上往下看，对于并列的，靠上的先执行。因此你的执行计划的执行顺序是：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,以此类推。
谢谢你热心的回答。我会看执行的顺序了。
然后呢，根据这个顺序你是如何优化我的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上的索引较快的得到结果反馈出来。思路大致如上，可能有不恰当的地方，欢迎大家指正。
不过有个问题就是我用了你写的查不出数据--新的，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'
字段有点多。
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)
)
--你试试这个能出数据么。看看效率如何。
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');
--试试下面这几个语句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');