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