Народ подскажите пжлста,
1) выполняю один запрос с подзапросом из pipelined ф-ии - отрабатывает ~ 13 сек
2)такой же запрос но с выставленными вручную значениями ~ 1.1 сек
запросы возвращают 20 строк
не могу заставить oracle при первом варианте запроса отрабатывать быстрее 8 сек.
бд: Oracle9i Enterprise Edition Release 9.2.0.7.0
статистика собрана
табл: записей
DM_SYSOBJECT_S 40899
LWBD_DOCUMENT_S 6859
LWEN_ACSS_GRP_OBJECT_CLS_PERM 2405
остальные табл. около 1000
запрос 1
with bd_doc as ( select column_value as r_object_id
from table ( pkgdd_utility.Fn_Get_get_separate_data ( '09be057280000e36,09be057280000e46', ',') )
)
SELECT /*+ CARDINALITY(bd_doc 2)*/
e.entitlement_id_i as fk_entitlement_id_i,
e.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
d.r_object_id
FROM bd_doc
join LWBD_DOCUMENT_S d on (d.r_object_id = bd_doc.r_object_id) and (d.is_manual_corr_acl_b = 0)
join DM_SYSOBJECT_S ds on (ds.r_object_id = d.r_object_id) and (ds.i_has_folder = 1)
join DM_ACL_S acls on (ds.acl_name = acls.object_name) and (ds.acl_domain = acls.owner_name)
join LWEN_OBJECT_CLASS oc on oc.fk_dm_acl_id_c = acls.r_object_id
join LWEN_ACSS_GRP_OBJECT_CLS_PERM ocp on oc.OBJECT_CLASS_ID_I = ocp.FK_OBJECT_CLASS_ID_I
join LWEN_ENTITLEMENT e on e.ENTITLEMENT_ID_I = ocp.FK_ENTITLEMENT_ID_I
join LWEN_ACCESS_GROUP ag on ag.ACCESS_GROUP_ID_I = ocp.FK_ACCESS_GROUP_ID_I
join DM_GROUP_S gs on ag.FK_DM_GROUP_ID_C = gs.R_OBJECT_ID
join DM_GROUP_R gr on gr.R_OBJECT_ID = gs.R_OBJECT_ID
where case
when ( lower(gs.description) like 'owner' ) and ( ds.r_lock_owner = ' ' ) then ds.owner_name
when ( lower(gs.description) like 'lock owner') and ( ds.r_lock_owner <> ' ' ) then ds.r_lock_owner
when ( lower(gs.description) like '%dm_world' ) then gr.users_names
else gr.users_names
end = 'test1'
UNION
(
SELECT /*+ CARDINALITY(bd_doc 2) */
e.entitlement_id_i as fk_entitlement_id_i,
e.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
d.r_object_id
FROM bd_doc
join LWBD_DOCUMENT_S d on (d.r_object_id = bd_doc.r_object_id) and (d.is_manual_corr_acl_b = 1)
join DM_SYSOBJECT_S ds on (ds.r_object_id = d.r_object_id) and (ds.i_has_folder = 1)
join DM_ACL_S acls on (ds.acl_name = acls.object_name) and (ds.acl_domain = acls.owner_name)
join LWEN_OBJECT_CLASS oc on oc.fk_dm_acl_id_c = acls.r_object_id
join LWEN_ACSS_GRP_OBJECT_CLS_PERM ocp on oc.OBJECT_CLASS_ID_I = ocp.FK_OBJECT_CLASS_ID_I
join LWEN_ENTITLEMENT e on e.ENTITLEMENT_ID_I = ocp.FK_ENTITLEMENT_ID_I
join LWEN_ACCESS_GROUP ag on ag.ACCESS_GROUP_ID_I = ocp.FK_ACCESS_GROUP_ID_I
join DM_GROUP_S gs on ag.FK_DM_GROUP_ID_C = gs.R_OBJECT_ID
join DM_GROUP_R gr on gr.R_OBJECT_ID = gs.R_OBJECT_ID
where case
when ( lower(gs.description) like 'owner' ) and ( ds.r_lock_owner = ' ' ) then ds.owner_name
when ( lower(gs.description) like 'lock owner') and ( ds.r_lock_owner <> ' ' ) then ds.r_lock_owner
when ( lower(gs.description) like '%dm_world' ) then gr.users_names
else gr.users_names
end = 'test1'
UNION
select /*+ CARDINALITY(bd_doc 2)*/
-- add to LOCk_OWNER user entitlement 'check_in_doc', 'unlock_doc'
ent.entitlement_id_i as fk_entitlement_id_i,
ent.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
d.r_object_id
from bd_doc
join LWBD_DOCUMENT_S d on (d.r_object_id = bd_doc.r_object_id) and (d.is_manual_corr_acl_b = 1)
join DM_SYSOBJECT_S ds on (ds.r_object_id = d.r_object_id) and (ds.i_has_folder = 1) and (ds.r_lock_owner = 'test1')
cross join lwen_entitlement ent
where ent.entitlement_code_c in ('check_in_doc', 'unlock_doc')
MINUS
select /*+ CARDINALITY(bd_doc 2)*/
-- delete from LOCK_OWNER user entitlement 'edit_metadata', 'delete_doc', 'chg_perm'
ent.entitlement_id_i as fk_entitlement_id_i,
ent.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
d.r_object_id
from bd_doc
join LWBD_DOCUMENT_S d on (d.r_object_id = bd_doc.r_object_id) and (d.is_manual_corr_acl_b = 1)
join DM_SYSOBJECT_S ds on (ds.r_object_id = d.r_object_id) and (ds.i_has_folder = 1) and (ds.r_lock_owner = 'test1')
cross join lwen_entitlement ent
where ent.entitlement_code_c in ('edit_metadata', 'delete_doc', 'chg_perm')
);
план
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7538 | 35M| | 7446 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 1 | RECURSIVE EXECUTION | SYS_LE_2_0 | | | | |
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | | | | | |
| 2 | COLLECTION ITERATOR PICKLER FETCH| FN_GET_GET_SEPARATE_DATA | | | | |
| 3 | SORT UNIQUE | | 7538 | 35M| 90M| 7446 |
| 4 | UNION-ALL | | | | | |
|* 5 | HASH JOIN | | 296 | 646K| | 561 |
|* 6 | HASH JOIN | | 124 | 29264 | | 341 |
|* 7 | TABLE ACCESS FULL | LWBD_DOCUMENT_S | 3426 | 68520 | | 13 |
|* 8 | HASH JOIN | | 740 | 156K| | 326 |
| 9 | TABLE ACCESS FULL | DM_GROUP_R | 239 | 6214 | | 2 |
|* 10 | HASH JOIN | | 38370 | 7119K| | 322 |
| 11 | TABLE ACCESS FULL | DM_GROUP_S | 120 | 3120 | | 2 |
|* 12 | HASH JOIN | | 38370 | 6145K| | 319 |
| 13 | TABLE ACCESS FULL | LWEN_ACCESS_GROUP | 115 | 2185 | | 2 |
|* 14 | HASH JOIN | | 38370 | 5433K| | 316 |
| 15 | TABLE ACCESS FULL | LWEN_ENTITLEMENT | 66 | 1056 | | 2 |
|* 16 | HASH JOIN | | 38370 | 4833K| | 313 |
| 17 | TABLE ACCESS FULL | LWEN_ACSS_GRP_OBJECT_CLS_PERM | 2350 | 21150 | | 4 |
|* 18 | HASH JOIN | | 1453 | 170K| | 308 |
|* 19 | HASH JOIN | | 88 | 5632 | | 7 |
| 20 | TABLE ACCESS FULL | LWEN_OBJECT_CLASS | 89 | 1691 | | 2 |
| 21 | TABLE ACCESS FULL | DM_ACL_S | 1235 | 55575 | | 4 |
|* 22 | TABLE ACCESS FULL | DM_SYSOBJECT_S | 20394 | 1115K| | 300 |
| 23 | VIEW | | 8168 | 15M| | 215 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9EF0C0_45FBDC5E | 8168 | 15M| | 215 |
| 25 | MINUS | | | | | |
| 26 | SORT UNIQUE | | 7538 | 35M| 90M| 7446 |
| 27 | UNION-ALL | | | | | |
|* 28 | HASH JOIN | | 296 | 646K| | 561 |
|* 29 | HASH JOIN | | 124 | 29264 | | 341 |
|* 30 | TABLE ACCESS FULL | LWBD_DOCUMENT_S | 3426 | 68520 | | 13 |
|* 31 | HASH JOIN | | 740 | 156K| | 326 |
| 32 | TABLE ACCESS FULL | DM_GROUP_R | 239 | 6214 | | 2 |
|* 33 | HASH JOIN | | 38370 | 7119K| | 322 |
| 34 | TABLE ACCESS FULL | DM_GROUP_S | 120 | 3120 | | 2 |
|* 35 | HASH JOIN | | 38370 | 6145K| | 319 |
| 36 | TABLE ACCESS FULL | LWEN_ACCESS_GROUP | 115 | 2185 | | 2 |
|* 37 | HASH JOIN | | 38370 | 5433K| | 316 |
| 38 | TABLE ACCESS FULL | LWEN_ENTITLEMENT | 66 | 1056 | | 2 |
|* 39 | HASH JOIN | | 38370 | 4833K| | 313 |
| 40 | TABLE ACCESS FULL | LWEN_ACSS_GRP_OBJECT_CLS_PERM | 2350 | 21150 | | 4 |
|* 41 | HASH JOIN | | 1453 | 170K| | 308 |
|* 42 | HASH JOIN | | 88 | 5632 | | 7 |
| 43 | TABLE ACCESS FULL | LWEN_OBJECT_CLASS | 89 | 1691 | | 2 |
| 44 | TABLE ACCESS FULL | DM_ACL_S | 1235 | 55575 | | 4 |
|* 45 | TABLE ACCESS FULL | DM_SYSOBJECT_S | 20394 | 1115K| | 300 |
| 46 | VIEW | | 8168 | 15M| | 215 |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9EF0C0_45FBDC5E | 8168 | 15M| | 215 |
|* 48 | HASH JOIN | | 6946 | 13M| | 505 |
|* 49 | HASH JOIN | | 2913 | 159K| | 263 |
|* 50 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 1457 | 29140 | | 234 |
|* 51 | INDEX RANGE SCAN | D_1FBE05728000002A | 1 | | | 10 |
| 52 | MERGE JOIN CARTESIAN | | 6852 | 240K| | 28 |
| 53 | INLIST ITERATOR | | | | | |
| 54 | TABLE ACCESS BY INDEX ROWID| LWEN_ENTITLEMENT | 2 | 32 | | 2 |
|* 55 | INDEX RANGE SCAN | ENTITLEMENT_CODE_U_I | 2 | | | 1 |
| 56 | BUFFER SORT | | 3426 | 68520 | | 26 |
|* 57 | TABLE ACCESS FULL | LWBD_DOCUMENT_S | 3426 | 68520 | | 13 |
| 58 | VIEW | | 8168 | 15M| | 215 |
| 59 | TABLE ACCESS FULL | SYS_TEMP_0FD9EF0C0_45FBDC5E | 8168 | 15M| | 215 |
| 60 | SORT UNIQUE | | 10419 | 20M| 54M| 3578 |
|* 61 | HASH JOIN | | 10419 | 20M| | 530 |
|* 62 | HASH JOIN | | 4370 | 238K| | 276 |
|* 63 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 1457 | 29140 | | 234 |
|* 64 | INDEX RANGE SCAN | D_1FBE05728000002A | 1 | | | 10 |
| 65 | MERGE JOIN CARTESIAN | | 10278 | 361K| | 41 |
| 66 | INLIST ITERATOR | | | | | |
| 67 | TABLE ACCESS BY INDEX ROWID | LWEN_ENTITLEMENT | 3 | 48 | | 2 |
|* 68 | INDEX RANGE SCAN | ENTITLEMENT_CODE_U_I | 3 | | | 1 |
| 69 | BUFFER SORT | | 3426 | 68520 | | 39 |
|* 70 | TABLE ACCESS FULL | LWBD_DOCUMENT_S | 3426 | 68520 | | 13 |
| 71 | VIEW | | 8168 | 15M| | 215 |
| 72 | TABLE ACCESS FULL | SYS_TEMP_0FD9EF0C0_45FBDC5E | 8168 | 15M| | 215 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."R_OBJECT_ID"="BD_DOC"."R_OBJECT_ID")
6 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
7 - filter("D"."IS_MANUAL_CORR_ACL_B"=0)
8 - access("GR"."R_OBJECT_ID"="GS"."R_OBJECT_ID")
filter(CASE WHEN (LOWER("GS"."DESCRIPTION") LIKE 'owner' AND "DS"."R_LOCK_OWNER"=' ') THEN
"DS"."OWNER_NAME" WHEN (LOWER("GS"."DESCRIPTION") LIKE 'lock owner' AND "DS"."R_LOCK_OWNER"<>' ') THEN
"DS"."R_LOCK_OWNER" WHEN LOWER("GS"."DESCRIPTION") LIKE '%dm_world' THEN "GR"."USERS_NAMES" ELSE
"GR"."USERS_NAMES" END ='test1')
10 - access("AG"."FK_DM_GROUP_ID_C"="GS"."R_OBJECT_ID")
12 - access("AG"."ACCESS_GROUP_ID_I"="OCP"."FK_ACCESS_GROUP_ID_I")
14 - access("E"."ENTITLEMENT_ID_I"="OCP"."FK_ENTITLEMENT_ID_I")
16 - access("OC"."OBJECT_CLASS_ID_I"="OCP"."FK_OBJECT_CLASS_ID_I")
18 - access("DS"."ACL_NAME"="ACLS"."OBJECT_NAME" AND "DS"."ACL_DOMAIN"="ACLS"."OWNER_NAME")
19 - access("OC"."FK_DM_ACL_ID_C"="ACLS"."R_OBJECT_ID")
22 - filter("DS"."I_HAS_FOLDER"=1)
28 - access("D"."R_OBJECT_ID"="BD_DOC"."R_OBJECT_ID")
29 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
30 - filter("D"."IS_MANUAL_CORR_ACL_B"=1)
31 - access("GR"."R_OBJECT_ID"="GS"."R_OBJECT_ID")
filter(CASE WHEN (LOWER("GS"."DESCRIPTION") LIKE 'owner' AND "DS"."R_LOCK_OWNER"=' ') THEN
"DS"."OWNER_NAME" WHEN (LOWER("GS"."DESCRIPTION") LIKE 'lock owner' AND "DS"."R_LOCK_OWNER"<>' ') THEN
"DS"."R_LOCK_OWNER" WHEN LOWER("GS"."DESCRIPTION") LIKE '%dm_world' THEN "GR"."USERS_NAMES" ELSE
"GR"."USERS_NAMES" END ='test1')
33 - access("AG"."FK_DM_GROUP_ID_C"="GS"."R_OBJECT_ID")
35 - access("AG"."ACCESS_GROUP_ID_I"="OCP"."FK_ACCESS_GROUP_ID_I")
37 - access("E"."ENTITLEMENT_ID_I"="OCP"."FK_ENTITLEMENT_ID_I")
39 - access("OC"."OBJECT_CLASS_ID_I"="OCP"."FK_OBJECT_CLASS_ID_I")
41 - access("DS"."ACL_NAME"="ACLS"."OBJECT_NAME" AND "DS"."ACL_DOMAIN"="ACLS"."OWNER_NAME")
42 - access("OC"."FK_DM_ACL_ID_C"="ACLS"."R_OBJECT_ID")
45 - filter("DS"."I_HAS_FOLDER"=1)
48 - access("D"."R_OBJECT_ID"="BD_DOC"."R_OBJECT_ID")
49 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
50 - filter("DS"."I_HAS_FOLDER"=1)
51 - access("DS"."R_LOCK_OWNER"='test1')
55 - access("ENT"."ENTITLEMENT_CODE_C"='check_in_doc' OR "ENT"."ENTITLEMENT_CODE_C"='unlock_doc')
57 - filter("D"."IS_MANUAL_CORR_ACL_B"=1)
61 - access("D"."R_OBJECT_ID"="BD_DOC"."R_OBJECT_ID")
62 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
63 - filter("DS"."I_HAS_FOLDER"=1)
64 - access("DS"."R_LOCK_OWNER"='test1')
68 - access("ENT"."ENTITLEMENT_CODE_C"='chg_perm' OR "ENT"."ENTITLEMENT_CODE_C"='delete_doc' OR
"ENT"."ENTITLEMENT_CODE_C"='edit_metadata')
70 - filter("D"."IS_MANUAL_CORR_ACL_B"=1)
Note: cpu costing is off
запрос 2
SELECT
e.entitlement_id_i as fk_entitlement_id_i,
e.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
ds.r_object_id
FROM DM_SYSOBJECT_S ds
join LWBD_DOCUMENT_S d on (ds.r_object_id = d.r_object_id) and (d.is_manual_corr_acl_b = 0)
join DM_ACL_S acls on (ds.acl_name = acls.object_name) and (ds.acl_domain = acls.owner_name)
join LWEN_OBJECT_CLASS oc on oc.fk_dm_acl_id_c = acls.r_object_id
join LWEN_ACSS_GRP_OBJECT_CLS_PERM ocp on oc.OBJECT_CLASS_ID_I = ocp.FK_OBJECT_CLASS_ID_I
join LWEN_ENTITLEMENT e on e.ENTITLEMENT_ID_I = ocp.FK_ENTITLEMENT_ID_I
join LWEN_ACCESS_GROUP ag on ag.ACCESS_GROUP_ID_I = ocp.FK_ACCESS_GROUP_ID_I
join DM_GROUP_S gs on ag.FK_DM_GROUP_ID_C = gs.R_OBJECT_ID
join DM_GROUP_R gr on gr.R_OBJECT_ID = gs.R_OBJECT_ID
where ds.r_object_id in ('09be057280000e36','09be057280000e46')
and ds.i_has_folder = 1
and case
when ( lower(gs.description) like 'owner' ) and ( ds.r_lock_owner = ' ' ) then ds.owner_name
when ( lower(gs.description) like 'lock owner') and ( ds.r_lock_owner <> ' ' ) then ds.r_lock_owner
when ( lower(gs.description) like '%dm_world' ) then gr.users_names
else gr.users_names
end = 'test1'
UNION
(
SELECT
e.entitlement_id_i as fk_entitlement_id_i,
e.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
ds.r_object_id
FROM DM_SYSOBJECT_S ds
join LWBD_DOCUMENT_S d on (ds.r_object_id = d.r_object_id) and (d.is_manual_corr_acl_b = 1)
join DM_ACL_S acls on (ds.acl_name = acls.object_name) and (ds.acl_domain = acls.owner_name)
join LWEN_OBJECT_CLASS oc on oc.fk_dm_acl_id_c = acls.r_object_id
join LWEN_ACSS_GRP_OBJECT_CLS_PERM ocp on oc.OBJECT_CLASS_ID_I = ocp.FK_OBJECT_CLASS_ID_I
join LWEN_ENTITLEMENT e on e.ENTITLEMENT_ID_I = ocp.FK_ENTITLEMENT_ID_I
join LWEN_ACCESS_GROUP ag on ag.ACCESS_GROUP_ID_I = ocp.FK_ACCESS_GROUP_ID_I
join DM_GROUP_S gs on ag.FK_DM_GROUP_ID_C = gs.R_OBJECT_ID
join DM_GROUP_R gr on gr.R_OBJECT_ID = gs.R_OBJECT_ID
where ds.r_object_id in ('09be057280000e36','09be057280000e46')
and ds.i_has_folder = 1
and case
when ( lower(gs.description) like 'owner' ) and ( ds.r_lock_owner = ' ' ) then ds.owner_name
when ( lower(gs.description) like 'lock owner') and ( ds.r_lock_owner <> ' ' ) then ds.r_lock_owner
when ( lower(gs.description) like '%dm_world' ) then gr.users_names
else gr.users_names
end = 'test1'
UNION
select
-- add to LOCk_OWNER user entitlement 'check_in_doc', 'unlock_doc'
ent.entitlement_id_i as fk_entitlement_id_i,
ent.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
d.r_object_id
from DM_SYSOBJECT_S ds
join LWBD_DOCUMENT_S d on (ds.r_object_id = d.r_object_id) and (d.is_manual_corr_acl_b = 1)
cross join lwen_entitlement ent
where ent.entitlement_code_c in ('check_in_doc', 'unlock_doc')
and ds.r_object_id in ('09be057280000e36','09be057280000e46')
and ds.i_has_folder = 1
and ds.r_lock_owner = 'test1'
MINUS
select
-- delete from LOCK_OWNER user entitlement 'edit_metadata', 'delete_doc', 'chg_perm'
ent.entitlement_id_i as fk_entitlement_id_i,
ent.entitlement_code_c as entitlement_name_c,
d.is_from_wss_b,
d.r_object_id
from DM_SYSOBJECT_S ds
join LWBD_DOCUMENT_S d on (ds.r_object_id = d.r_object_id) and (d.is_manual_corr_acl_b = 1)
cross join lwen_entitlement ent
where ent.entitlement_code_c in ('edit_metadata', 'delete_doc', 'chg_perm')
and ds.r_object_id in ('09be057280000e36','09be057280000e46')
and ds.i_has_folder = 1
and ds.r_lock_owner = 'test1'
);
план
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 584 | 94 |
| 1 | SORT UNIQUE | | 3 | 584 | 94 |
| 2 | UNION-ALL | | | | |
| 3 | NESTED LOOPS | | 1 | 236 | 17 |
| 4 | MERGE JOIN CARTESIAN | | 35 | 7945 | 17 |
|* 5 | HASH JOIN | | 1 | 211 | 15 |
|* 6 | HASH JOIN | | 27 | 4995 | 12 |
| 7 | MERGE JOIN CARTESIAN | | 27 | 4293 | 9 |
| 8 | TABLE ACCESS BY INDEX ROWID | LWEN_OBJECT_CLASS | 1 | 19 | 2 |
| 9 | NESTED LOOPS | | 1 | 140 | 7 |
| 10 | NESTED LOOPS | | 1 | 121 | 5 |
| 11 | NESTED LOOPS | | 1 | 76 | 4 |
| 12 | INLIST ITERATOR | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 1 | 56 | 3 |
|* 14 | INDEX RANGE SCAN | D_1FBE057280000108 | 2 | | 2 |
|* 15 | TABLE ACCESS BY INDEX ROWID | LWBD_DOCUMENT_S | 1 | 20 | 1 |
|* 16 | INDEX UNIQUE SCAN | D_1FBE0572800001CE | 1 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | DM_ACL_S | 1 | 45 | 1 |
|* 18 | INDEX UNIQUE SCAN | D_1FBE057280000103 | 1 | | |
|* 19 | INDEX RANGE SCAN | OBJECT_CLASS_DM_ACL_ID_I | 1 | | 1 |
| 20 | BUFFER SORT | | 115 | 2185 | 7 |
| 21 | TABLE ACCESS FULL | LWEN_ACCESS_GROUP | 115 | 2185 | 2 |
| 22 | TABLE ACCESS FULL | DM_GROUP_S | 120 | 3120 | 2 |
| 23 | TABLE ACCESS FULL | DM_GROUP_R | 239 | 6214 | 2 |
| 24 | BUFFER SORT | | 66 | 1056 | 15 |
| 25 | TABLE ACCESS FULL | LWEN_ENTITLEMENT | 66 | 1056 | 2 |
|* 26 | INDEX UNIQUE SCAN | PK_ACSS_GRP_OBJECT_CLS_PER_IDS | 1 | 9 | |
| 27 | MINUS | | | | |
| 28 | SORT UNIQUE | | 3 | 584 | 94 |
| 29 | UNION-ALL | | | | |
| 30 | NESTED LOOPS | | 1 | 236 | 17 |
| 31 | MERGE JOIN CARTESIAN | | 35 | 7945 | 17 |
|* 32 | HASH JOIN | | 1 | 211 | 15 |
|* 33 | HASH JOIN | | 27 | 4995 | 12 |
| 34 | MERGE JOIN CARTESIAN | | 27 | 4293 | 9 |
| 35 | TABLE ACCESS BY INDEX ROWID | LWEN_OBJECT_CLASS | 1 | 19 | 2 |
| 36 | NESTED LOOPS | | 1 | 140 | 7 |
| 37 | NESTED LOOPS | | 1 | 121 | 5 |
| 38 | NESTED LOOPS | | 1 | 76 | 4 |
| 39 | INLIST ITERATOR | | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S | 1 | 56 | 3 |
|* 41 | INDEX RANGE SCAN | D_1FBE057280000108 | 2 | | 2 |
|* 42 | TABLE ACCESS BY INDEX ROWID | LWBD_DOCUMENT_S | 1 | 20 | 1 |
|* 43 | INDEX UNIQUE SCAN | D_1FBE0572800001CE | 1 | | |
| 44 | TABLE ACCESS BY INDEX ROWID | DM_ACL_S | 1 | 45 | 1 |
|* 45 | INDEX UNIQUE SCAN | D_1FBE057280000103 | 1 | | |
|* 46 | INDEX RANGE SCAN | OBJECT_CLASS_DM_ACL_ID_I | 1 | | 1 |
| 47 | BUFFER SORT | | 115 | 2185 | 7 |
| 48 | TABLE ACCESS FULL | LWEN_ACCESS_GROUP | 115 | 2185 | 2 |
| 49 | TABLE ACCESS FULL | DM_GROUP_S | 120 | 3120 | 2 |
| 50 | TABLE ACCESS FULL | DM_GROUP_R | 239 | 6214 | 2 |
| 51 | BUFFER SORT | | 66 | 1056 | 15 |
| 52 | TABLE ACCESS FULL | LWEN_ENTITLEMENT | 66 | 1056 | 2 |
|* 53 | INDEX UNIQUE SCAN | PK_ACSS_GRP_OBJECT_CLS_PER_IDS | 1 | 9 | |
| 54 | MERGE JOIN CARTESIAN | | 1 | 56 | 6 |
| 55 | NESTED LOOPS | | 1 | 40 | 4 |
| 56 | INLIST ITERATOR | | | | |
|* 57 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 1 | 20 | 3 |
|* 58 | INDEX RANGE SCAN | D_1FBE057280000108 | 2 | | 2 |
|* 59 | TABLE ACCESS BY INDEX ROWID | LWBD_DOCUMENT_S | 1 | 20 | 1 |
|* 60 | INDEX UNIQUE SCAN | D_1FBE0572800001CE | 1 | | |
| 61 | BUFFER SORT | | 2 | 32 | 5 |
| 62 | INLIST ITERATOR | | | | |
| 63 | TABLE ACCESS BY INDEX ROWID | LWEN_ENTITLEMENT | 2 | 32 | 2 |
|* 64 | INDEX RANGE SCAN | ENTITLEMENT_CODE_U_I | 2 | | 1 |
| 65 | SORT UNIQUE | | 1 | 56 | 18 |
| 66 | MERGE JOIN CARTESIAN | | 1 | 56 | 6 |
| 67 | NESTED LOOPS | | 1 | 40 | 4 |
| 68 | INLIST ITERATOR | | | | |
|* 69 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 1 | 20 | 3 |
|* 70 | INDEX RANGE SCAN | D_1FBE057280000108 | 2 | | 2 |
|* 71 | TABLE ACCESS BY INDEX ROWID | LWBD_DOCUMENT_S | 1 | 20 | 1 |
|* 72 | INDEX UNIQUE SCAN | D_1FBE0572800001CE | 1 | | |
| 73 | BUFFER SORT | | 3 | 48 | 5 |
| 74 | INLIST ITERATOR | | | | |
| 75 | TABLE ACCESS BY INDEX ROWID | LWEN_ENTITLEMENT | 3 | 48 | 2 |
|* 76 | INDEX RANGE SCAN | ENTITLEMENT_CODE_U_I | 3 | | 1 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("GR"."R_OBJECT_ID"="GS"."R_OBJECT_ID")
filter(CASE WHEN (LOWER("GS"."DESCRIPTION") LIKE 'owner' AND "DS"."R_LOCK_OWNER"=' ') THEN
"DS"."OWNER_NAME" WHEN (LOWER("GS"."DESCRIPTION") LIKE 'lock owner' AND "DS"."R_LOCK_OWNER"<>' ') THEN
"DS"."R_LOCK_OWNER" WHEN LOWER("GS"."DESCRIPTION") LIKE '%dm_world' THEN "GR"."USERS_NAMES" ELSE
"GR"."USERS_NAMES" END ='test1')
6 - access("AG"."FK_DM_GROUP_ID_C"="GS"."R_OBJECT_ID")
13 - filter("DS"."I_HAS_FOLDER"=1)
14 - access("DS"."R_OBJECT_ID"='09be057280000e36' OR "DS"."R_OBJECT_ID"='09be057280000e46')
15 - filter("D"."IS_MANUAL_CORR_ACL_B"=0)
16 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
filter("D"."R_OBJECT_ID"='09be057280000e36' OR "D"."R_OBJECT_ID"='09be057280000e46')
18 - access("DS"."ACL_DOMAIN"="ACLS"."OWNER_NAME" AND "DS"."ACL_NAME"="ACLS"."OBJECT_NAME")
19 - access("OC"."FK_DM_ACL_ID_C"="ACLS"."R_OBJECT_ID")
26 - access("AG"."ACCESS_GROUP_ID_I"="OCP"."FK_ACCESS_GROUP_ID_I" AND
"OC"."OBJECT_CLASS_ID_I"="OCP"."FK_OBJECT_CLASS_ID_I" AND "E"."ENTITLEMENT_ID_I"="OCP"."FK_ENTITLEMENT_ID_I")
32 - access("GR"."R_OBJECT_ID"="GS"."R_OBJECT_ID")
filter(CASE WHEN (LOWER("GS"."DESCRIPTION") LIKE 'owner' AND "DS"."R_LOCK_OWNER"=' ') THEN
"DS"."OWNER_NAME" WHEN (LOWER("GS"."DESCRIPTION") LIKE 'lock owner' AND "DS"."R_LOCK_OWNER"<>' ') THEN
"DS"."R_LOCK_OWNER" WHEN LOWER("GS"."DESCRIPTION") LIKE '%dm_world' THEN "GR"."USERS_NAMES" ELSE
"GR"."USERS_NAMES" END ='test1')
33 - access("AG"."FK_DM_GROUP_ID_C"="GS"."R_OBJECT_ID")
40 - filter("DS"."I_HAS_FOLDER"=1)
41 - access("DS"."R_OBJECT_ID"='09be057280000e36' OR "DS"."R_OBJECT_ID"='09be057280000e46')
42 - filter("D"."IS_MANUAL_CORR_ACL_B"=1)
43 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
filter("D"."R_OBJECT_ID"='09be057280000e36' OR "D"."R_OBJECT_ID"='09be057280000e46')
45 - access("DS"."ACL_DOMAIN"="ACLS"."OWNER_NAME" AND "DS"."ACL_NAME"="ACLS"."OBJECT_NAME")
46 - access("OC"."FK_DM_ACL_ID_C"="ACLS"."R_OBJECT_ID")
53 - access("AG"."ACCESS_GROUP_ID_I"="OCP"."FK_ACCESS_GROUP_ID_I" AND
"OC"."OBJECT_CLASS_ID_I"="OCP"."FK_OBJECT_CLASS_ID_I" AND "E"."ENTITLEMENT_ID_I"="OCP"."FK_ENTITLEMENT_ID_I")
57 - filter("DS"."I_HAS_FOLDER"=1 AND "DS"."R_LOCK_OWNER"='test1')
58 - access("DS"."R_OBJECT_ID"='09be057280000e36' OR "DS"."R_OBJECT_ID"='09be057280000e46')
59 - filter("D"."IS_MANUAL_CORR_ACL_B"=1)
60 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
filter("D"."R_OBJECT_ID"='09be057280000e36' OR "D"."R_OBJECT_ID"='09be057280000e46')
64 - access("ENT"."ENTITLEMENT_CODE_C"='check_in_doc' OR "ENT"."ENTITLEMENT_CODE_C"='unlock_doc')
69 - filter("DS"."I_HAS_FOLDER"=1 AND "DS"."R_LOCK_OWNER"='test1')
70 - access("DS"."R_OBJECT_ID"='09be057280000e36' OR "DS"."R_OBJECT_ID"='09be057280000e46')
71 - filter("D"."IS_MANUAL_CORR_ACL_B"=1)
72 - access("DS"."R_OBJECT_ID"="D"."R_OBJECT_ID")
filter("D"."R_OBJECT_ID"='09be057280000e36' OR "D"."R_OBJECT_ID"='09be057280000e46')
76 - access("ENT"."ENTITLEMENT_CODE_C"='chg_perm' OR "ENT"."ENTITLEMENT_CODE_C"='delete_doc' OR
"ENT"."ENTITLEMENT_CODE_C"='edit_metadata')
Note: cpu costing is off