Re: How to tune a huge SELECT ?

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