| Tuning the query - Outer join [message #200457] |
Tue, 31 October 2006 00:21  |
bagulia_pilla
Messages: 25
Registered: July 2006
|
Junior Member |
|
|
HI,
Please help me to tune this query as this is taking more time in explain plan
SELECT DISTINCT prof.cms_lsp_lmt_profile_id AS lmtprofile, prof.cms_orig_country,
prof.cms_orig_organisation prof_org, limits.cms_bkg_country limit_country,
limits.cms_bkg_organisation limit_org, sec.security_location,
sec.security_organisation, col_task.security_location, cc.dmcl_cntry_iso_code,
sci_lsp_co_borrow_lmt.cms_bkg_country co_bo_lmt_loc,
sci_lsp_co_borrow_lmt.cms_bkg_organisation co_bo_lmt_org,
sec1.security_location co_bo_sec_loc, sec1.security_organisation co_bo_sec_org
FROM sci_lsp_lmt_profile prof,
sci_lsp_appr_lmts limits,
cms_limit_security_map sec_map,
cms_security sec,
cms_cc_task cc,
cms_collateral_task col_task,
sci_lsp_co_borrow_lmt,
cms_limit_security_map sec_map1,
cms_security sec1
WHERE prof.cms_lsp_lmt_profile_id = limits.cms_limit_profile_id(+)
AND limits.cms_lsp_appr_lmts_id = sec_map.cms_lsp_appr_lmts_id(+)
AND limits.cms_lsp_appr_lmts_id = sci_lsp_co_borrow_lmt.cms_limit_id(+)
AND sec_map.cms_collateral_id = sec.cms_collateral_id(+)
AND prof.cms_lsp_lmt_profile_id = cc.cms_lsp_lmt_profile_id(+)
AND sec_map.cms_collateral_id = col_task.cms_collateral_id(+)
AND sci_lsp_co_borrow_lmt.cms_lsp_co_borrow_lmt_id = sec_map1.cms_lsp_co_borrow_lmt_id(+)
AND sec_map1.cms_collateral_id = sec1.cms_collateral_id(+)
Thanks in advance.
Please find the explain plan ..attached
[Updated on: Tue, 31 October 2006 00:22] Report message to a moderator |
|
|
|
| Re: Tuning the query - Outer join [message #200464 is a reply to message #200457] |
Tue, 31 October 2006 00:50  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
This query only has join predicates (WHERE conditions). There are no filter predicates. Whats more, all of the joins are outer joins, so you are going get to EVERY row back, no matter what.
On this basis, the plan is excellent. When you join every row in two tables, HASH JOIN is the king.
Now, this is important:
I notice you have a DISTINCT in the query. Why? Are all of the outer-joined tables joined on a UNIQUE or PRIMARY KEY? Or are they joined on a foreign key? eg. Is cms_lsp_appr_lmts_id unique in sci_lsp_appr_lmts? What about sec_map.cms_lsp_appr_lmts_id?
If two or more of them are non-unique, then you may have a problem (a problem that is returning duplicates).
Consider what would happen if 1 PROF had 100 CC rows and 500 LIMITS rows. The PROF would join to CC, giving 100 rows with the same PROF info, but different CC info. Then the PROF info is joined to LIMITS: 100x500 = 50,000 rows. Those 50,000 rows represent a cartesian join between the CC rows and the LIMITS rows.
Try replacing the SELECT clause with "SELECT COUNT(*)". What is the number returned? If that number is bigger than the biggest table, then you have a data model problem, not a SQL problem. If this is the case, then there is no point tuning the SQL because the SQL is wrong. There are no circumstances where such a SQL can ever possibly make sense - it means that you don't understand your data model properly.
Ross Leishman
|
|
|
|
|
|
| Re: Tuning the query - Outer join [message #200674 is a reply to message #200672] |
Tue, 31 October 2006 20:49 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Um, you didn't attach a plan. Nor did you read my original reply. If you want help, help yourself first by reading the advice you have been given.
- Go back and read it again.
- Keep reading it until you understand it.
- If you don't understand it, say exactly which bit you don't understand, and tell me what you think it might mean.
- Look at the cardinality of the other 8 joins, not just the one that I happened to cite as an example.
- Do the COUNT(*) check that I recommended.
Ross Leishman
|
|
|
|
| Re: Tuning the query - Outer join [message #200980 is a reply to message #200674] |
Thu, 02 November 2006 02:12 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Correct me if i am wrong.
prof.cms_lsp_lmt_profile_id = limits.cms_limit_profile_id(+)
AND limits.cms_lsp_appr_lmts_id = sec_map.cms_lsp_appr_lmts_id(+)
AND limits.cms_lsp_appr_lmts_id = sci_lsp_co_borrow_lmt.cms_limit_id(+)
First line says get all the record from "prof" table outer join with "limits" table. So it will be something like this
prof limits
1 1
2 2
3 3
4 NULL
5 NULL
second line says outer join "limits" with "sec_map".
So that means the condition will hold good only for first three records. fourth record it will fail becauase you are comparing NULL. So from my point of view outer join is waste of time or they have not understood what outer join means or this is what they want to do or could somebody explain to me whether i have understood it incorrectly.
prof limits sec_map
1 1 1
2 2 NULL
3 3 NULL
4 NULL 4 **** this record will not be fetched
5 NULL NULL *** this record also will not be fetched
If my guess is correct we expect all the records from prof and the corresponding matching records from limits, sec_map etc if they exists... But if you think about it it is working more or less like a equi join + some outer join.
"this is not the first time where i am getting confused after seeing outer join queries."
Awaiting some clarifications.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
