Re: Tablespace/Datafile Layout for Datawarehouse

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 10 Jun 2007 15:29:33 -0500
Message-ID: <miYai.12089$4Y.11951@newssvr19.news.prodigy.net>

"Greg Rahn" <greg_at_structureddata.org> wrote in message news:1181499105.021017.133990_at_d30g2000prg.googlegroups.com...
> 1. Yes. A (smallfile) datafile can have approx 4 million blocks.
> Personally I use 8k blocks for everything. It is the middle of the
> road size and doesn't put you in an edge condition. If you want
> larger IOs in a DW, adjust the multi block read accordingly.
>
> 2. From a performance view, having fewer files lowers the file
> descriptor requirement, but it may impact the recovery strategy - now
> a single file recovery will take longer due to the increased size.
>
> --
> Regards,
>
> Greg Rahn
> http://structureddata.org
>
>

This is a datawarehouse so I'm thinking 16k block size. If there are tables that don't need the large I/O they can always be put in a separate tablespace with an 8k block size.

As far as performance over recovery ... performance of the database here has taken a last place over the ease of management for the DBA at least in my opinion. If there is anything that can help the performance of the database but would cause the DBA more work it never happens. Received on Sun Jun 10 2007 - 15:29:33 CDT