Slow performance to use 'UNION'

[Blu]

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

[tamilselvan]

Execution plan ?
Index Details ?

Tamil

[Blu]

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

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) 

[tamilselvan]

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

[Blu]

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

[Cookies]

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?

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 

[Blu]

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.

[mike9]

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?