Re: partitioning

"Peter" <depend3_at_yahoo.com> wrote in
news:wtyv9.184779$8o4.29325_at_afrodite.telenet-ops.be:

> I know companies that never use CBO, just because statistics slow
> down performance if they aren't taken regularly. If only one table
> of a user has statistics taken from it, it grately reduces speed of
> all other tables queries under RBO. In general an oracle database
> can perform very well without statistics and CBO. The example I talk
> about is a system with tables less than 100000 rows some with
> queries joining tables on date basis. I saw a query improve from
> seven seconds to less than 1 or 2 secs just by partitioning the
> tables and the indexes, without statistics and CBO.

The ironic part is that in your example above, it's an excellent candidate for RBO: relatively small datasets so analyzes can be run periodically without affecting the online system.

The 'issues' (and I intend that in quotes) with RBO is when your datasets span in the giga and terabyte range, if not greater. Some RDBMS vendors allow one to output the statistics from one database to a file, which can then be imported to another database. The beauty here is that if you are mirroring your instances, you can run the statistics on your backup, and upload them into your primary.

I expect Oracle to mimic Sybase's behavior relatively soon -- they 'borrow' everything else, so why not? <g>

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com