Performance of a Query : which one is giving good performace

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » Performance of a Query : which one is giving...

Performance of a Query : which one is giving good performace

Rate Topic

Display Mode

Topic Options

Author

Message

Raghu.K

Posted Friday, December 28, 2012 3:45 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, December 31, 2012 3:01 AM
Points: 18, Visits: 35

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

GilaMonster

Posted Friday, December 28, 2012 3:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 38,105, Visits: 30,396

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

Jo Pattyn

Posted Friday, December 28, 2012 12:02 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 1,333, Visits: 4,418

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

nick.mcdermaid

Posted Monday, January 21, 2013 5:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:15 PM
Points: 162, Visits: 437

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

PaulB-TheOneAndOnly

Posted Tuesday, January 22, 2013 6:35 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 3,017, Visits: 4,471

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 »

Permissions