Re: Index Across Tables

All perfectly true, but that still doesn't make the bitmap join index an index 'across tables'. The index is built very much on the Sales table, and the Sales table alone. The start- and stop-rowids it contains, for example, are those for the sales table. For every row in the Sales table alone is there a bit representing truth or falseness.

So whilst it is true that it happens to be an index of the truth or falseness of each Sales table record for some condition held in another table to which it can be joined, it still remains an index on one table.

Regards
HJR "Mark Townsend" <markbtownsend_at_attbi.com> wrote in message news:B94CA513.38AE1%markbtownsend_at_attbi.com...
> in article 3d275053$0$232$cc9e4d1f_at_news.dial.pipex.com, Niall Litchfield
at
> niall.litchfield_at_dial.pipex.com wrote on 7/6/02 1:17 PM:
>
> >> And the new bit mapped
> >> join indexes in Oracle9i rock.
> >
> > care to expand. I haven't looked so this is a purely lazy question.
>
> Imagine a large fact table, such as Sales. Queries on Sales are typically
> filtered by one or more dimensional values - say the age band for a given
> customer. This age band is in the dimension table Customer.
>
> In Oracle9i, you can create an index such as
>
> CREATE BITMAP INDEX cust_age_band
> ON Sales(Customer.Age_Band)
> FROM Sales, Customer
> WHERE Sales.Customer_Id = Customer.Customer_Id
>
> For each value of Age_Band in Customer, you will build a bit mapped index.
> However, the bit maps themselves will refer to the rows in the Sales
table,
> NOT to the rows in the Customer table. This means the optimizer can
> immediately identify the rows in the Sales table that qualify for a
specific
> age band, without having to actually visit the customer table.
>
> 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).
>
>
Received on Sat Jul 06 2002 - 19:03:10 CDT