Re: Oracle partitioning

kachkar_at_my-deja.com wrote in

>Let us assume that I've created the ideal environment for the
>partitioning, again how
>effective is the partitioning ?

Very effective. It's simply being able to control the physical distribution of data. It allows you to specify what data (according to a pre-defined criteria) in a table is stored in which datafiles. When you access the table using this criteria, Oracle knows which datafiles to use and which to discard.

The usual example is partitioning according to date. E.g. an invoice table with several million of rows per month spanning the last 5 years. Partitioning the table according to month, will physically group invoices of the same month into the same datafiles. When you then need to process the invoices for a single month to determine the market trends, sales stats etc. for a month, Oracle only accesses the datafiles containing that month's data.

5 years x 12 month = 60 datafiles, assuming that each datafile is large enough to handle one month's invoices. If you query a single month's data, you cut down the amount of physical data to access to 1/60th of the total data volume in that table. That is a -significant- reduction. OK, I'm assuming that you're using full table scans here which is not always the case.

But nothing is as simple as that. What about indexing? What if you need to perform product analysis for a specific product for the last quarter? Or determine the annual sales for a specific department, branch or region? As the time period spans several months, the effectiveness of time partitioning diminishes.

So in the end it all depends on what you want to do, and what the nature of the data is. Requirements first, then select the technology to solve it! :-)

regards,
Billy Received on Thu Oct 28 1999 - 07:16:51 CDT