Re: Oracle Table Spanning

"Anton Dischner" <nospam_at_nospam.org> wrote in message news:260120041442027686%nospam_at_nospam.org...

> a date column is very helpful.
>
> statemants like:
>
> select ... where mydate between '1.1.2004' and '31.1.2004'
>
> should perform very well.
>
> for fast handling, creating a smaller extract of your table and using
> this is often a faster solution.
>
> With other words: Use partitioning only where absolut necessary.
> I strongly believe you do not have to use partitioning.

This is good advice, except for the cautioning against partitioning. With the later versions of Oracle, you'll find an easy way to solve contention problems with 3rd-party software (who don't let you change code) is precisely to hash-partition tables and indexes.

As an aside: if there is one design decision that drives me nuts is the widespread "let's put start and end date columns on the time-series tables and make end_date = NULL the current period".

Talk about NOT HAVING a CLUE!

A column with a NULL value is a non-index, FTS certificate for Oracle. As soon as the "current period" is defined by a NULL on end_date, one is basically condemned to FTS or at the very least range scan to find current period! Probably the most common query in a time series...

Yet I've seen this design "technique" used more times than I care to remember. How hard can it be to define a HIGHDATE constant that will be used as the "open date" value for end_date and have a PL/SQL function that returns the constant to be used in query predicates and column populate?

What, 31st December 3999 is not far ahead in the future to function as an open date in your designs? Don't flatter yourselves...

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nspam