SQL Optimization : Rule Based Vs Choose Based

[bcbcheng]

When u try to optimize a SQL statement, which one u will choose ? <br>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, <br>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 ?<br>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 ? <br><br><br>

 

[MikeLacey]

Have you run ANALYSE TABLE ... ? The cost based optimiser relies on the info collected by ANALYSE. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>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?"

 

[MikeLeung]

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.<br><br>Mike

 

[bcbcheng]

Yes, of course I have analyze table. I use the following commands for each table :<br><br>ANALYZE TABLE [Table Name ] COMPUTE STATISTICS FOR TABLE ;<br>ANALYZE TABLE [Table Name ] COMPUTE STATISTICS FOR ALL INDEXES ;<br>ANALYZE TABLE [Table Name ] COMPUTE STATISTICS FOR ALL COLUMNS ;<br><br>These 3 commands are run for each table, but, the optimizer still can't use index scan for some tables. Any Ideas ?<br><br>

 

[wadewells]

&nbsp;&nbsp;First, read the Oracle Server Tuning Manual for your release.<br>&nbsp;&nbsp;Rule-based will use indexes if available, not if better performance.&nbsp;&nbsp;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?)<br>&nbsp;&nbsp;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.<br>&nbsp;&nbsp;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.<br>&nbsp;&nbsp;I've found that Cost-Based (use by Choose) may screw up with &gt;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.

 

[MikeLacey]

&quot;Yes, of course I have analyze table.&quot;<br><br>My aplogies. I did not mean to imply that you don't know what you're doing! &lt;s&gt; Remember that we have lots of people here who don't (me -- for instance)<br><br>Anyway - I would agree with Wadewell that sometimes, with small tables, it doesn't matter but usually full table scans are hideously bad news.<br><br>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.<br><br>Not a trivial process this - mature applications often have many indexes and removing/changing one or two can degrade performance in other areas. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>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?"

 

[Guest_imported]

Visit site: <A HREF="

http://www.wsl.ch/relics/rauminf/riv/datenbank/general/tinalondon.html"

TARGET="_new">

http://www.wsl.ch/relics/rauminf/riv/datenbank/general/tinalondon.html</A><br>There

are very nice advices how to optimize query.