Re: Crosstab performance

Comments embedded.
On Mar 11, 9:47 am, "aravind.ka..._at_gmail.com" <aravind.ka..._at_gmail.com> wrote:
> Hi,
>
> I am creating a crosstab view which presents the information at the
> row level and produces it in a columnar format. I am creating a view
> that uses dynamic sql to create the case statements for each of these
> columns. The input table can have 120M rows which need to be split
> into 200K rows with 600 columns each. My question is regarding the
> runtime performance of such a view.

Much of that depends upon your server configuration; I expect running such a view on a Cray would be swift, yet trying that on a VIC20 would result in dismal performance. Of course you're running on something somewhere in between those two extremes, and it's too bad you chose to not post the specifics of that server (CPU count, installed memory, operating system, other applications running concurrently). And you conveniently forgot to post which Oracle release (all four or five numbers) you're using. As it is answering that question is nothing better than guesswork.

>
> This is a datawarehouse application and the resulting view will be
> used in queries and reports.

And that's admirable, really.

> Will it be a performance hog creating and
> running 600 case statements on the fly everytime the report is run?
>

Who could say? Dynamic SQL has its issues, and constructing a view using dynamic SQL isn't something I'd choose to do, especially generating 600 CASE statements. And do you really have enough RAM installed to create such a view?

> The other option that I have is to create a table with 600 columns and
> load the table with the values and then use that result table in the
> reports.

Possibly a better option, as you can index the table for more efficient access paths. It's a data warehouse, by your admission, thus the loading/refresh process is a time-consuming affair run afterhours.   I think I would rather have a table or a materialized view. Other opinions may differ.

>
> I appreciate your response.
> Arvind.

David Fitzjarrell Received on Tue Mar 11 2008 - 10:07:01 CDT