 |
 |
Archives of the TeradataForum
Message Posted: Thu, 18 Dec 2003 @ 14:42:34 GMT
| Subj: | | Re: Partitioned Primary Index |
| |
| From: | | Dieter Noeth |
Craig Ferry wrote:
| | We are thinking about using Partitioned Primary Indexes on some of our bigger tables. Does anyone have any feedback good or bad
about using PPIs (i.e. performance gains/losses, access problems, etc)? | |
It's usually a perfomance boost if you use a current patch level...
| | Does something like the following make sense if I want to partition in monthly chunks. Our tables are loaded each month with
an additional month of information. Any thoughts/suggestions? | |
| | Would something like the following make sense for yearly/monthly partition, or is there a better way to do this? I'm not sure if
the parser would know where to find the data this way. I tried to do a partition by year then by month but didn't have any luck. | |
> CREATE TABLE my_db.my_table ,NO FALLBACK ,
> NO BEFORE JOURNAL,
> NO AFTER JOURNAL
> (date_invoice DATE FORMAT 'YY/MM/DD' NOT NULL,
> join_no INTEGER NOT NULL)
> PRIMARY INDEX (date_invoice, join_no)
> PARTITION BY (extract(year from date_invoice) * 100 + extract(month
> >from date_invoice))
Unless you provide a similar calculation in your where-cluase, the optimizer will probably not be able to determine which partition
to use. You should use a RANGE_N:
PRIMARY INDEX (date_invoice, join_no)
PARTITION BY RANGE_N( date_invoice
between date '2000-01-01' and date '2004-12-31' each interval '1' month)
The range is not fixed, you can extend the range of dates. Check the manuals for lots of details.
Dietetr
| |
