Re: Index Across Tables

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.

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 - 15:45:56 CDT