Partitioning tables

[dedale]

Hello,

I'm creating a biologic database. There only few entities in which must be inserted nearly 50Gb of data. That 's why i want to use partitioned tables.
My questions are :
how many partitions must i create ?
is there a limit for the number of partitions ?
is there a limit size for an optimum use of the partition ?

Thank you in advance for your help.
David

[adewri]

First of all you need to know the type of partitioning methods available. The based on the methods you need to decide which method would suit your situation best.

Read this thoroughly
http://otn.oracle.com/docs/products/...titi.htm#20590

HTH

[BJE_DBA]

We had 24 partitions for a 2-year window of monthly telecom data.
We needed to go to daily loads. One of many solutions I suggested was to simply have daily partitions, its not even 800 partitions.
Dont worry about the number of partitions your not likely to hit the limit.
I used range partitioning for my date key. Oracle prefers more partitions since the granularity is one partition to a parallel process ( has Oracle improved that in 9.2 ? ). So the more the merrier. Local indexes ease administration.
Load into standalone flat table and 'exchange partition' to swap data/index/stats with a partition which results in no data movement, only data dictionary pointers. Very fast and you never have to rebuild the other partitions indexes so it 'scales'.

[slimdave]

The caveat against having too many partitions is that if you have queries that do not utilize partition elimination, it can require much more work.

Comparing a non-partitioned table to a table with 600 partitions, a query might have to touch 600 index partitions to find a row that exists in only one partition. That could be a Bad Thing.

[marist89]

Originally posted by slimdave
Comparing a non-partitioned table to a table with 600 partitions, a query might have to touch 600 index partitions to find a row that exists in only one partition. That could be a Bad Thing.

There's this new thing Oracle has called an "Index". Is it more work to touch 600 partitions than do a FTS on one table that has the same number of rows? Maybe. In comparison, though, I wouldn't say it is "much" more...

[slimdave]

Indexing? Really? Fantastic what they think of nowadays.

Of course, comparing a FTS on a non-partitioned table to an index scan on a partitioned table is rather disingenuous and misleading. Why would you not index the column just because the table is not partitioned?

If you would index a partitioned table on particular columns, then you would index a non-partitioned equivalent on the same, wouldn't you? The difference would be that a query predicated on the indexed column would only have to touch one index, instead of 600 seperate partitions of an index.

The point i am making is that you have to consider both the positives and the negatives of the feature befoe jumping straight into it. Partitioning can improve parallelism sure, but parallelism has a downside also. Who parallelises on an OLTP application where individual queries return very small numbers of rows? Maybe only people who believe that non-partitioned tables do not need indexes

[abhaysk]

I go with slimdave...

Non Partitioned Tables will be more helpful, if properly planned to build the index on key columns in right order.

And the queries optimised for APPL usage, will fetch, rather there will be less overhead for the retrivel of data compared to the Partitioned Tables.

We have Non Partioned Tables holding around 30GB data..and we dont face any problems..And we have such N number of tables.



Abhay.

[slimdave]

I'm not saying the either partitioned or non-partitioned is better, just that they suit different situations. Think about how data gets in the table, how it's modified once it's in there, how it's selected, how it's deleted.

[dedale]

Hello,

thank you for all your answers and I have well understood that i have to think about the use of the data before to decide whether or not i'll use partitioned tables. However i have another constraint to tell you : nearly 1 Gb will be added to the database each week and the size of the data added will grow quickly?

In this case, is it not better to use partitionned table than index on non partitionned table ?

Bye.
David

[adewri]

See having a partitioned table doesnot always guarantee that the performance will increase. You need to test and see if performance improves or not and also you need to modify your queries so that it fetches the correct partitions.