Printable View
I have a below query:
SELECT * FROM BUSINESS_PL
WHERE ADDRESS IS NULL;
from the above table, in the explain plan, it does a full table scan. but the cost is very low b/c the table had less than 100 records. Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???
You can get nulls indexed in some circumstances, such as ...
- Using a bitmap index -- but don't do it on an OLTP systems
- Use a composite index, where the other column is not null when the address column is null
- Use a function-based index ... in thiscase you can choose to index only null values.
The second option is the easiest.
> Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???
And the answer is, yes. Note, however, there will be no index entries for the NULL values. So an index on a 100% NULL column will be empty.
Example follows:
===
drop table t;
create table t as select rownum id, all_objects.* from all_objects where rownum < 12000;
update t set object_type = NULL;
create index t_idx1 on t(object_type);
analyze table t compute statistics for table for all indexes for all indexed columns;
--- Access dba_indexes.
--- Identify indexes with zero rows.
select INDEX_NAME, INDEX_TYPE, TABLE_NAME,
LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY
NUM_ROWS, BUFFER_POOL
from dba_indexes
where owner = 'your ID here'
and table_name = 'T'
and num_rows = 0
order by table_name desc;
===
Result is:
INDEX_NAME INDEX_TYPE TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY NUM_ROWS BUFFER_POOL
T_IDX1 NORMAL T 0 0 0 0 DEFAULT
How about the three methods i just gave for getting those nulls included in an index?Quote:
Originally posted by paladin
> Any way, the question is "can we put the index on the ADDRESS column"??? even the column is NULL???
And the answer is, yes. Note, however, there will be no index entries for the NULL values. So an index on a 100% NULL column will be empty.
Two of your methods should indeed work, the 1st and 3rd. Of course, the 1st and 3rd will work only with the CBO and not the RBO.
As for the 2nd, it won't work with the query >>as specified<<. For example, if I add a composite index (t_idx2) as:
===
drop table t;
create table t as select rownum id, all_objects.* from all_objects where rownum < 1200;
update t set object_type = NULL;
create index t_idx1 on t(object_type);
create index t_idx2 on t(id,object_type);
analyze table t compute statistics for table for all indexes for all indexed columns;
select * from t
where object_type is NULL;
===
It still runs a table scan, and doesn't use the composite index. Of course, if you modify the query like so:
===
select * from t
where id = 100 and
object_type is NULL;
===
Then of course it will do a range scan on index t_idx2, thus using the composite index. But then the 2nd argument to the where clause is redundant if 100% of the column is null, which is what the original poster stated.
I don't think a composite index will help if the only value in the where clause is the 100% NULL column. If you can make it work, I would be interested in seeing the example. Thanks.
Your test is badly formed because ...
- the optimizer doesn't know that the "id" column is not null.
- placing the object_type second in the list of columns in the composite index reduces the chances of it being used
- The table is so small that the optimizer might choose to use an FTS anyway
- Since all the values of object_type are null, it's more efficient with this data set to FTS anyway
Code:SQL> create table my_table as select * from dba_objects;
Table created.
SQL> update my_table set object_type = null
2 where rownum < 11;
10 rows updated.
SQL> desc my_table
Name Null? Type
----------------------------------------- -------- --------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> alter table my_table modify (owner not null);
Table altered.
SQL> create index my_index on my_table (object_type,owner);
Index created.
SQL> analyze table my_table compute statistics;
Table analyzed.
SQL> set autotrace traceonly explain
SQL> select * from my_table where object_type is null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=10 Bytes=860)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MY_TABLE' (Cost=3 Card=1
0 Bytes=860)
2 1 INDEX (RANGE SCAN) OF 'MY_INDEX' (NON-UNIQUE) (Cost=2 Ca
rd=10)
Quote:
Originally posted by paladin
As for the 2nd, it won't work with the query >>as specified<<.
...
I don't think a composite index will help if the only value in the where clause is the 100% NULL column. If you can make it work, I would be interested in seeing the example.
Your example was totaly nonadequate.Code:SQL> CREATE TABLE t AS SELECT ROWNUM id, ALL_OBJECTS.*
2 FROM ALL_OBJECTS;
Table created.
SQL> UPDATE t SET id = NULL WHERE id =1;
1 row updated.
SQL> ALTER TABLE t MODIFY object_type NOT NULL;
Table altered.
SQL> CREATE INDEX t_idx1 ON t(id,object_type);
Index created.
SQL> ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL indexed
Table analyzed.
SQL> set autotrace on explain
SQL> SELECT owner, object_name, object_type FROM t
2 WHERE id IS NULL;
OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------- -------------
SYS /1001a851_ConstantDefImpl JAVA CLASS
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=47)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=
47)
2 1 INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
=1)
1. You neglected an important condition that slimdave mentioned: the other column in a composite index must be non-null. This must be known to the optimizer either by the definition of the collumn in the dictionary or by the predicate in a query.
2. You have update all of the rows to contain nulls in the indexed columns! So how dumb should the optimizer be to choose that index when it knows it will have to scan an entire index (plus visit all the rows in the table)?
Ups! I see I was too late with my answer...
slimdave,
You said:
> # the optimizer doesn't know that the "id" column is not null.
># placing the object_type second in the list of columns in the composite index reduces the chances of it being used
># The table is so small that the optimizer might choose to use an FTS anyway
># Since all the values of object_type are null, it's more efficient with this data set to FTS anyway
Addressing these issues, here is the SQL:
drop table t;
create table t as select rownum id, all_objects.* from all_objects where rownum < 12000;
update t set object_type = NULL;
alter table t modify (id not null);
create index t_idx1 on t(object_type);
create index t_idx2 on t(object_type,id);
(1) Here the optimizer does know that 'id' is not null.
(2) I placed 'object_type' first in the composite index.
(3) The table has 12,000 rows - not so small that a FTS should be preferred.
(4) Of course - if all the values are one value, a FTS should be preferred. But that was how the original poster specified the situation - a 100% NULL column.
Results:
(a) Using the RBO:
select * from t where object_type is NULL;
SELECT STATEMENT Optimizer=RULE
TABLE ACCESS (FULL) OF T
(b) Using the CBO:
select /*+ CHOOSE */ * from t where object_type is NULL;
SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=7 Card=11999 Bytes=1067911)
TABLE ACCESS (FULL) OF T (Cost=7 Card=11999 Bytes=1067911)
So - in conclusion - even with a composite index, if one key (1st or 2nd) is 100% NULL, and that is the only value specified in the where clause, then the index is not used. Of course, that is after all what we would expect the optimizer to do -- if a column is 100% NULL, and the only parameter in the where clause references that column, then what use would an indexed lookup possibly be? Answer - of no use at all, of course.
Jurij Modic said:
Your example was totaly nonadequate.
1. You neglected an important condition that slimdave mentioned: the other column in a composite index must be non-null. This must be known to the optimizer either by the definition of the collumn in the dictionary or by the predicate in a query.
2. You have update all of the rows to contain nulls in the indexed columns! So how dumb should the optimizer be to choose that index when it knows it will have to scan an entire index (plus visit all the rows in the table)?
My response:
For #1 - you are correct. I fixed that by doing alter table to ensure the optimizer knows that one of the 2 key values is not NULL.
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.Quote:
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.
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.
DING!!
Round 2
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. :-)
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.Quote:
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.