Re: Full table scan query

Morning Jonathan,

The hint was correct - I didn't fall into the old problem of having a comma between the alias and index name. The hint was indeed ignored.

As for the optimizer ignoring the hint, I think it could be down to index statistics, or similar, but I'm still a little puzzled as to why, for reasone that will become clear.

When we built a new index on the leading column only, that index was used.
When we hinted to use the existing index, that hint was ignored. When we hinted first_rows, that hint was used and the results were back in 40 millisecond, rather than 35 seconds. Nice. Unfortunately, our performance team have found that setting the first_rows hint on, results in slower responses elsewhere in the application (the app allows us to hint first_rows, or not, for the whole application, not selectively) so we had to look again at the problem.

We had just computed statistics and still we got a FTS. Finally, in a fit of desparation/inspiration we rebuilt the ignored index - suddenly, we are using the index and not the FTS.

When we checked with the customer, thay had not been rebuilding their indexes on a regular basis, and when they did, they stopped getting the FTS on this particular query, resulting in a much improved response time.

I'm loath to leave it at that until I understand what is going on, but for now, the customer is happy, while the developer and I are somewhat puzzled - still.

Regards,
Norman.

PS. Will look into 10053 and see what I get. Thanks.

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Posted At: Saturday, January 26, 2002 5:58 PM Posted To: server
Conversation: Full table scan query
Subject: Re: Full table scan query

Although there are some bugs in the optimizer in 8.0.5, it should still obey a hint unless there is a good reason why the hint should be ignored.

Although I doubt you will have made such a trivial error, is it possible that your index hint was incorrect - e.g. using the table-name instead of the alias in the hint ?

The event that Sybrand was suggesting came out with a typo, I think, it should have been 10053. Received on Mon Jan 28 2002 - 02:46:21 CST