Join_index in all_indexes table 1

[cmmrfrds]

There is a column in all_indexes called join_index. I have looked up the meaning of this but I still don't understand what it means.
I noticed that all our indexes say NO in this column. Can somebody explain the implications of this column. Thank you.

select
ind.table_name,
ind.index_name,
uniqueness,
distinct_keys,
num_rows,
join_index

from sys.all_indexes ind
where ind.owner = 'CLARITY'

 

[SantaMufasa]

Cmmrfrds,

There is probably good reason that this feature (that Oracle introduced in 9i) doesn't show up in your application's data dictionary with JOIN_INDEX = 'YES'...Its typical use if for applications where the data are static and the cardinality of the values in the bitmapped column is "low" (e.g., < 300). (The overhead for updating a table which uses "Bitmap join indexes" would be unacceptable.)

Basically, "Bitmap join indexes" is an index that contains the "results" of a query.

Here is a link to a more complete explanation and example of bitmap join indexes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cmmrfrds]

Thank you for the information. Since ours is a datewarehouse, I will read up and see if there are some applicable situations.

 

[SantaMufasa]

Yes, cmmrfrds, Data Warehouse applications are usually the best venues for bitmap join indexes. If you end up building some, please post back here your findings about how well they work for you.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”