Re: Data-Warehouse dilemmas

In article <72ot67$248$1_at_nnrp1.dejanews.com>,   gilhirsch_at_my-dejanews.com wrote:
> 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
>

   Gil,

  If the indexed_field1 sumarized in the query WAS NOT the columns used to do the partition (the partition column), the partition view has no effect. The partition views in Oracle 7.3 are very useful in select containing where clause in the partition columns, due the fact Oracle will just search one table or index, instead of 50, in your case.

   When you search using the partition column to look just one table, you can look the word FILTER above each table excluded in explain plain.

   Best regards
   Luis Santos
   lsantos_at_pobox.com

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Dec 04 1998 - 15:16:47 CST