Re: Index Across Tables

You could go one step further, and create a single bitmap index on the table SALES, defined on the columns

    (customer.age_band, geography.state, product.product_class)

So the index contains bitmaps covering rowid ranges on the Sales tables, but values from the other three tables.

Sounds like a great idea - but the penalty for updating the fact table, or any of the dimension tables in a way that changes the bitmaps is huge. (But we don't update the indexed columns on tables with bitmap indexes anyway). Moreover, when you create a bitmap join index, Oracle creates a couple of global temporary tables in the SYS schema, and forgets to drop one of the when you drop the index - so you gradually accumulate garbage in the SYS schema if you follow a policy of

    drop index
    apply DML to fact table
    rebuild index

I'm still trying to decide why a bitmap join index might, or might not, be a better idea than an index-based bitmap function.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Mark Townsend wrote in message ...


>

>Do this with one or more other columns in the dimensional tables (State in

>Geography, perhaps, Product_Class in Product, perhaps), and determination

of


>the results set for the query such as

>

>SELECT SUM(Sales.Dollar_Amount)

>FROM Sales, Customer, Geography, Product

>WHERE <..standard join conditions...>

>  AND Customer.Age_band = 6

>  AND Geogrpahy.State ='CA'

>  AND Product.Product_Class = '2 Seater Convertibles'

>

>become a simple XOR operation on the three relevant bit maps. Very very

>fast, and not too costly, as bit maps don't take up too much space

>(relatively).

>