Index/performance tuning

[slimdave]

For #2 - please reread my post. I KNOW that I updated 100% of values for the other key to be 100% NULL. Yes, the optimizer would be dumb to use the index if that was the only parameter in the where clause. But that was my point -- that was the condition specified by the original poster. I am saying I don't think that the composite index gets used UNDER THAT CONDITION, and have demonstrated that.

I think you are being disingenuous here, because if all the values are null then it wouldn't make sense to use any of the three methods I suggest -- a full table scan would be the only efficient way of executing the query.

Instead you agree that methods one and three would work, and that two wouldn't. It makes no sense to then say "but we're talking about a situation where all rows are null".

There's also no-where in the original post where it says the address column is 100% NULL.

So, in your second attempt at a demo you are still wrong. The misguided distribution of data you are using renders the whole indexing question irrelevant.

[Mr.Hanky]

DING!!

Round 2

[paladin]

You said: "There's also no-where in the original post where it says the address column is 100% NULL."

The original post stated: "Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???"

You might be right - the original poster may have meant that NULL is PERMITTED - that of course is different. (The broken English in the question is ambiguous.) But then the question doesn't make sense to me - of course you can put an index on a column that permits NULL values.

And yes, I agree - if it is 100% NULL - then why index? Good question. If the query was:

select * from t
where object_type = 'hello';

instead of

select * from t
where object_type is NULL;

both the RBO and the CBO will in fact use the 1st index.

For a big table (many rows), such a query will run faster than doing a full table scan - because the index will quickly show there is no such case (where object_type = 'hello').

As to why in the real world you would actually be running queries against columns that are 100% NULL in large tables - heh heh, I have seen Siebel systems where there are literally dozens, if not hundreds, of b-tree indexes on 100% NULL columns. I have seen a case with 17 separate single-key b-tree indexes on 100% NULL columns ON A SINGLE TABLE!

(As to why b-tree indexes are used, Siebel as of yet uses only the RBO. Siebel has many queries running against such columns. Heck, depending on their configuration, they generate SQL to do joins against tables that are completely empty - frequently.)

So - this stuff really does happen in the real world. :-)

[slimdave]

Originally posted by paladin
If the query was:

select * from t
where object_type = 'hello';

instead of

select * from t
where object_type is NULL;

both the RBO and the CBO will in fact use the 1st index.

RBO would, CBO might -- if there was a histogram on object_type then CBO might FTS anyway, depending on the histogram, clustering factor, blah blah blah.