Index Usage with VIEWS
Date: 1996/05/20
Message-ID: <31A10EEE.5D12_at_cybercomm.net>#1/1
Hi Everyone:
I have a following situation in an Data Warehousing engagement:
I am dealing with 12 monthly tables each containing 'detail/raw' sales
data for a month. The table structures of these monthly table is as follows:
When I execute a aggregate query(see below) against the view, it does
a FULL TABLE SCAN as opposed to INDEX SCAN of each of the monthly table even
though the tables are indexed by aggregate column(c2):
SELECT MAX(c2) FROM v1;
We all know that when we do the following it will use index scan
because of index on c2 column:
SELECT MAX(c2) FROM table-month1;
Q1: Why is the access path different for a view than a table.
Q2: Is there a way to force the optimizer to choose 'index-scan' of the base
tables of the view.
Note: Optimizer ignores any hints at view level. It is also smart enough!! to
recognize a 'dummy where clause(where c2 = c2)' and therefore not use this as
basis of index-scan.
Any comments/suggestions would be appreciated.
| c1 | c2 | c3 | ........................| c15 |
tables have indexes on column c2 (representing revenue in $). We also have a
view 'V1' representing a yearly unified view of the monthly data:
------------------------------------------------
Each of these monthly table contain 1/2 Million rows. Each of these
CREATE VIEW v1 AS
SELECT * FROM table_month1
UNION
SELECT * FROM table_month2
UNION
.......
SELECT * FROM table_month12;
Most of the users will be executing adhoc queries against the view
rather than the monthly detail tables. Also most of the queries deal will
AGGREGATE's (EX: sum, max, min etc..)
Please Reply to "fschitto_at_cybercomm.net"
Thank You for your time
--
/\
/ \ /\
/~~~~\/~~\ /\
||||| / /\ / \/~~\
(o o) / /~~\ \ \
+------------------oOOo-(_)-oOOo---------------------------------+
| |
| Frank Schittone Barnegat, New Jersey |
| fschitto_at_cybercomm.net |
| "Do Something...LEAD, FOLLOW, or GET OUT OF THE WAY!" |
| |
+----------------------------------------------------------------+
Received on Mon May 20 1996 - 00:00:00 CEST