Re: How to tune a huge SELECT ?

Billy Verreynne wrote:

> Christian Svensson wrote:
>
> > Since these SELECT get a lot of data, we are talking about approx 100
> > million rows. It takes approx 7-8 hours for Cognos to select this data
> > into a Cognos tempfile where it later use this to build the cubes.
>
> Pulling a 100 million rows across is, pardon me for being very blunt Chris,
> UTTER FRIGGEN STUPIDITY!
>
> Why have a warehouse in the first place? Why have a database engine as
> capable as Oracle in the first place? Why not simply dump that data on a PC
> in some file format and use it directly there?
>
> The purpose of having the data in Oracle is to use Oracle's abilities at
> processing the data. Retrieving a 100 million rows from Oracle is a lot
> more expensive than reading a 100 million records from a flat file. And
> guess what - reading it from a local flat file (using a file stream to
> block read) will even be _faster_ than having to pull it from Oracle across
> the network.
>
> The reason I'm foaming at the mouth (not a pretty sight I know ;-), use the
> tools (in this case Oracle and Cognos) correctly!
>
> This means _not_ pulling across a 100 million rows, but only that which is
> applicable. Perform as much processing as possible on the database server
> (this is after all why we are using db engines in the first place). Have a
> look at what Oracle offers ito data warehousing.
>
> Next, disable Cognos from using its own functions. Cognos has a nice suite
> of functions. But consider:
> SELECT region, city, NICE_FUNCTION( sales ) FROM the_world
>
> Oracle does not know what NICE_FUNCTION does - it is not a ANSI SQL
> function. So Cognos changes the SQL to the following:
> SELECT region, city, sales FROM the_world
>
> Cognos pulls _all_ the data from the table across. And then it applies the
> function NICE_FUNCTION(sales) and it performs the aggregation and grouping
> and sorting. On a client PC.
>
> It works when dealing with mickey mouse data sets. It does not work when
> dealing with real data warehouses.
>
> > What I wonder is what Oracle setting/tuning can you make when you want
> > to get this amount of data from the database ?
>
> Nothing. The only thing that needs tuning is the attitude and preconceptions
> of people using large volumes of data. Nothing personal Chris, simply that
> I have been this exact same problem on more than one occassion with users
> that refuse to understand the impact of attempting to process large volumes
> of data on the client side and not the server side.
>
> --
> Billy

A point I wish I had made. Cognos is a reporting tool. No report EVER required 100M rows of data be transferred anywhere. 100M rows is a dump file. Do your processing on the server and only transfer the result set.

Daniel Morgan Received on Fri Sep 13 2002 - 11:08:29 CDT