-
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)
Last edited by tamilselvan; 09-09-2004 at 10:08 AM.
-
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
- Cookies
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|