Home » RDBMS Server » Performance Tuning » How to assure partition table is better than non-partition table (2 MERGED) (Oracle 10g R2)
How to assure partition table is better than non-partition table (2 MERGED) [message #501118] Fri, 25 March 2011 23:28 Go to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
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 [message #501120 is a reply to message #501118] Fri, 25 March 2011 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 22535
Registered: January 2009
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501129 is a reply to message #501118] Sat, 26 March 2011 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58623
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think that performance better partition table than non-partition table.

Sometimes better, sometimes worse, sometimes same.

Regards
Michel
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 Go to previous messageGo to next message
flyboy
Messages: 1769
Registered: November 2006
Senior Member
Tlg13team wrote on Sat, 26 March 2011 05:31
I 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:31
How 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:31
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?

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 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
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 #501208 is a reply to message #501206] Sun, 27 March 2011 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58623
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe the documentation itself:
Database Data Warehousing Guide
Chapter 5 Partitioning in Data Warehouses

Regards
Michel
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
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).

1		1
2		2
...		...
10		10

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 #501258 is a reply to message #501211] Mon, 28 March 2011 04:27 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
Member
Than you Keven. This is wonderful response.

[Updated on: Mon, 28 March 2011 04:28]

Report message to a moderator

Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501260 is a reply to message #501258] Mon, 28 March 2011 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58623
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now Kevin make the effort to summarize and explain the documentation, I hope you will make the effort to read it out of respect for Kevin.

Regards
Michel
Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501406 is a reply to message #501211] Mon, 28 March 2011 20:34 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
Member
Hi Keven.

I understand your explanation and math formula Smile.
BUT, I have a little confuse at your formula.

I read about cartesian product at Wikipedia.
URL: http://en.wikipedia.org/wiki/Cartesian_product#cite_note-1

Cartesian product formula is on two sets: ranks × suits = {result}

I have use above formula at your assume data.

The result is below:

Non-Partition table: (100 x 100)=10000 touched.
Partition table: (10 x 10)+(10 x 10) +.....+(10 x 10) =1000 touched.

Your results are 1100 and 10100, BUT my results are 1000 and 10000.

Please explain this difference?

In the end, I have get best benefit when two partition table join case. Am I correct?
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1934
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

Re: How to assure partition table is better than non-partition table (2 MERGED) [message #501409 is a reply to message #501407] Mon, 28 March 2011 21:19 Go to previous message
BlackSwan
Messages: 22535
Registered: January 2009
Senior Member
>I think that performance better partition table than non-partition table.
What happens when the column used to partition the data, does not appear in the WHERE clause?
Previous Topic: row cache lock/dc_users??
Next Topic: Performance issue with long running query
Goto Forum:
  


Current Time: Wed Jul 30 14:37:57 CDT 2014

Total time taken to generate the page: 0.26292 seconds