Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SELECT Records from a PARENT-CHILD set which DONT appear as CHILD in the same set!!
This query takes a lot of time when it there are many records in
BC_ITEM_SOURCE_MAP corresponding to the dataset the query calls. All
other
time, it works OK. I've found out this to be involved with the
construction of the view -
CREATE OR REPLACE VIEW BC_1ST_LEVEL_ADV_SHARED_VERS_1 ( USER_ID,
OWNER, RECEIVING_WORKSPACE, VERSION_ID ) AS select DISTINCT SV.USER_ID, SV.OWNER, SV.RECEIVING_WORKSPACE, IAM.CHILD VERSION_ID from (select DISTINCT a.user_id, a.owner, a.receiving_workspace, i.version_id from BC_ACCESS a, BC_ROLES r, BC_ITEM_SOURCE_MAP ism, BC_ITEM_VERSIONS i where i.supplier_shared_p = '1' and ism.item_buy = i.item_id and ism.branch_id = i.branch_id and ism.start_version <= i.version_id and (ism.end_version >= i.version_id or ism.end_version is null) and ism.owner = a.owner and (ism.maker = a.receiving_workspace) --or ism.seller = a.receiving_workspace) and a.role_id = r.role_id and (r.access_type_cc = BC_ACCESS_TYPE_SUPPLIER_CC OR R.ACCESS_TYPE_CC = BC_ACCESS_TYPE_ADV_SUPPLIER_CC)) SV, BC_ITEM_ASSY_MAP IAM where SV.VERSION_ID = IAM.PARENT AND IAM.CHILD_SUPP_SHARED_P = '1' AND NOT EXISTS ( SELECT '' FROM BC_ITEM_SOURCE_MAP ISM, BC_ITEM_VERSIONS V WHERE V.VERSION_ID = IAM.CHILD AND ISM.ITEM_BUY = V.ITEM_ID AND ISM.BRANCH_ID = V.BRANCH_ID AND ISM.START_VERSION <= IAM.CHILD AND (ISM.END_VERSION >= IAM.CHILD OR ISM.END_VERSION IS NULL)
AND ISM.OWNER = SV.OWNER and (ism.maker = SV.receiving_workspace or ism.seller = SV.receiving_workspace) )
The main problem being, this view BC_1ST_LEVEL_ADV_SHARED_VERS tries
to see
'all items sourced to a supplier that don't appear as children in the
same
dataset'. So basically, we are trying to see all records from a
dataset
(hierarchial) which don't appear as child to itself or any other
parent in the
same dataset.
This query belongs to 'Supplier Access Module'. While a supplier tries
to see
an item it figures out the access rules for the supplier and shows him
only the
items that appear at the topmost level and nowhere below for the
sourced item.
Bottomline is, that takes almost 40% CPU and at least 3 to 8 seconds
to
execute. So whenever suppliers access the application, the app always
(almost)
hangs.
Since, CONNECT BY cannot be joined, we have to use expensive OUTER
JOIN and
stuff like that. So basically I guess I have to find a way to see if I
can find
such data and quickly compare of it being present nowhere in 'lower
level in
the same tree'.
Any other way to do that? I mean using LEVEL and HAVING COUNT(LEVEL) or something like that?
Thanks in appreciation,
NM
Received on Thu May 16 2002 - 13:33:45 CDT