| How to assure partition table is better than non-partition table (2 MERGED) [message #501118] |
Fri, 25 March 2011 23:28  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
hi all,
I think that performance better partition table than non-partition table.
How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same.
Is it true way or not?
Thx
|
|
|
|
|
|
|
|
| Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501130 is a reply to message #501118] |
Sat, 26 March 2011 02:55  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Tlg13team wrote on Sat, 26 March 2011 05:31I think that performance better partition table than non-partition table. Sometimes yes, it is. Fortunately, it is described in Concepts book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
For 10gR2, the chapter about partitioning is placed here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2570 Quote:Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions.
Tlg13team wrote on Sat, 26 March 2011 05:31How to assure partition table is better than non-partition table at SELECT operation? Have a look at "partition pruning" in the previous link.
Tlg13team wrote on Sat, 26 March 2011 05:31I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same.
Is it true way or not?
I do not believe they are exactly the same - at least partition iterator should be stated on partitioned table. Anyway, it is possible that they take the same amount of time. But, without knowing more details (table definition, data volume, query), it is hard to tell whether partitioning would help there (yes, it is not silver bullet, it is useful on some types of queries, but not on all queries).
|
|
|
|
| Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501206 is a reply to message #501130] |
Sun, 27 March 2011 12:24 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Since you have not provided any particular query to tune, I am assuming your question is a general question about partitioning for performance. Partitioning can help speed up queries in at least two ways:
1) by letting you scan less data because of "partition pruning". Think smaller FULL TABLE SCAN. This is nice but you can often get a similar benefit by using an index, and table scans are something oracle does well so usually partition pruning is not a GAME CHANGING event.
2) by letting you improve join performance by transforming a large join into many smaller joins because of "partition wise joins". This of course requires a join and you did not mention the need to join. Partition wise joins can be huge. It is typical for partition wise joins to offer an order of magnitude improvement in performance or more, when done correctly. Order of magnitude is my definition of GAME CHANGING.
But as you might suspect, you need the right situations for these to work. You have to figure out if you have these situations.
Read up on each of these two concepts (partition pruning, partition wise joins) to decide if partitioning for performance reasons applies to your situation.
I think Ross has a great article on Partition Wise Joins somewhere but I can't find it.
Kevin
[Updated on: Sun, 27 March 2011 12:29] Report message to a moderator |
|
|
|
|
|
| Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501211 is a reply to message #501208] |
Sun, 27 March 2011 13:17 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Good one. I read that and it was great. But like most manuals it does not really give a simple explanation or offer up practical experience. So let me toss this out as a teaser to show why partition wise joins can be so big.
Assume we have a table with 100 rows in it. There are no indexes and we want to join all rows in the table to itself. We would thus have the two lists of the same 100 rows we see below. In order to join these two 100 row lists, we effectively do a cartesian product. We scan the left list once (100 rows looked at once) and then for each row from the list on the left we scan all rows on the right looking for matches (100 rows scanned 100 times).
1 1
2 2
3 3
... ...
100 100
The math is pretty simple. The number of rows looked at is this:
100(1) + 100(100) = 10100 rows touched. If you do not understand this math, then go back and read what I said about the cartesian join getting it done and walk through the cartesian join process in your head until you do understand.
But suppose we partition the big table into 10 smaller partitions that maybe look like what we see below. Something wonderful happens. The rows in each partition can be found in only one partition in the other set of rows. Thus row#1 on the left is found in one and only one partition on the right. This is true for all rows because of the partitioning. To join row#1, we only need to look at one of the ten partitions effectively eliminating 90% of the rows we have to look at.
So to join the partitioned version of the table we acutally need to do 10 smaller joins. But the process is the same, for any one of the smaller partitions, look at each row the left (10 rows looked at once), then look at each row on the right once for each row taken from the left side (10 rows looked at 10 times).
11 11
12 12
... ...
20 20
91 91
92 92
... ...
100 100
We do the same math, but do it ten times.
10(1) + 10(10) = 110(10) = 1100 rows touched.
Notice how the total rows touched for the non-partitioned table was 10,100 rows. But for the total of all the little partitions was 1,100 rows touched. That is 89% less work to do the same join operation. One way to look at partitioning is to see how it artificially creates new information about the data that we did not have before by physically (or logically) re-arranging rows into groups. It is all about the metadata, the information about the data.
(10100-1100)/10100 = 89%
Though this example is a little contrived, it shows you what partition wise joins are all about. You get this benefit regardless of if you are using parallel query or not though Oracle Doc suggests you need parallel query to get benefit from partition wise joins. Parallel query is a different animal altogether. One can see how Parallel Query is a natural jumping off point from Partitioning, but Parallel Query does not reduce workload, Partitioning can reduce workload.
Kevin
|
|
|
|
|
|
|
|
|
|
| Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501407 is a reply to message #501406] |
Mon, 28 March 2011 20:50 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
You must touch rows on both sides, left and right. I believe you have only counted rows touched on the right.
Also just for clarification, I used the term CARTESIAN PRODUCT but this is to refer to the fact that we will be joining all rows to all rows in each row grouping and then filter out rows that do no match the join criteria as vs. using an index to facilitate the join. We are doing a join, jsut any indexing to speed if up. If you are looking for a mental exercise, consider how the whole thing might change if you had an locally partitioned index on the table.
Lastly don't get hung up on the example. It is just a simple way for me to demonstrate one way to get an order of magnitude improvment out of partition wise joins. Notice of course that as a advanced feature this works only in the right situations. You have to match your needs to the purpose of the the feature. This goes for any advaced feature of oracle. To the extent that what you want to do matches the features designed purpose you have a home run. The more you get away from that the less mileage you get out of the feature.
Good luck, Kevin
[Updated on: Mon, 28 March 2011 20:54] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.