×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Optimization : Rule Based Vs Choose Based

SQL Optimization : Rule Based Vs Choose Based

SQL Optimization : Rule Based Vs Choose Based

(OP)
When u try to optimize a SQL statement, which one u will choose ?
In my case, my SQL statement always join with 4 - 7 tables ( UNION appears 3 times for each SQL statement ). When I choose Choose-based, the showplan show that some tables use index scan, and 1 or 2 tables use full table scan. However,
when I choose Rule-based, the showplan will show that all tables are using index can. Thus, can I say that Rule-based is better for my case ?
I have also asked opinion from Oracle hotline, but the technician from Oracle told me that it is better to turn to Choose based. Should I choose Choose-based rather than Rule-based ?


RE: SQL Optimization : Rule Based Vs Choose Based

Have you run ANALYSE TABLE ... ? The cost based optimiser relies on the info collected by ANALYSE.

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site
Please -- Don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

RE: SQL Optimization : Rule Based Vs Choose Based

Yes, Oracle always recommend you to use Choose Base rather Rule Base optimiser as Oracle will phase out Rule Base Optimiser altogether in the future release. The only disadvantage of using Choose Base is you need to do Analyse Table periodically as what MikeLacey suggested. Otherwise, the optimiser will not have the updated stats and therefore do a table scan for you.

Mike

RE: SQL Optimization : Rule Based Vs Choose Based

(OP)
Yes, of course I have analyze table. I use the following commands for each table :

ANALYZE TABLE [Table Name ] COMPUTE STATISTICS FOR TABLE ;
ANALYZE TABLE [Table Name ] COMPUTE STATISTICS FOR ALL INDEXES ;
ANALYZE TABLE [Table Name ] COMPUTE STATISTICS FOR ALL COLUMNS ;

These 3 commands are run for each table, but, the optimizer still can't use index scan for some tables. Any Ideas ?

RE: SQL Optimization : Rule Based Vs Choose Based

  First, read the Oracle Server Tuning Manual for your release.
  Rule-based will use indexes if available, not if better performance.  If your tables are small, indexes can a waste (in the extreme case, if your table is only 1 block, why read a second block just to index a handful of records?)
  If your tables are larger, and if you are getting just a small subset of rows (that might require reading a subset of blocks), then indexing can help.
  The ultimate test is to do real timings. One easy way is to SET TIMING ON in SQL*PLUS and run the sql statements with these two optimizations.
  I've found that Cost-Based (use by Choose) may screw up with >4 tables, because the number of permutations and combinations of possible plans is too large to do an exhaustive analysis. You may get a sub-optimal plan. If so, the next step is to use HINTS to get the Cost-Based Optimizer to do something better.

RE: SQL Optimization : Rule Based Vs Choose Based

"Yes, of course I have analyze table."

My aplogies. I did not mean to imply that you don't know what you're doing! <s> Remember that we have lots of people here who don't (me -- for instance)

Anyway - I would agree with Wadewell that sometimes, with small tables, it doesn't matter but usually full table scans are hideously bad news.

If I get stuck with these problems I start by dropping all the indexes (making a note of them first) then running with trace turned on and analysing the results with tkprof. I then add indexes (not the ones I just dropped) to remove the scans.

Not a trivial process this - mature applications often have many indexes and removing/changing one or two can degrade performance in other areas.

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site
Please -- Don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close