Using Index Hints 2

[brettanicus]

We're using BusObj to query an Oracle database. To improve Oracle performance, I need to use index hints in the SQL. I haven't been able to find any documentation or tips on how to do this. Any ideas? Thanks!

 

[PAULSGS]

You have to build objects which contain the hint text

If you wanted a hint as complex as this one (copied from OTN)


/*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
USE_NL (glcc glf) USE_MERGE (gp gsb) */


You would have to build an object, with a meaningful name which contains the full text of the hint and select this first when you build your query

The objects built in this fashion would not pass the "Parse" test, but will work when combined with other objects

 

[jimbopalmer]

you are the Universe Designer?

when you make an object, you specify a table and column

mytable.mycolumn

to add a hint where that object is the first column in the query

/*+ all_rows */ mytable.mycolumn

when BO makes the SQL, the hint will precede the first value in the select and you are done! I tried to remain child-like, all I acheived was childish.

 

[ATP]

If your on Oracle 8.1.x I've found that fully analyzing the table and indexes gives ORACLE a better chance of choosing the correct path.

Plus if I never want an index to be used, I use an opperator on the join. e.g. upper(a.Supplier_id) = b.supp_id.

I've found using Hints a pain as they can speed up one query but kill all the others.