|
-
> 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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|