Oracle Hints

[sumit]

Hi,

I have table with X,Y,Z columns. I have an index on these 3 columns with position X=1 Y=2 and Z=3.

Now I am writing a query with the following condition ...

......
....
where y=? and Z=?;

As far I know though I am not using X column in my where clause in Query , Oracle will not use the index. Is that right?

If so, then is there any hints so that explicitly I can tell oracle to use that specified index.

Like /*+ ORDERED index (XXX, XXXXX) */


Thanks and regards

[prakashs43]

Originally posted by sumit
Hi,


As far I know though I am not using X column in my where clause in Query , Oracle will not use the index. Is that right?

Thanks and regards

yes that is right if u are using a single index with 3 columns in the order specified . if u create three different index seperately with one column in each then these indexes will be used in where clause in whatever order u mention . ofcourse u cannot use certain functions in the where clause along with those columns .

[chrisrlong]

It depends on how selective it thinks x, by itself, is.

If it does not use the index, it might be right. Remember that once you start hinting, you are essentially taking over the responsibility of keeping that statement optimized forever. Given that, the hint you showed is correct:

SELECT --+ INDEX(T1,INDEX1)

...where T1 is the alias for the table is you used one in the statement. Otherwise it is the name of the table. INDEX1 is obviously the name of the index.

- Chris

[slimdave]

Sorry prakashs43, it depends on the version of Oracle, the init.ora, and the rest of the SQL statement. You can no longer give a definitive "no it won't use the index".

If you have a very wide table (few rows per block) and you want a count of rows where y=? and Z=?, then a fast full index scan is an option for later versions of Oracle. Some other queries may be unsuitable for this, espaecially if the query has to touch the table itself to return a result.

A very low value of "optimizer_index_cost_adj" will promote a fast full index scan.

Also, an index skip scan is an option for later versions of Oracle. If there are only very few unique values of "x" then Oracle might still optimize the access path to use the index.

Beware of the hint -- use INDEX() hint to try and promote a scan of a bitmap index in the wrong version of Oracle and you could find yourself scanning an unrelated btree index on the same table and killing your performance.

[chrisrlong]

BTW - my bad, I thought you had used X and Z in your query, not Y and Z

slimdave got it right - alhough I hadn't heard of the error he refers to.

- Chris

[slimdave]

It may be version specific, but I got bitten by it on 8.1.7.4 for HP/UX 64bit. Oracle's v9iR1 documentation states ...

"The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint."

Since 8.1.7 I analyze columns, indexes and indexed columns using the compute method, and stay clear of hints. The CBO has been doing a good job.

[chrisrlong]

Okay - thread-jacking is complete

In the database I am currently working on, there are over 600 tables with the top dozen having over 20 million records each.

There are also probably a few thousand queries with probably around a hundred that are what I call on-screen page-able reports - they call them queues. In any case, there can be dozens of permutations for each of these queues.

Now, given the large number of statements, keeping a useable SGA is important. A couple years before I arrived they hadn't implemented many bind variables and had essentially flooded the SGA causing multiple problems. Now they use a lot of binds, which is good for the SGA. However, the vast majority of fields in the database have extremely skewed data. All these fields would benefit from histograms. But, of course, histograms require hard-coded values. Reverting to hard-coded values would multiply the number of statements by many thousands, at least. Therefore, for all the major keys, no histograms can be used.

Why am I telling you all this? Because this is a situation where the optimizer simply does not have enough information to make good decisions. This database would collapse completely without a pervasive use of hinting. So, unfortunately, we are not all blessed with as perfect a database as you are . There are situations that simply require hinting.

Digression over

- Chris

[slimdave]

I think that in your place I might be strongly tempted to drift back towards hard-coded values for all those skewed columns, particularly where the performance benefit per report might be more than a few seconds.

Losing a few tenths of a second per query to a hard parse might be preferable to pervasive problems with bad optimizer plans, and if it makes that hard-parse ratio look bad then so be it -- there's an exception to every rule, whether it be "keep hard parses to a minimum" or "avoid the use of hints".

In the spirit of which, I'd try and keep the hinting as vanilla as possible -- INDEX_COMBINE() rather than INDEX(), and avoid specifying the index name if you could get away with it.

Now it's a bit of an application design issue, but I'm fond of dealing with highly-skewed data by using NULL in place of the most popular value -- this gives the optimizer no choice but to avoid the index when a predicate uses "is null" to get 80% of the rows, and gives it an option of using a much smaller index when a less popular value is required. Doesn't work for everything of course, and is difficult to apply to existing apps (though function-based indexes can be used to simulate this on an existing system with less hassle).