topic hint oracle in Application Development and Automation Discussions

hint oracle

Former Member — Sun, 16 Mar 2008 07:55:58 GMT

hi,

when is the time to use HINT commend and when not

and what is the benefit of using this commend,

i see example like :

%_HINTS ORACLE 'TABLE catsdb ABINDEX(~1)'.

what is ABINDEX(~1)?

Regards

Re: hint oracle

Former Member — Sun, 16 Mar 2008 08:07:03 GMT

Hi Shnya,

Check this link which tell abouts HINTS statement in Oracle. I hope this may help you up to some extent.

http://www.adp-gmbh.ch/ora/sql/hints/index.html

Thanks,

Vinay

Re: hint oracle

Former Member — Mon, 17 Mar 2008 08:35:22 GMT

Hints should only be used in exceptional cases.

The optimzer should in principle be able to find out, what is the best index. Sometimes this does not work,

for example when there are several indexes and your where condition has fields from several indexes,

then the selectivity estimation can be wrong. The optimzer thinks index 2 is the best, actually index 1 is better.

Therefore:

Check SQL trace and explain first, try to optimzie where condition, if nothing works use a hint (for your actual

database !!)

Your example shows a hints, which tells the database to use index 1.

Siegfried

Re: hint oracle

Former Member — Mon, 17 Mar 2008 12:35:25 GMT

Hi,

always keep your table statistics up-to-date.

This means that the statistics should reflect the current data volume of your table(s). Normally the Basis is running statistics on a daily basis on the tables that need new statistics.

The decision if a table gets analyzed comes from a monitoring of DML (Insert, Update, Deletes) against that table.

The statistics are a MUST for a proper execution plan. If you have good statistics you will rarely need hints.

With using hints you try to be more clever than the Optimizer of the database.

The danger of using hints is, that changes in data volume can cause big performance issues because the hint is not appropriate anymore. Furthermore you may hiding a flaw in your data model design (i.e. wrong or missing indexes).

I use hints only when special features requiring them (i.e. PARALLEL hints on SQL statements).

Hope that helps...