Data-Warehouse dilemmas

Hi,

  I'm currently doing a little research work to decide on which of oracle's versions is best for a data-warehouse. The versions I'm considering are: Oracle 7.3.4 Workgroup server (Cheap)
Oracle 8.0.5 Enterprise Edition + Partition option (VERY expensive)

My first approach was to try the oracle8 partitions, which proved to be fast and easy on maintenance, but also extremely expensive, since the Partition Option is only available with the Enterprise Edition.

Trying to save a little money for customers, I tried a Partition View on oracle7 and got some really BAD performance when using aggregation functions (max, min...). I was using the following settings:
- 50 tables with over a million records each

• same structure and indexes
• PARTITION_VIEW_ENABLED (even though I wasn't trying to eliminate partitions)
• constraints
• analyzed tables & indexes

The 'warehouse_view' was defined as:
create or replace view warehouse_view as   select * from table1 union all
  select * from table2 union all
  ...
  select * from tableN;

(no where clauses)

Explain plan for 'select max(indexed_field1) from warehouse_view' was: SORT AGGREGATE
  VIEW WAREHOUSE_VIEW
    UNION-ALL PARTITION

      INDEX FULL SCAN TABLE1_FIELD1
      INDEX FULL SCAN TABLE2_FIELD1
      ...
      INDEX FULL SCAN TABLEn_FIELD1

This query took less than a couple of secs on oracle8, and an amazing 2.5 minutes on oracle7.

Both the oracle7 and oracle8 versions were checked on the same NT machine, with default configuration.

Questions:
1. How can I improve performance on aggregated queries on oracle7

   (besides setting the SORT_AREA_SIZE, which is already set to 50 Megs). 2. Will adding the parallel query option (only available for oracle7's

   Enterprise Edition) make the difference?

Some other Data-Warehouse questions: Currently, I'm filling my daily tables via SQL*Loader (direct path), and when the day is out, I add constraints, indexes, analyze, and add them to the warehouse_view. 3. I'd love to hear about other ways of doing this (any warehouse management schemes that work will do). 4. I also need to add a view/table which will allow me to follow up on latest data (which is not in the default warehouse_view). Any known ways to get the last X records from a non-indexed LARGE table will do.

Any other data-warehouse related suggestions are more than welcome.

Thanx,
  Gil.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Nov 16 1998 - 04:01:43 CST