hash or range?
Andy, January 02, 2006 - 7:34 pm UTC
So with that in mind, if I have the following table:
SQL> create table t
2 (
3 object_id varchar2(16) ,
4 object_type varchar2(18) ,
5 -- about 12 other columns here omitted for brevity)
/
with object_id values categorized like this:
SQL> select min(object_id) "MIN",
max(object_id) "MAX",
count(object_id) "CNT"
from t
group by object_id
order by object_id;
MIN MAX CNT
---------------- ---------------- --------
0800a04f8000015d 0800a04f8004056e 945000
0900a04f80000140 0900a04f80054630 1120000
0b00a04f80000107 0b00a04f8000026b 30
0c00a04f80000103 0c00a04f8004eeb2 12
1000a04f8000012d 1000a04f80051125 354
1700a04f8000012c 1700a04f8000012c 800
1900a04f80000117 1900a04f80051007 160
3a00a04f8000011d 3a00a04f80008900 11
3c00a04f80000102 3c00a04f80000102 998
3d00a04f80000101 3d00a04f8004a8d9 998
3e00a04f8000011e 3e00a04f8000011e 1603
4100a04f80011785 4100a04f80053982 140
4600a04f8000ed88 4600a04f80053479 609
4b00a04f8000018d 4b00a04f8000ed9a 12045
4c00a04f80000186 4c00a04f8000edad 1306299
5800a04f80000286 5800a04f8000ed6e 796044
I just can't seem to come up with a way to range partition only the 4 largest object_id values and put the remaining 12 object_id values into an 'all_the_others' partition. Do you think it would be OK to create a partition for each upper bound value (16 total partitions), even though some of the partitions will only ever have about 30 or so rows, while some others will have well over a million?
Or would you suggest a Hash partitioning scheme in this case - to evenly spread the data out across hashed partitions?
Can't thank you enough for your valuable time and suggestions. I appreciate it :-)
Andy
January 02, 2006 - 7:37 pm UTC
I don't get that query. you group by object_id, but you have min and max object_ids that are NOT THE SAME???
I don't know what I'm looking at.
And most importantly - what is your goal with partitioning. What precisely do you want to accomplish with it?
sorry
A reader, January 03, 2006 - 4:29 am UTC
sorry, sorry, sorry - should have been this query
SQL> select min(object_id) "MIN",
max(object_id) "MAX",
object_type TYPE,
count(object_type) "CNT"
from t
group by object_type
order by object_type;
MIN MAX TYPE CNT
---------------- ---------------- -------- -------
0800a04f8000015d 0800a04f8004056e method 945000
0900a04f80000140 0900a04f80054630 document 1120000
0b00a04f80000107 0b00a04f8000026b drawer 30
0c00a04f80000103 0c00a04f8004eeb2 cab 12
1000a04f8000012d 1000a04f80051125 job 354
1700a04f8000012c 1700a04f8000012c subt 800
1900a04f80000117 1900a04f80051007 config 160
3a00a04f8000011d 3a00a04f80008900 dconfig 11
3c00a04f80000102 3c00a04f80000102 trial 998
3d00a04f80000101 3d00a04f8004a8d9 logf 998
3e00a04f8000011e 3e00a04f8000011e srvlog 1603
4100a04f80011785 4100a04f80053982 seslog 140
4600a04f8000ed88 4600a04f80053479 mod_met 609
4b00a04f8000018d 4b00a04f8000ed9a smtlist 12045
4c00a04f80000186 4c00a04f8000edad acl 1306299
5800a04f80000286 5800a04f8000ed6e qlist 796044
My goal for partitioning is to take advantage of the partition elimination feature. Based on the high number of ad-hoc queries against this database, the optimizer is frequently going with full table scans even though I have the correct indexes in place. I don't have a problem with FTS, but if I can help it run faster by using partition elimination - or maybe by using a local index on the partition - then I'd like to accomplish that.
Or maybe I'm still missing a concept somewhere and should be looking at another feature???
sorry about not being clearer before. thanks again for your help.
Andy
January 03, 2006 - 7:36 am UTC
so, what do they query by that you would like to take advantage of partition elimination by??
Unless you partition by something "useful", well, partition elimination won't kick in at all.
and if your existing b*tree indexes "aren't helping/being used", a locally partitioned b*tree index will accomplish the same thing...
Is this a warehouse table OR a transactional table? (eg: read mostly with data loads OR constantly modified?)
A reader, January 03, 2006 - 1:22 pm UTC
it's a transactional table, used by a 3rd party document management software. they store metadata in the database as objects and the content files on the OS file system. every object has an object_id in the main table (table t)that is used to join with other tables in order to extract the meatdata associated with each content file.
the object_id column from this table is used in just about every query the application issues against the database, and it's the only column that can be used to join 2 or more tables together (all other columns are unique to each table) - so I'm sure that object_id is the column to partition on. I'm just not sure if partitioning by Range would be OK since this would leave me with a dozen or so partitions with anywhere between 11 and a 12,000 rows each, and the remaining 4 partitions with over 1 million rows. Is this bad practice to have?
But if I Hash partition in object_id, I would/should end up with more evenly sized partitions but like you said it might not be useful enough for partition elimination.
uugh, maybe I should stop thinking so much about it and just test both to see which one (if any) works best.
January 03, 2006 - 1:45 pm UTC
if object_id is used in every query directly....
why wouldn't the index on object_id be used? Looks like a unique key to me.
Show me a query that is "broken" (back ad-hoc query) and help me see why partitioning on object_id would help it?
Ramesh, January 03, 2006 - 5:22 pm UTC
Tom,
I did not get the answer to the question.
Can we improve query performance of OLTP table (5+ million rows) by hash partitioning on primary key and all the partitions resides on same physical disk.
Query always againest primary key.
January 03, 2006 - 6:13 pm UTC
you didn't answer my question either? so we are even??
You started by saying something very very much contradictory to what you are saying now.
You first said:
<quote>
My goal for partitioning is to take advantage of the partition elimination
feature. Based on the high number of ad-hoc queries against this database, the
optimizer is frequently going with full table scans even though I have the
correct indexes in place.
</quote>
And now it is always by key, always using an index??? This is why I ask questions, it is not possible to say "good bad indifferent" without understanding the problem.
It is highly improbable that you would improve query performance of keyed reads by hashing on the key. In fact, if you don't design this properly with a mix of "hash by primary key", "global range partition keys by their keys", you would likely negatively impact performance of queries.
<quote src=Expert Oracle: Database Architecture>
OLTP Systems
You should not look toward partitions as a way to massively improve query performance in an OLTP system. In fact, in a traditional OLTP system, you must apply partitioning with care so as to not negatively affect runtime performance. In a traditional OLTP system, most queries are expected to return virtually instantaneously, and most of the retrievals from the database are expected to be via very small index range scans. Therefore, the main performance benefits of partitioning listed previously would not come into play. Partition elimination is useful where you have full scans of large objects, because it allows you to avoid full scanning large pieces of an object. However, in an OLTP environment, you are not full scanning large objects (if you are, you have a serious design flaw). Even if you partition your indexes, any increase in performance achieved by scanning a smaller index will be minisculeif you actually achieve an increase in speed at all. If some of your queries use an index and they cannot eliminate all but one partition from consideration, you may find your queries actually run slower after partitioning since you now have 5, 10, or 20 small indexes to probe, instead of one larger index. We will investigate this in much more detail later when we look at the types of partitioned indexes available to us.
......
</quote>
sorry - but the information you've been giving me is contradictory and I hesitate to say anything without good inputs.
that wasn't me!!!!!
Andy, January 03, 2006 - 7:35 pm UTC
Hi Tom - that last post wasn't from me, so I will try to explain a bit more, I have to take a step back and describe a few things better:
I've been calling the main table in the database 't' (it's actual name is OBJ_ATT). another table, called DOC_ATT1, is frequently used in a join with t on the object_id column. I randomly just grabbed this query out of a trace and did an explain plan on it. I've noted that most of the queries are producing basically the same plan (Full Table Scans):
SELECT d.scope
FROM doc_att1 d, obj_att t
WHERE t.object_id = d.object_id
AND t.is_folder = 1
AND t.is_deleted = 0;
which produces a plan like this:
SELECT STATEMENT Cost=95 03-JAN-2006
2.1 SORT(UNIQUE)
3.1 HASH JOIN
4.1 TABLE ACCESS(FULL) - DOC_ATT1
4.1 TABLE ACCESS(FULL) - OBJ_ATT
I have an index on (object_id) for DOC_ATT1, an index on (scope) for DOC_ATT1, and an index on (object_id) for T - but no index on (is_folder) for T or (is_deleted) for T.
To state the obvious, the columns in table t called "is_folder" and "is_deleted" are perfect candidates for a bitmap index (values are either 1 or 0) but since the table gets updated frequently I am cautious about using bitmaps here. I currently have 10 indexes on other columns for the table t, which has 70 columns in the table, and I suppose I could create one index for each column but that doesn't seem like a good solution.
If I remove the "AND t.is_folder = 1 AND t.is_deleted = 0" in the WHERE clause, the index gets used on T (object_id), but no indexes are used on the doc_att1 table probably because of the high cardinality of the SCOPE column.
I was thinking that if a majority of the queries will be doing a FTS anyway, partitioning could help.
The doc_att1 table I'm not so concerned about doing a FTS on, it'll remain somewhat small; but table t (obj_att) I'm not so thrilled about the FTS on because it is going to grow to tens of millions of rows.
I'm trying to make a partition in table t that would contain a similar amount of rows as exist in the entire table called DOC_ATT1. So if the smaller table DOC_ATT1 has 10,000 rows, then I would like a partition for the object_id values in table t to have 10,000 rows - because it seems to me that if the FTS on table t could eliminate the other partitions except the 10,000 row partition that is a match to the table being joined, the FTS on table t would go faster because it wouldn't have to include the 1 million other rows from the other partitions in table t that would otherwise be scanned in the FTS on table t.
In my head I'm thinking this analogy and trying to apply it to partitioning table t:
I go into a huge grocery store and I'm looking for apples. The grocery store is "partitioned" into sections - cereal isle, produce isle, canned goods isle, etc. - and even though I can go directly to the produce "partition" where there exists other produce like oranges and pears, I can at least eliminate having to look through the canned goods and cereal isles (partition elimination). So even though I couldn't use an index and go directly to the apple stand (needed a full table scan) at least I was able to go directly to produce (only needed to full scan the the produce partition) and get the apples.
ouch, that hurt my head
January 03, 2006 - 8:40 pm UTC
Indeed, I wonder who Ramesh is and why he felt I didn't answer *his* question, given - well, that he hasn't any here on this page....
Ok...
... perfect candidates for a bitmap index (values are either 1 or 0) but since
the table gets updated frequently I am cautious about using bitmaps here. ...
well, they are horrible candidates for bitmaps then :) and the zero/one-ness don't incline me to bitmaps immediately.
But - what is the cardinalities here - does the optimizer understand:
"is_folder = 1 - very few"
(guessing you have few folders) - if so, it might full scan "T" and then index access the other - I'm guessing this all comes down to cardinalities and getting them right - is an explain plan showing you card= values even a little bit close to reality?
...
If I remove the "AND t.is_folder = 1 AND t.is_deleted = 0" in the WHERE clause,
the index gets used on T (object_id), but no indexes are used on the doc_att1
table probably because of the high cardinality of the SCOPE column.
.....
that surprises me - are you sure? I would expect two full scans and a hash join since you are returning EVERYTHING??? This query:
SELECT d.scope
FROM doc_att1 d, obj_att t
WHERE t.object_id = d.object_id
is using a nested loops joing??!?! that is whacky. (and one of the tables would have to be full scanned pretty much, unless you had an index on scope,object_id - then it could full scan or fast full scan that index instead - but it'd still be a full scan)
But partitioning by object_id would do:
zero
for this query really - maybe with parallel query it could do joins just between the right partitions - but that isn't the goal here.
The problem with your analogy is - you don't have fruit, you have the UPC (universal price code) and you hashed by it and things are organized by their UPC - not very useful!
But back to your query above, lets see
a) tkprof with row source operation for it
b) autotrace traceonly explain for it
c) same tkprof without the where clause you say you removed
d) same autotrace without the where clause you say you removed
Hash Partitioning - Records Across Partition
Ashok, September 21, 2010 - 1:39 pm UTC
This is excellent overview of hash paritioning. Though it does not explain the possibility of a id (say customer id) of hash paritioned table spanning across multiple partitions. So if I have hash partitions A and B on customer id, What is the likely hood of the customer id 1000 falling in both A and B hash partitions?
September 21, 2010 - 4:10 pm UTC
the likelihood is exactly 0.0%
think about it - if you hash on ID, that means you take ID and turn it into a partition number so you know where to put it and ultimately where to find it again. If the same ID could hash to partition "P1" today and partition "P2" tomorrow - we'd never be able to find the data again.
The hash function is deterministic - given a value for ID, it will always hash to the same partition in that table (unless and until you change the number of partitions of course - but even then, it will deterministically return the same value given the new partitioning scheme)
Data between two partitions
Ammar khwaireh, December 05, 2011 - 12:40 am UTC
Hi Tom,
Do i lose the value of partitioning if my query gets results from two partitions? Actually i have a big table and i don't know what to partition on.
It contains (store_id,item_id,serial_no) and would expect 60 million records. Is it a good practice to partition by serial_no ? (although queries may go into more than one partition)
Thanks
December 06, 2011 - 11:15 am UTC
No, you don't "lose" the value.
If you get data from two partitions, but you have 1,000 partitions - I'd say that is pretty good.
It contains (store_id,item_id,serial_no) and would expect 60 million records.
Is it a good practice to partition by serial_no ? (although queries may go into
more than one partition)
no one can answer that without knowing what the application does, how it queries the data.
it could well be that since you have such a small number of records, only 60 million, that partitioning isn't even necessary at this stage.
let's start with this question - why are you partitioning in the first place. What is your goal behind partitioning?
Partitioning
Ammar Kh, December 06, 2011 - 1:43 pm UTC
Dear Tom,
thanks for your reply. Actually, it is a table to hold the balances of inventory items. It will contain a huge number of serials. Each record with a quantity of 1.
The application will insert into this table (Receipts of SIM cards,Vouchers) and will issue from it (update qty to zero).
So, i want to partition this table in order to have faster select from it (when issue) and faster insert (when receipt) as it will be going bigger each day.
thanks for your advice.
December 07, 2011 - 12:59 pm UTC
what kind of select do you do?
if you do not full scan this table, you will not likely be making selects go faster with partitioning, in fact - you will have to work hard to ensure you do not make them go SLOWER!
partitioning is not fast=true.
It takes about the same amount of time to retrieve 100 rows from a 100,000 row table via an index as it does from a 1,000,000,000 row table.
So, do you full scan a lot?
Would you benefit from partition elimination during this full scan?
If the answer is "no" to either of those, partitioning isn't going to make selects faster.
also, it is in general slower to insert into a partitioned table - we have to do MORE WORK to figure out where a row goes before putting it there. there are some edge cases in a highly concurrent insert scenario where it can increase concurrency - but you really have to have a special case for that to be true.
Think about the physics involved here - think about what you do (to the data) and what is involved in partitioning and put forth why you believe partitioning would make things 'faster' for you.
in your case (because I'm assuming 'index access'), partitioning would likely be useful for ease of administration - but not performance, in fact you'd have to be careful to not make things slower after partitioning.