datawarehouse explain plan question

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Mon, 17 May 1999 18:30:55 -0400
Message-ID: <3740989F.4B5A86A1@bigfoot.com>

I am going through an article by Loney, Arondoff, and Sonawalla called "When you Wish Upon a Star" about the Star hint for datawarehouse queries.
They start with a discussion of how a normal, not star optimized explain plan
would handle a query involving three dimension tables and 1 fact table. I have
a question about a comment they make about the non-optimized plan. This example
will make more sense to those familiar with a datawarehouse star schema, but I'm sure
a lot of people can answer to it regardless. This is a long post, and can be summed up very simply - If I have a composite index of three columns - A,B, & C - and I do a query only on column A, why is a RANGE scan neccessary.

You can skip the rest if the answer is pretty obvious - but I presented the entire
example from the article in case.

The ddl is as follows:
The dimension tables:

create table product
(product_ID NUMBER,

Product_Name VARCHAR2(20),
constraint product_pk primary key (product_id));

create table period
(period_ID NUMBER,

period_Name VARCHAR2(20),
constraint Period_pk primary_key(Period_id));

create table customer
(customer_ID NUMBER,

customer_name VARCHAR2(20),
constraint CUSTOMER_PK primary key (Customer_ID));

and the fact table is
CREATE TABLE ORDERS
(product_ID NUMBER,

Period_ID NUMBER,
Customer_ID NUMBER,
Order_Amount NUMBER
constraint orders_pk
primary key (Product_ID, Period_ID, Customer_ID), constraint ORDERS_PRODUCT_FK foreign key (Product_ID) references PRODUCT(Product_ID),
constraint ORDERS_PERIOD_FK foreign key (Period_ID) references PERIOD(Period_ID),
constraint ORDERS_CUSTOMER_FK foreign key (Customer_ID) references CUSTOMER(Customer_ID));

So then they execute the following statement

Select PRODUCT.Product_name,

          PERIOD.Period_name,
          CUSTOMER.Customer_Name,
          ORDERS.Order_Amount

from OrdeRS,PERIOD,CUSTOMER,PRODUCT
where PRODUCT.PRoduct_name = 'WIDGET'
and PERIOD.Period_name = 'Last 3 Months' and CUSTOMER.Customer_Name = 'MAGELLAN'

and ORDERS.Period_ID = Period.ID
and ORDERS.Customer_ID = CUSTOMER.Customer_ID
and ORDERS.Product_ID = PRODUCT.Product_ID

So essentially, you have primary keys on the dimesion tables and a composite
primary key on the fact table consisting of foreign keys to those primary keys on the
dimesion tables. So when this query is executed, non-star style, the explain plan
is as follows -

NESTED LOOPS
NESTED LOOPS

        NESTED LOOPS
            TABLES ACCESS FULL PRODUCT
                TABLE ACCESS BY ROWID ORDERS
                INDEX RANGE SCAN ORDERS_PK
             TABLE ACCESS BY ROWID CUSTOMER
                INDEX UNIQUE SCAN CUSTOMER_PK
            TABLE ACCESS BY ROWID PERIOD
                INDEX UNIQUE SCAN PERIOD_PK.

The article goes on to point out that this query can be killed by joining the product
to the order table first (fact tables are generally huge), so that after some jimmying,
you can get the dimension tables joined first to speed things up.

But my question is about that RANGE scan on orders_pk. The article states " a range scan was performed on ORDERS_PK, because only
the first column of that index was used while joining ORDERS to PRODUCT"

My understanding was that as long as you referenced columns in a left-wise fashion
on a composite index, you could still get a UNIQUE scan.

No?

Received on Mon May 17 1999 - 17:30:55 CDT