Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance of a Query : which one is giving good performace Expand / Collapse
Author
Message
Posted Friday, December 28, 2012 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:43 AM
Points: 20, Visits: 43
Hi Please help me...

I want join 4 tables in the Oracle.Table 1 is having 4 different where conditions.

Which one is better (Performance wise)

1.Join all 4 tables and write table1 where conditions globally (After Joining 4 tables)
2.Make Table1 as Derived Table with all where conditions and joining with 3 tables.

Please help me
Post #1400858
Posted Friday, December 28, 2012 3:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Test them and see?

Alternately, maybe ask on an Oracle forum where you're more likely to get an answer.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1400860
Posted Friday, December 28, 2012 12:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:22 PM
Points: 1,403, Visits: 6,900
Can't tell much without actually seeing it. I had success using orafaq, just be sure you read the forum guide. Do you have a testcase (create table, insert data, select query,query plan)?
Post #1401000
Posted Monday, January 21, 2013 5:12 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:00 PM
Points: 179, Visits: 592
Generally speaking (as this is a general question), probably Option 1 as it lets Oracle pick the best way of applying predicates. Under limited circumstances, option 2 will limit the way in which it applies predicates.

Ask a general question get a general answer.
Post #1409752
Posted Tuesday, January 22, 2013 6:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Raghu.K (12/28/2012)
I want join 4 tables in the Oracle.Table 1 is having 4 different where conditions.

Which one is better (Performance wise)

1.Join all 4 tables and write table1 where conditions globally (After Joining 4 tables)
2.Make Table1 as Derived Table with all where conditions and joining with 3 tables.


Not much to work with.
Assuming you have the right indexes on place, test both cases. Since this is an Oracle question I would suggest to trace/tkprof both solutions then look at buffer_gets on both of them, usually the solution with the less buffer_gets would be the one that would perform the better.

Hope this helps.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1410307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse