Slow performance to use 'UNION'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Slow performance to use 'UNION'

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Execution plan ?
    Index Details ?

    Tamil

  3. #3
    Join Date
    Apr 2002
    Posts
    73
    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 ROWIDOF 'USERNAME'(Cost=2 Card=1 Bytes=8)
       
    7    6               INDEX (UNIQUE SCANOF 'USERNAME_PK' (UNIQUE)(Cost=1 Card=39444)
       
    8    5             TABLE ACCESS (BY INDEX ROWIDOF 'COMPANY_DEFAULT_PERMISSIONS' (Cost=1 Card=1 Bytes=8)
       
    9    8               INDEX (UNIQUE SCANOF 'COMPANY_DEF_PERMS_PK'(UNIQUE)
      
    10    4           TABLE ACCESS (FULLOF 'SUPPLIER' (Cost=19 Card=16Bytes=128)
      
    11    3         FILTER
      12   11           TABLE ACCESS 
    (FULLOF 'SUPPLIER_SPM_ACL' (Cost=5
    Card
    =1 Bytes=9)
      
    13   11           INDEX (UNIQUE SCANOF '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 (FULLOF '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 ROWIDOF 'USERNAME' (Cost=2 Card=1 Bytes=8)
      
    18   17             INDEX (UNIQUE SCANOF 'USERNAME_PK' (UNIQUE) (Cost=1 Card=39444)
      
    19   16           TABLE ACCESS (FULLOF 'SUPPLIER' (Cost=19 Card=328 Bytes=2624
    Last edited by tamilselvan; 09-09-2004 at 10:08 AM.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  5. #5
    Join Date
    Apr 2002
    Posts
    73
    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

  6. #6
    Join Date
    Oct 2002
    Posts
    182
    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(anullbasupplier_id from 
    (SELECT s.supplier_id a
      FROM company_default_permissions cdp
    username usupplier 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 usupplier 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 
    - Cookies

  7. #7
    Join Date
    Apr 2002
    Posts
    73
    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.

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width