 Performance tuning [message #345145] |
Tue, 02 September 2008 07:11  |
NewLife
Messages: 170
Registered: April 2008
|
Senior Member |
|
|
The below query gives me duplicate records as shown after the query, I need to select only DISTINCT records without using distinct keyword because if i use distinct the cost increases 10 fold (from 1000 to 11000), so can you guys suggest me anything ?
select
fm.ds as Functional_mgr,
rp.ds as Responsible_person,
res.ds as Resource_name,
from ADM.MEMO_MV dup,
av_element e1,
av_element e2,
av_resreq rq,
adm.memo_alerts dma,
av_resource fm,
av_resource rp,
av_resource res,
av_resource rs
where dup.Project_Name = rq.proj
and dup.Activity_Name = rq.act
and e1.resp1<>e2.resp1
and (e2.category = 'GT OBS' AND e2.element = dup.Activity_OBS)
and nvl(dma.sent_flag,'N') = 'N'
and rs.rc02 not like '%D'
and rs.rc01 <> 'DEACT'
and fm.rn(+) = e2.resp1
and rp.rn(+) = dup.Responsible_person
and res.rn(+) = rq.rn
and rq.ver in ( 0,97 )
Gives me the following output:
FUNCTIONAL_MGR RESPONSIBLE_PERSON RESOURCE_NAME
John Maria Young
John Maria Young
Jacob Sam Michael
Jacob Sam Michael
NOTE* I need to get the distinct values without using distinct because of the time and cost involved. There are around 200 to 1000 records
desired output
FUNCTIONAL_MGR RESPONSIBLE_PERSON RESOURCE_NAME
John Maria Young
Jacob Sam Michael
|
|
|
|
|
|
| Re: Performance tuning [message #345151 is a reply to message #345145] |
Tue, 02 September 2008 07:26  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you don't want to include a distinct command, (or presumably GROUP BY or Analytics, as they will do similar operations) then you'll have to create a set of WHERE clauses that restrict you to non-duplicate rows.
And no, we don't know what you'll need to add to the WHERE clause.
You'll need to look in detail at al the columns from all the tables in the duplicate rows, and see what extra constraints you can add.
|
|
|
|
|
|
| Re: Performance tuning [message #345155 is a reply to message #345145] |
Tue, 02 September 2008 07:33 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
SELECT COUNT(*)
FROM av_resource rs
WHERE rs.rc02 not like '%D'
AND rs.rc01 <> 'DEACT';
I bet this query will return 2.
As you included AV_RESOURCE table into query without joining conditions (just these filtering ones), it will make cartesian product with other resultset rows.
As you do not use its columns anywhere, it can be removed (if it is not simplification of the real problem).
|
|
|
|
|
|
| Re: Performance tuning [message #345162 is a reply to message #345145] |
Tue, 02 September 2008 07:52 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
How about you post the explain plan of the query. As others have already mentioned it looks like you could have a cartesian product. I have noticed one more table which is not joined properly.
| Quote: |
...
adm.memo_alerts dma,
...
and nvl(dma.sent_flag,'N') = 'N'
|
Apart from the filter I cannot see any join condition with the main query.
Regards
Raj
|
|
|
|
|
|
| Re: Performance tuning [message #345180 is a reply to message #345168] |
Tue, 02 September 2008 09:17 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Now let's try formating it and posting it.
We'd rather you did something like
EXPLAIN PLAN FOR <query>
SELECT * FROM table(dbms_xplan.display()); |
|
|
|
| Re: Performance tuning [message #345186 is a reply to message #345180] |
Tue, 02 September 2008 09:24 |
NewLife
Messages: 170
Registered: April 2008
|
Senior Member |
|
|
hey guys, i got to run home, i will come back day after tomorrow and post it, gotta read that sticky topic regarding performance 
Thanks michel, JRowbottom, flyboy & raj , hoping for your continued support, you guys rock
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
