Oracle 7 partition view vs star schema

From: <zuot_at_my-deja.com>
Date: Wed, 01 Sep 1999 19:50:48 GMT
Message-ID: <7qk02j$3b9$1@nnrp1.deja.com>

Does anyone have comments on the performance of this data warehouse design:
star schema with the fact table on partition view (oracle 7.3)?

The reason we ask the above question is that we know that the fast access on partition view tables relies on parallel scan of the partitioned tables which support the view.
Whereas the star-schema required index on the partitioned table seems to
prevent parallel scan to happen which dramatically slows down the query speed.

For example,
we have a query doing a join between a dimension table and a fact table (partition view),
the following is the essential part of the explain plan:

                VIEW  SUBSCR_BILLING (the partition view)
PARALLEL_COMBINED_WITH_PARENT
                  UNION-ALL PARTITION  PARALLEL_COMBINED_WITH_PARENT

                    FILTER   PARALLEL_COMBINED_WITH_PARENT
                      TABLE ACCESS BY ROWID SBL_00 (partitioned table)
PARALLEL_COMBINED_WITH_PARENT
                        INDEX FULL SCAN SBL_00_PK_IDX

PARALLEL_COMBINED_WITH_PARENT

                    FILTER   PARALLEL_COMBINED_WITH_PARENT
                      TABLE ACCESS BY ROWID SBL_01 (partitioned table)
PARALLEL_COMBINED_WITH_PARENT
                        INDEX FULL SCAN SBL_01_PK_IDX
PARALLEL_COMBINED_WITH_PAR


						.


						.


						.

We notice that processing the query is extremely slow (and we figured that this is
because there is no table scan on the partitioned tables).

Avoiding index (by adding +0 on join), we got

                VIEW  SUBSCR_BILLING PARALLEL_TO_SERIAL
                  UNION-ALL PARTITION  PARALLEL_COMBINED_WITH_PARENT

                    FILTER   PARALLEL_COMBINED_WITH_PARENT
                      TABLE ACCESS FULL SBL_00

PARALLEL_COMBINED_WITH_PARENT

                    FILTER   PARALLEL_COMBINED_WITH_PARENT
                      TABLE ACCESS FULL SBL_01

PARALLEL_COMBINED_WITH_PARENT and there is the table scan and running the query is much, much faster.

I guess that we are certainly not the only site using Oracle 7.3 for data warehouse. Have anyone experience similar problem before? Or maybe my understanding of among partition view, parallel processing, table scan, and index is not accurate?

Your comments are appreciated.

Tao Zuo

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 14:50:48 CDT