Slow performance to use 'UNION'

The following query took 25 senconds to run, the first select returned 1645 records and took 406 msec, the second select returned 0 record and took 31 msec. I had tried to use the UNION ALL but the response time seems inconsistent.

SELECT S.SUPPLIER_ID AS "SUPPLIER_ID"
FROM COMPANY_DEFAULT_PERMISSIONS cdp, USERNAME u, SUPPLIER S
WHERE u.username_id = 117120
AND u.company_id = cdp.company_id
AND U.COMPANY_ID = S.COMPANY_ID
AND S.SUPPLIER_ID NOT IN (SELECT SUPPLIER_ID
FROM SUPPLIER_SPM_ACL SSA
WHERE SSA.SUPPLIER_ID = S.SUPPLIER_ID AND (SSA.username_id = 117120 OR SSA.user_group_id IN (SELECT user_group_id FROM USERNAME_GROUP_MAP WHERE username_id = 117120)))
AND cdp.can_read = 1
AND cdp.object_type_id = 9
UNION
SELECT S.SUPPLIER_ID AS "SUPPLIER_ID"
FROM SUPPLIER_SPM_ACL SSA, USERNAME u, SUPPLIER S
WHERE u.username_id = 117120
AND u.username_id = SSA.username_id
AND U.COMPANY_ID = S.COMPANY_ID
AND S.SUPPLIER_ID = SSA.SUPPLIER_ID
AND SSA.can_read = 1

Execution plan ?
Index Details ?

Tamil

Thanks for your help, I am not allowed to use the 'UNION ALL' since it will return the duplicate records.

PHP Code:

INDEX_NAME TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ -------------------------- COMPANY_DEF_PERMS_PK COMPANY_DEFAULT_PERMISSIONS OBJECT_TYPE_ID COMPANY_DEF_PERMS_PK COMPANY_DEFAULT_PERMISSIONS COMPANY_ID CO_DEF_PERMISSIONS_IN1 COMPANY_DEFAULT_PERMISSIONS COMPANY_ID SUPPLIER_IN1 SUPPLIER NAME SUPPLIER_IN3 SUPPLIER CONTACT_CONTACT_ID SUPPLIER_IN4 SUPPLIER COMPANY_ID SUPPLIER_IN5 SUPPLIER PARENT_SUPPLIER_ID SUPPLIER_PK SUPPLIER SUPPLIER_ID SUPPLIER_SPM_ACL_PK SUPPLIER_SPM_ACL SUPPLIER_SPM_ACL_ID USERNAME_IN1 USERNAME COMPANY_ID USERNAME_IN2 USERNAME CONTACT_CONTACT_ID INDEX_NAME TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ -------------------------- USERNAME_IN3 USERNAME USERTYPE_ID USERNAME_IN4 USERNAME SUPPLIER_ID USERNAME_IN5 USERNAME DROPTO_CONTACT_ID USERNAME_IN6 USERNAME REMITTO_CONTACT_ID USERNAME_IN7 USERNAME SHIPTO_CONTACT_ID USERNAME_IN8 USERNAME LAST_PROJECT_ID USERNAME_IN9 USERNAME USERNAME_ALIAS_ID USERNAME_PK USERNAME USERNAME_ID USERNAME_UK1 USERNAME USERNAME USERNAME_UK1 USERNAME NAME_SPACE Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=55 Card=17 Bytes=410 ) 1 0 SORT (UNIQUE) (Cost=55 Card=17 Bytes=410) 2 1 UNION-ALL 3 2 FILTER 4 3 NESTED LOOPS (Cost=22 Card=16 Bytes=384) 5 4 NESTED LOOPS (Cost=3 Card=1 Bytes=16) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'USERNAME'(Cost=2 Card=1 Bytes=8) 7 6 INDEX (UNIQUE SCAN) OF 'USERNAME_PK' (UNIQUE)(Cost=1 Card=39444) 8 5 TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY_DEFAULT_PERMISSIONS' (Cost=1 Card=1 Bytes=8) 9 8 INDEX (UNIQUE SCAN) OF 'COMPANY_DEF_PERMS_PK'(UNIQUE) 10 4 TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=19 Card=16Bytes=128) 11 3 FILTER 12 11 TABLE ACCESS (FULL) OF 'SUPPLIER_SPM_ACL' (Cost=5 Card=1 Bytes=9) 13 11 INDEX (UNIQUE SCAN) OF 'USERNAME_GROUP_MAP_PK' (UNIQUE) (Cost=1 Card=1 Bytes=8) 14 2 HASH JOIN (Cost=27 Card=1 Bytes=26) 15 14 TABLE ACCESS (FULL) OF 'SUPPLIER_SPM_ACL' (Cost=5 Card=28 Bytes=280) 16 14 NESTED LOOPS (Cost=21 Card=328 Bytes=5248) 17 16 TABLE ACCESS (BY INDEX ROWID) OF 'USERNAME' (Cost=2 Card=1 Bytes=8) 18 17 INDEX (UNIQUE SCAN) OF 'USERNAME_PK' (UNIQUE) (Cost=1 Card=39444) 19 16 TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=19 Card=328 Bytes=2624)

Avoid full table scans on SUPPLIER and SUPPLIER_SPM_ACL tables.

Use HINT if index is not used on those tables.

Example: /*+ INDEX(S SUPPLIER_IN4) */ .

Tamil

After I added the index hint and the execution plan doesn't have any full table scan, however the query run much slower ( from 390 msec to 56 sec).

SELECT /*+ INDEX(S SUPPLIER_PK) */ S.SUPPLIER_ID AS "SUPPLIER_ID"
FROM COMPANY_DEFAULT_PERMISSIONS cdp, USERNAME u, SUPPLIER S
WHERE u.username_id = 117120
AND u.company_id = cdp.company_id
AND U.COMPANY_ID = S.COMPANY_ID
AND S.SUPPLIER_ID NOT IN(SELECT /*+ INDEX(SSA SUPPLIER_SPM_ACL_PK) */ SUPPLIER_ID
FROM SUPPLIER_SPM_ACL SSA
WHERE SSA.SUPPLIER_ID = S.SUPPLIER_ID AND
(SSA.username_id = 117120 OR SSA.user_group_id
IN (SELECT user_group_id FROM USERNAME_GROUP_MAP WHERE username_id = 117120)))
AND cdp.can_read = 1
AND cdp.object_type_id = 9

I don't see how forcing the index with a hint would improve the performance if the CBO determined a FTS to be more efficient from the get go.

Perhaps if you explained what each query is trying to accomplish.
Is there any reason an OR statement in the where-clause could not
accomplish what you need?

Does this improve anything?

PHP Code:

select decode(a, null, b, a) supplier_id from (SELECT s.supplier_id a FROM company_default_permissions cdp, username u, supplier s WHERE u.username_id = 117120 AND u.company_id = cdp.company_id AND u.company_id = s.company_id AND s.supplier_id NOT IN ( SELECT supplier_id FROM supplier_spm_acl ssa WHERE ssa.supplier_id = s.supplier_id AND ( ssa.username_id = 117120 OR ssa.user_group_id IN ( SELECT user_group_id FROM username_group_map WHERE username_id = 117120))) AND cdp.can_read = 1 AND cdp.object_type_id = 9) full outer join (SELECT s.supplier_id b FROM supplier_spm_acl ssa, username u, supplier s WHERE u.username_id = 117120 AND u.username_id = ssa.username_id AND u.company_id = s.company_id AND s.supplier_id = ssa.supplier_id AND ssa.can_read = 1) on a = b

I did try the query you suggested but it didn't help too much for the performance, but still thanks for your time and effort.

I replaced the 'EXISTS' with 'IN' in the sub-query and the entire query run very fast.

Quote:

I had tried to use the UNION ALL but the response time seems inconsistent.

What do you mean by that? Do you realy need UNION or would UNION ALL also be ok?