0

So i define bitmap index for column_a. then i query

Select column_a, column_b 
from table_name 
where column_a = 'values';

But after i check the Explain plan, my query still do a table access full scan. but if i only do

Select column_a
from table_name 
where column_a = 'values';

it successfully use my defined index

1 | BITMAP CONVERSION TO ROWIDS

2 | BITMAP INDEX SINGLE VALUE

my question then, should i define all index on all columns that i want to query? even though some of the columns have high cardinality?

UrDailyCS
  • 17
  • 6

1 Answers1

0

Index should cover where clause columns along with table columns mentioned in select clause. In general you should at least cover where clause columns and depending on column data type,frequency of usage of query select clause may follow. Remember over indexed table can degrade database performance

Note: 
 1.If you defined index with all columns it will not that useful index considering index size,performance and maintenance
 2.B-tree indexes are most effective for high-cardinality data.

Oracle official documentation

devesh
  • 618
  • 6
  • 26
  • so if i want to select 10 columns, i should make index on 10 columns? – UrDailyCS Aug 29 '23 at 05:59
  • Not recommended .An index does not only take space up on disk. It occupies memory and memory contention is often the factor that determines query performance. In general, building an index on every column will occupy more space than then original data. (One exception would be a column that is relative wide and has relatively few values.) In addition, to satisfy many queries you may need one or more indexes plus the original data. Your page cache gets rather filled with data, which can increase the number of cache misses, which in turn incurs more overhead. – devesh Aug 29 '23 at 13:55
  • but you said "Index should cover..... table columns mentioned in select clause", what did that mean? how can i make my query use my index – UrDailyCS Aug 30 '23 at 15:36