Re: Oracle 7 partition view vs star schema

There isn't really enough detail to give a complete evaluation of your problem but:

1. The plan of the join shows you are doing INDEX FULL SCAN on the tables
2. I infer from the PARALLEL_WITH_PARENT (in particular on the VIEW line) that the rest of the plan is a parallel scan of the dimension table with a nested loop index into the partition view. (I also suspect that you have only used one dimension table, and that the 'star query' is not actually a star).

The major cost of the 'star' query then is that every single entry in each of the the FACT indexes is being accessed for every single row dimensional join.

It is possible to combine star queries and partition views in 7.3, but I have only managed to make them run serially. Even so, with appropriate data distributions serial Stars can run more efficiently than parallel queries using other paths.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

zuot_at_my-deja.com wrote in message <7qk02j$3b9$1_at_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.
Received on Mon Sep 06 1999 - 04:20:45 CDT