Re: Data Warehouse and DB Block Buffers

In article <6kuqb6$g2e$1_at_nnrp1.dejanews.com>,   rad_at_earthling.net wrote:
>
> I'm a UNIX systems guy, not a DBA. That said..
>
> Does a data warehouse take much advantage of db block buffers?

YES!!! OLAP is a lot diffent than OLTP. OLAP normally uses much more data at a time to sum, average, calculate change differences, and other time dependant calculations. The more data it can bring in at a time the better off you are. And if you have not yet created the database or you can recreate it, make the db blocksize at least 16k or 32k. The more data brought in at once the better.

> If it is
> mostly doing full tablescans that are not repetitive from one job to the
next,
> will Oracle be reusing any data in the db block buffers? If a DW does
utilize
> db buffers, how so?
>

Sometimes yes and sometimes no, it depends on how the database is designed. One place where it really helps is by keeping the support tables in memory (table caching helps with this too). Where you get the big bang for your buck here is if they do a lot of calculating on the fly, it really drops your i/o.

> Also, when loading data into a single table on a DW, will the data be placed
> contigously physically? That is, will full tablescans take advantage of
read
> ahead cache on a high end disk array?
>

If you are loading it into a "clean" area, yes. Something else that makes a big difference is to index the crap out of the database. It will slow down the loading, but it will get rid of theose full table scans.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Jun 01 1998 - 15:02:37 CDT