Re:Partitioning Questions

From: <dgoulet_at_vicr.com>
Date: Tue, 6 Jun 2000 10:09:55 -0400
Message-Id: <10520.107872@fatcity.com>

--IMA.Boundary.986003069

Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part

Terry,

    I have found that partitioning has a very large impact on all parts of database operation where very large tables are concerned. In an earlier post reply that I made (on autonumbering) I took that 100 million+ row table and broke it up with partitioning into types of ATE equipment's. To make this transparent to my end users I used views (which they really like and were used to). The query by itself eliminated 90% of the data in the total table by looking at only one of 10 partitions. End result, queries that took all day to resolve now run in less than 10 minutes. Why, 1st there is the elimination of data that they did not need. Second the partitioned indexes have fewer leaf blocks to search. I've also found that the data upload process which is a batch type of job runs 25% faster as well.

____________________Reply Separator____________________

Subject: Partitioning Questions
Author: "Terrian; Thomas" <Thomas.Terrian_at_standardregister.com> Date: 6/5/00 11:25 AM

We are just now beginning to work with partitioned tables and views. I have a few nagging questions. One of them is -- What is the true performance benefits of partitioning? I am not talking about the maintenance benefits, just the query performance benefits.

For example, since our queries will be using the indexes, no reason to partition the table right? I guess there would be some benefit on partitioning the table so that multiple disk drives could be working to retrieve the data. Any other performance benefits (not including the DDL benefits)?

And what about the index partitioning that we are going to do. It kind of seems like we are doing Oracle's work for them. I mean, we are telling the optimizer that this partition has these values, this other partition has these other values, etc. Is this not just a leaf block in a b-tree index or am I missing something? Why would this help the optimizer?

Ok, you can blast away. I am sure that I am missing something obvious and maybe someone can explain it to me. What are the true performance benefits of table and index partitioning? (again, not the DDL benefits).

Tom

Tom Terrian
Oracle DBA
(937) 221-1647
Thomas.Terrian_at_standardregister.com  

--IMA.Boundary.986003069

Content-Type: application/octet-stream; name="mime7146.raw"
Content-Transfer-Encoding: base64
Content-Description: Unknown data type
Content-Disposition: attachment; filename="mime7146.raw"

PGVuY29kZWQgY29udGVudCByZW1vdmVkIC0tIGJpbmFyaWVzIG5vdCBhbGxvd2VkIGJ5IExp Received on Tue Jun 06 2000 - 09:09:55 CDT