求SQL优化

[haowang21]

现在做的是一个数据授权的功能，权限可以分配到用户和小组, 小组下又包含用户和岗位, 岗位下包含用户

表的大体结构如下

-- 项目表（权限管理的数据）
create table PROJECT
(
  project_id             NUMBER(18) not null,
  project_no             VARCHAR2(100),
  project_name           VARCHAR2(1000),
  project_style          NUMBER
);

--授权表
create table PROJECT_AUTH
(
  project_auth_id    NUMBER(18),
  project_id         NUMBER(18)
);

--授权关系表
create table PROJECT_AUTH_RELA
(
  r_project_auth_id  NUMBER(18) not null,
  project_auth_id    NUMBER(18) not null, --授权ID
  object_id         NUMBER(18) not null, --对应的用户ID，小组ID或者角色ID
  object_type       NUMBER(2) not null, -- objetType包含用户(1)，小组(2)
  is_edit           NUMBER(2)
);

--用户表
create table T_USER
(
  user_id           NUMBER(18) not null,
  login_name        VARCHAR2(256),
  user_name         VARCHAR2(256)
);

--用户小组关系表
create table user_group_rela
(
  r_user_group_id  NUMBER(18) not null,
  group_id          NUMBER(18) not null, 
  object_id         NUMBER(18) not null, --对应的用户ID，岗位ID
  object_type       NUMBER(2) not null -- objetType包含用户(1)，岗位(3)
);

--岗位用户关系表
create table job_user_rela
(
  r_job_user_id  NUMBER(18) not null,
  job_id          NUMBER(18) not null, 
  user_id         NUMBER(18) not null --用户ID
);

其中项目表大概3W条数据， 所有项目授权后授权关系表中大概6W条数据 查询是异常的慢 分页查询10条数据需要6秒多。

查询逻辑如下

传入用户ID 查询该用户可以访问的所有项目。 包含小组下的用户，小组下岗位下的用户

SELECT *
  FROM PROJECT pp
 WHERE EXISTS (SELECT 1
          FROM PROJECT_AUTH_RELA T, PROJECT_AUTH T2
         WHERE T.PROJECT_auth_id = T2.PROJECT_AUTH_ID
           AND ((T.OBJECT_TYPE = 1 AND T.OBJECT_ID = 15656) OR
               (T.OBJECT_TYPE = 2 AND EXISTS
                (SELECT 1
                    FROM USER_GROUP_RELA A
                   WHERE A.GROUP_ID = T.OBJECT_ID
                     AND ((A.OBJECT_TYPE = 1 AND A.OBJECT_ID = 15656) OR
                         EXISTS (SELECT 1
                                   FROM JOB_USER_RELA J
                                  WHERE J.JOB_ID = A.OBJECT_ID
                                    AND A.OBJECT_TYPE = 3
                                    AND J.USER_ID = 15656)))))
           AND t2.PROJECT_ID = pp.PROJECT_ID
           AND ROWNUM = 1)

查询很慢，求各位大大SQL优化方案，

[wangchangming]

表结构列出来了，索引结构呢，执行计划呢

[haowang21]

Plan Hash Value  : 3613226037 

------------------------------------------------------------------------------------------------
| Id   | Operation                | Name                   | Rows  | Bytes  | Cost  | Time     |
------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT         |                        |     1 |   1040 | 23561 | 00:04:43 |
|  * 1 |   FILTER                 |                        |       |        |       |          |
|    2 |    TABLE ACCESS FULL     | PM_T_PROJECT_ESTABLISH |   228 | 237120 |    18 | 00:00:01 |
|  * 3 |    COUNT STOPKEY         |                        |       |        |       |          |
|  * 4 |     FILTER               |                        |       |        |       |          |
|  * 5 |      HASH JOIN           |                        |     1 |     25 |   207 | 00:00:03 |
|  * 6 |       TABLE ACCESS FULL  | PM_T_OBJECT_AUTH       |     1 |     13 |    68 | 00:00:01 |
|    7 |       TABLE ACCESS FULL  | PM_R_OBJECT_AUTH       | 59966 | 719592 |   137 | 00:00:02 |
|  * 8 |      FILTER              |                        |       |        |       |          |
|  * 9 |       TABLE ACCESS FULL  | SM_R_OBJECT_GROUP      |    20 |    220 |     2 | 00:00:01 |
| * 10 |       FILTER             |                        |       |        |       |          |
| * 11 |        TABLE ACCESS FULL | SM_R_JOB_USER          |     1 |     26 |     2 | 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter( EXISTS (SELECT 0 FROM "PM_T_OBJECT_AUTH" "T2","PM_R_OBJECT_AUTH" "SYS_ALIAS_1" WHERE ("T"."OBJECT_ID"=15656 AND "T"."OBJECT_TYPE"=0 OR "T"."OBJECT_ID"=15656 AND "T"."OBJECT_TYPE"=1 OR
  "T"."OBJECT_TYPE"=2 AND EXISTS (SELECT 0 FROM "SM_R_OBJECT_GROUP" "SYS_ALIAS_2" WHERE ("A"."OBJECT_ID"=15656 AND "A"."OBJECT_TYPE"=1 OR EXISTS (SELECT 0 FROM "SM_R_JOB_USER" "J" WHERE :B1=3 AND
  "J"."USER_ID"=15656 AND "J"."JOB_ID"=:B2)) AND "A"."GROUP_ID"=:B3)) AND ROWNUM=1 AND "T"."OBJECT_AUTH_ID"="T2"."OBJECT_AUTH_ID" AND "T2"."OBJECT_TYPE"=2 AND "T2"."OBJECT_ID"=TO_NUMBER(:B4)))
* 3 - filter(ROWNUM=1)
* 4 - filter("T"."OBJECT_ID"=15656 AND "T"."OBJECT_TYPE"=0 OR "T"."OBJECT_ID"=15656 AND "T"."OBJECT_TYPE"=1 OR "T"."OBJECT_TYPE"=2 AND EXISTS (SELECT 0 FROM "SM_R_OBJECT_GROUP" "SYS_ALIAS_2" WHERE
  ("A"."OBJECT_ID"=15656 AND "A"."OBJECT_TYPE"=1 OR EXISTS (SELECT 0 FROM "SM_R_JOB_USER" "J" WHERE :B1=3 AND "J"."USER_ID"=15656 AND "J"."JOB_ID"=:B2)) AND "A"."GROUP_ID"=:B3))
* 5 - access("T"."OBJECT_AUTH_ID"="T2"."OBJECT_AUTH_ID")
* 6 - filter("T2"."OBJECT_TYPE"=2 AND "T2"."OBJECT_ID"=TO_NUMBER(:B1))
* 8 - filter("A"."OBJECT_ID"=15656 AND "A"."OBJECT_TYPE"=1 OR EXISTS (SELECT 0 FROM "SM_R_JOB_USER" "J" WHERE :B1=3 AND "J"."USER_ID"=15656 AND "J"."JOB_ID"=:B2))
* 9 - filter("A"."GROUP_ID"=:B1)
* 10 - filter(:B1=3)
* 11 - filter("J"."USER_ID"=15656 AND "J"."JOB_ID"=:B1)

[haowang21]

列出的表结构和真实的略有不同， 但是逻辑是一样的。 上面列出的表结构只管理项目的， 真实业务包含各种不同类型的数据类型。

[wangchangming]

这不是 SELECT * FROM PROJECT pp WHERE EXISTS (SELECT 1 FROM PROJECT_AUTH_RELA T, PROJECT_AUTH T2 WHERE T.PROJECT_auth_id = T2.PROJECT_AUTH_ID AND ((T.OBJECT_TYPE = 1 AND T.OBJECT_ID = 15656) OR (T.OBJECT_TYPE = 2 AND EXISTS (SELECT 1 FROM USER_GROUP_RELA A WHERE A.GROUP_ID = T.OBJECT_ID AND ((A.OBJECT_TYPE = 1 AND A.OBJECT_ID = 15656) OR EXISTS (SELECT 1 FROM JOB_USER_RELA J WHERE J.JOB_ID = A.OBJECT_ID AND A.OBJECT_TYPE = 3 AND J.USER_ID = 15656))))) AND t2.PROJECT_ID = pp.PROJECT_ID AND ROWNUM = 1) 这条语句的执行计划吧，如果方便就留个QQ吧，好沟通

[haowang21]

非常感谢。。 QQ 25332221

引用 3 楼 wanghao__ 的回复:

列出的表结构和真实的略有不同， 但是逻辑是一样的。 上面列出的表结构只管理项目的， 真实业务包含各种不同类型的数据类型。