Re: slow query - index usage question

Sorry Holger

relevant comments aimed at OP and not you except for the TK thing.

On the constraint thing, I believe it's stored outside the table, whereas a NOT NULL on the column is a semaphore in the table header. Might be wrong though. We were having this discussion a long time ago (what constraints can affect the CBO ? )

PK - Yes
Unique - Yes
Foreign - We didn't decide
Check - show me an example !
View Check - Doubtful
Scope - Doubtful

"Holger Baer" <holger.baer_at_science-computing.de> wrote in message news:3E786C5C.4090706_at_science-computing.de...
> 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 - 10:56:42 CST