Re: Data-Warehouse dilemmas

From: Steve Mitchell <steve_at_mitchells.demon.co.uk>
Date: Mon, 07 Dec 1998 22:52:51 +0000
Message-ID: <366C5C42.6C2F6E90@mitchells.demon.co.uk>

There is also a bug in the earlier version of Oracle 7.3.4 that can prevent partition elimination taking place when a query is parallised. Patch to 7.3.4.2.0 and partition views will work as expected.

Steve

lsantos_at_pobox.com wrote:

> 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 Mon Dec 07 1998 - 16:52:51 CST