Home » RDBMS Server » Performance Tuning » Performance tuning (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
icon6.gif  Performance tuning [message #345145] Tue, 02 September 2008 07:11 Go to next message
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 #345148 is a reply to message #345145] Tue, 02 September 2008 07:24 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Why don't you remove duplicate records from your table?
Re: Performance tuning [message #345151 is a reply to message #345145] Tue, 02 September 2008 07:26 Go to previous messageGo to next message
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 #345154 is a reply to message #345151] Tue, 02 September 2008 07:30 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
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.



Yeah i was lloking at this, probably i need to dig deeper, anyways thanks for your advice, i shall revert back with my findings Smile
Re: Performance tuning [message #345155 is a reply to message #345145] Tue, 02 September 2008 07:33 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #345161 is a reply to message #345145] Tue, 02 September 2008 07:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There seems to be no restriction on e1, other than that it cannot be equal to e2.resp1
Re: Performance tuning [message #345162 is a reply to message #345145] Tue, 02 September 2008 07:52 Go to previous messageGo to next message
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 #345168 is a reply to message #345162] Tue, 02 September 2008 08:28 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Well here's the explain plan attached

Re: Performance tuning [message #345180 is a reply to message #345168] Tue, 02 September 2008 09:17 Go to previous messageGo to next message
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());
than just a dump from v$sql_plan
Re: Performance tuning [message #345186 is a reply to message #345180] Tue, 02 September 2008 09:24 Go to previous message
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 Smile

Thanks michel, JRowbottom, flyboy & raj , hoping for your continued support, you guys rock

Previous Topic: UPDATE ISSUE
Next Topic: Not Exists Vs Join
Goto Forum:
  


Current Time: Tue Dec 06 06:13:10 CST 2016

Total time taken to generate the page: 0.10909 seconds