Re: slow query - index usage question

Telemachus wrote:
> Beware of quoting other people like that (TK) without posting a
> reference....

Apologies for that, but almost every time at http://asktom.oracle.com anyone comes up with a question like the OP's 'why isn't my index being used although I'm hinting at it?', the answer includes in a way or another what I said, that a hint adjusts the factors that are used when calculating the costs of a given query, thus making the usage e.g. of an index more appealing but still not a requirement.

So I went to asktom.oracle.com, searched for hint and this is what the first article on the topic contained:

<quote>
Many things influence the "cost" of a query. The "cost" is just some artificial number we arrived at to select a query given a certain environment. Take a query and get its cost. Now, add a hint that makes the query run faster and check out its cost -- it might be higher, might be lower then the first. Why? Because the environment with the hint had us adjust the internal numbers we assign to various operations -- in an attempt to make your hint "look like" the best way to go. It might have adjusted the over cost for the plan you wanted to be lower then the plan we did without the hint, or it may have made the plan we did without the hint extremely expensive (artificially inflated its cost). </quote>

The next hits had variations on the same theme. But still apologies to Tom Kyte, I certainly won't quote you without reference!

>
> I'm not sure if you can use SQLT (the new explainer) on 806,but please post
> as much detail as you can from the EXPLAIN
>

I was answering the OP, so my explain plans won't be of much help ;-)

>
> Of course it can't use an index... You're trying to do a NOT IN ! indexes
> don't store nulls so therefore it must scan the whole table.

Not me, the OP :-). And the question was why there is no index used on the subselect, so I think you didn't read the original question.

>
> Also, the inner distinct looks like it could be removed to me. if it both
> VINs were not null and the distinct hauled out
[..]
correct but not the point (the explain plan doesn't change in my test case and I didn't asked).

But while we're at this, could anyone explain the difference between declaring a column not null (with alter table modify or create table) and adding a named check constraint on the same column:

create table t (x int not null);
-- which will create a check constraint named sys_cXXXX

vs.

create table t( x int);
alter table t add constraint not_null_check check (x is not null);

Because the latter will not allow CBO to use an index.

Regards,
Holger Received on Wed Mar 19 2003 - 07:10:52 CST