Re: cluster index on a table

> As Kevin said, there's no guarantee that tuples will be
> read back
> in the order you inserted them.

Ok, didn't know that

> A better option you might consider is to use a separate
> table for the
> re-ordered tuples.
> You could even do this using partitions

Problem is I'm already using partions: I'm partitioning on a monthly basis. I want to avoid partitioning on a daily basis: I have 200 tables partitioned by month, 2 years of data. Partition them by day would mean 700*200 tables: what kind of performance impacts would it mean?

Does this other option make sense:

partition only "last month" by day; older months by month.
Day by day the tables of the current month gets clustered (say at 1.00AM next day).
Then, every 1st of the month, create a new table as

- create table mytable as select * from <parent_table> where time <in last month> (this gets all the data of last month ordered in the "almost" correct order, because all the single tables were clustered)
- alter mytable add constraint "time in last month"
- alter mytable inherit <parent_table>

and then drop last month's tables.

Is this even doable? I mean: between

- alter mytable inherit <parent_table>
- drop last month's tables.

more than one table with the same constraint would inherit from the same table: that's fine unless someone can see the "change" before the "drop tables" part, but I guess this shouldn't be a problem if I use the serializable transaction level.

This way I could cluster the tables (not perfectly, since I would cluster data day by day, but it's enough) and still have few tables, say (31 for current month + 23 for the past 23 months) * 200.