Re: Data Warehouse. Massive Join. HELP!

Hi Stuart,

I can't give you a very specific answer, only a direction to go into, because I never had access to a system with 8 processors: If you have 8 (!) processors, and are using cost based optimizer, you should be able to use parallel query, either by providing a degree of parallelism directly on the table or in the query. Oracle High Performance Tuning by Guy Harrisson covers this subject in detail.

Hth,

Sybrand Bakker, Oracle DBA

On Wed, 24 Feb 1999 20:19:38 GMT, stuco_at_mailcity.com wrote:

>Here's my situation:
>
>Data Warehouse - building a summary table based on a 7-table join (driving
>table is 2 million rows, other tables are about 500,000 rows each).
>
>To load this table takes 5 hours with a nice Rule-based query that has been
>tuned to use unique and range index scans. Thing is...IT TAKES 5 BLOODY
>HOURS!!
>
>So I analyzed all the tables and indexes, ran it through the Explain Plan and
>all my lovely index scans in the Cost-based mode have turned into full table
>scans with sort-merge joins.
>
>So I think, 'well maybe this might be faster'. ---Nope.
>
>This is the first of many tables that are being built from our data store and
>with the data is taking half a day to generate. We have an IBM SP/2 running
>AIX 4.2 with 8 processors and 2Gig of memory.
>
>What can I do to tune this thing??
>
>Thanks for your help.
>
>Stuart Cowen
>Paladin Consulting - Dallas
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Sybrand Bakker, Oracle DBA Received on Wed Feb 24 1999 - 16:00:53 CST