Skip to Main Content
  • Questions

  • partition comparision is done from left to right

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sj.

Asked: August 17, 2001 - 10:12 pm UTC

Last updated: September 07, 2001 - 11:58 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

</code> http://asktom.oracle.com/pls/ask/f?p=4950:9:::NO::F4950_P9_ORIG,F4950_P9_DISPLAYID:Y,1199992531506 <code>

"
Well, xyzdate = 16-aug-01 and col_code = abby which is less than dzz so that
goes into partition one..

For all other dates < 16-aug-01 -- they will ALL go into partition
part1_08_16_01

the partition comparision is done from left to right. Any rows that have a xyzdate less then august 16'th, 2001 will by definition go into partition part1 -- xzydate is less than 16-aug-2001, the first partition they fit into -- there
they go.

Only when the date is equal to (or greater than) 16-aug-2001 will we ever even
consider another partition.

"

In one of your previous answers to me you have said that , partition bounds are never equal to , never less than , infact we define less than. So when the date is equal to 08_16_01 how is it going into the first partition.

I accept that abby fits into the first partition when considering the range given for the second partition key , but when considerig both the columns in the partition key, when it starts looking from left to right, THE PARTITION SHOULD GET DISQUALIFIED , as soon as it finds that 16-aug-2001 is not less than but equal to 16-aug-2001 .


Iam surprised to hear that anyrows less than 16-aug-2001 , will go right into the first partition without even taking into consideration the second partition key. IN MY OPINION a DATE LIKE AUGUST 2ND 2001, AND COL_CODE LIKE WEST , should not go into the first partition but into the third partition. HOW COME IT GOES INTO THE FIRST PARTITION?

THANK YOU



and Tom said...

You have a set of vectors:

(to_date('08/16/00','mm/dd/yy'),'DZZ')
(to_date('08/16/00','mm/dd/yy'),'EASTA')
(to_date('08/16/00','mm/dd/yy'),'WDD')
(to_date('08/16/00','mm/dd/yy'),'WESTA')
(the-rest)


You give us a VECTOR (16-aug-2001,'ABBY')

We compare this vector to each of the above. In a multi-column range partitioned table, the values are compared from left to right.

If the first column in your vector is LESS-THAN the first column of some partition range -- that is the partition it goes into.

If the first column in your vector is EQUAL-TO the first column of some partition range -- we continue the comparision based on the second column. In this case, we compare 'ABBY' to 'DZZ' and discover that goes into that partition.


The rules are pretty clear on this in the documentation -- its the way it is supposed to work. Consider your partition range to be a sequence of "characters in a string". Suppose you had a partition range:

( 'B', 'C' ) => string 'BC'
( 'B', 'E' ) 'BE'
( 'B', 'G' ) 'BG'


Now, if you compare the string 'AD' to each of those, you would find that 'AA' < 'BC' right? 'AD' is not greater then 'BC' -- even though D > C. The compares are done from LEFT to RIGHT and the first thing that stops the compare -- in effect wins.

The same happens for partition ranges. We compare the vector from the current row to the vectors that define the partition and STOP immediate when we find the thing that is LESS-THAN. If that is the first column, so be it. If you used the partition ranges I have above ('bc','be','bg') -- anything that starts with A would go into the partition 'BC' since 'A' < 'B'. The same holds true for your dates.


You must have mis-intrepreted my response to you in a prior posting (if you provide the URL, i'll take a look and see if I can make it more clear for others)

Maybe this will help as well. The partition ranges are compared and sorted in exactly the same way a set of rows in a table would be. Consider:

ops$tkyte@ORA817.US.ORACLE.COM> create table t ( d date, c varchar(5) );
Table created.

ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( '16-aug-2001', 'DZZ' );
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( '16-aug-2001', 'EASTA' );
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( '16-aug-2001', 'WDD' );
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( '16-aug-2001', 'WESTA' );

ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( '16-aug-2001', 'ABBY' );
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( '16-jan-2001', 'ZZZZ' );

1 row created.

ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select * from t order by d, c;

D C
--------- -----
16-JAN-01 ZZZZ
16-AUG-01 ABBY
16-AUG-01 DZZ
16-AUG-01 EASTA
16-AUG-01 WDD
16-AUG-01 WESTA

6 rows selected.

So, if you are ever confused about what partition a row with certain values will go into -- simply create a small table with columns having the same datatypes as your partition key. Insert your partition key values as I did and then insert the row(s) you want to find out where they will go. Select * from that table ordering by the partition key. The PARTITION row that comes out immediately after your data row (my data rows are in bold above) is the partition that row will go into. This shows that 16-jan-2001 will go into partition 1 even though ZZZZ is "really" big -- the first column sorts it to the top putting it into the first partition.


See also:

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c09parti.htm#430096 <code>

it has more examples and explains this as well.



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oracle's documentation on partitions is not good.

George, August 19, 2001 - 10:29 am UTC

The link which you gave does not have satisfactory examples at all. ORACLE is not doing enough work at documentation. This is what will happen , if they come up with too many new features, without any discretion. And what happened to all the hard copied docs, why did they stop producing them.
FOLLOW UP:


"Well, xyzdate = 16-aug-01 and col_code = abby which is less than dzz so that goes into partition one.."

though the high bound of the first partition is values less than 16-aug-01 , but there is no other partition with a lesser high bound then that, that is the reason why values less that the given date will go into part1_16-aug-01.

BUT , instead of abby if the col_cd for that record is EAST, will it go into the second partition i.e.part1_16-aug-01. , according to your explanation it should .

Are we right , Tom.



Tom Kyte
August 19, 2001 - 12:45 pm UTC

I didn't find it lacking (the documentation) -- it seems fairly clear to me. I particularly like the example:

<quote>
In mathematical terms, for vectors V1 and V2 which contain the same number of values, Vx[i] is the ith value in Vx. Assuming that V1[i] and V2[i] have compatible datatypes:

* V1 = V2 if and only if V1[i] = V2[i] for all i

* V1 < V2 if and only if V1[i] = V2[i] for all i < n and V1[n] < V2[n] for some n

* V1 > V2 if and only if V1[i] = V2[i] for all i < n and V1[n] > V2[n] for some n
</quote>

In the example you are talking about with the vectors:

(to_date('08/16/00','mm/dd/yy'),'DZZ')
(to_date('08/16/00','mm/dd/yy'),'EASTA')
(to_date('08/16/00','mm/dd/yy'),'WDD')
(to_date('08/16/00','mm/dd/yy'),'WESTA')
(the-rest)

It has NOTHING to do with there not being a partition for the date of 08/17/00. The rules on vector comparision are listed above. Regardless of whether or not there was a partition with a date of 08/17/00 -- all rows with values less then the vector (to_date('08/16/00','mm/dd/yy'),'WESTA') would go into one of the first four partitions.

In this case using the vector:

('16-aug-01','ABBY') and using the rules above, we find that

('16-aug-01','ABBY') < (to_date('08/16/00','mm/dd/yy'),'DZZ')

and hence belongs in that partition) since V1[i] = V2[i] for i < 2 and V1[n] < V2[n] for n = 2

For me, the only simple way I can express it is -- if you were to have a table as I do above ( table t ( d, c ) ) -- we find the partition to put the data in in the same fashion as a sort on that table would be. You would not expect 16-aug-01, 'ABBY' to ever sort AFTER 16-aug-01, 'DZZ' (that would be a bug) -- the same rule applies for partitions.



correction

George, August 19, 2001 - 1:05 pm UTC

Iam really sorry.

in the below paragraph

"though the high bound of the first partition is values less than 16-aug-01 , but there is no other partition with a lesser high bound then that, that is the reason why values less that the given date will go into part1_16-aug-01. "

I did not mean a lesser high bound, actually I wanted it to be a higher bound i.e. partitions with hight bound values of 15-aug-01 and higher.

secondly, lets say we have 8 partitions for 15th and 16th Aug. Now the user is trying to insert records for 14th August. They will definitely go into part1_08_15_01( as per the partitioning nameing convention in your example, I would prefer the year to be the prefix.) But this way data will be spread hapzardly, I think this will be harmful.

Is there any way where if data coming in , for example 12th Aug in the above case is made to go into an exclusive partition which is not part of the above partitions( actually how can we create such a partition at the table creation time, or how can we do this dynamically). How can this be done , how can this be done at all.

Tom Kyte
August 19, 2001 - 3:52 pm UTC 

You are associating "dates" with partitions -- it is true that dates are frequently used to partition data, but there is nothing "inherit" in the partitioning code that makes dates different from say a number.  For example, if the partition vectors were:

(200,'DZZ')
(200,'EASTA')
(200,'WDD')
(200,'WESTA' )
(max_value,max_value)

we would do this no differently.  Anything with a value LESS THAN 200 (less than august whatever) would go into the first partition.  Anything with a value EQUAL to 200 would go into one of the remaining partitions dependning on the value of the second column.  In your example, having august-15/august-16 and desiring anything before august-15'th to go into its own special partition, you would set up partitioning vectors:

ops$tkyte@ORA817.US.ORACLE.COM> create table t
  2  ( xyzdate date,
  3    col_code varchar2(10)
  4  )
  5  partition by range(xyzdate,col_code)
  6  (partition MIN_VALUES  values less than        
           (to_date('08/14/2001','mm/dd/yyyy'),MAXVALUE),
  7   partition part1_20010815 values less than 
           (to_date('08/15/2001','mm/dd/yyyy'),'DZZ'),
  8   partition part2_20010815 values less than 
           (to_date('08/15/2001','mm/dd/yyyy'),'EASTA'),
  9   partition part3_20010815 values less than 
           (to_date('08/15/2001','mm/dd/yyyy'),'WDD'),
 10   partition part4_20010815 values less than 
           (to_date('08/15/2001','mm/dd/yyyy'),'WESTA'),
 11   partition part1_20010816 values less than 
           (to_date('08/16/2001','mm/dd/yyyy'),'DZZ'),
 12   partition part2_20010816 values less than 
           (to_date('08/16/2001','mm/dd/yyyy'),'EASTA'),
 13   partition part3_20010816 values less than 
           (to_date('08/16/2001','mm/dd/yyyy'),'WDD'),
 14   partition part4_20010816 values less than 
           (to_date('08/16/2001','mm/dd/yyyy'),'WESTA'),
 15   PARTITION MAX_VALUE VALUES LESS THAN(MAXVALUE,MAXVALUE)
 16  )
 17  /

Table created.

ops$tkyte@ORA817.US.ORACLE.COM> 
ops$tkyte@ORA817.US.ORACLE.COM> insert into t values ( to_date('08/14/2001','mm/dd/yyyy'), 'ZZZZ' );

1 row created.

ops$tkyte@ORA817.US.ORACLE.COM> 
ops$tkyte@ORA817.US.ORACLE.COM> select * from t partition( min_values );

XYZDATE   COL_CODE
--------- ----------
14-AUG-01 ZZZZ

In otherwords -- there is nothing "haphazard" about it, the data just goes where you tell us to.  the partitioning software is not expecting a partition/day (or month or year or anything).  

So, you can make it put the data where you want by setting up the proper range partitions (eg: using august-14'th,maxvalue catches EVERY row that has a date of august-14 and before -- regardless of the value in the second column.

ASCII value of the concatenation of the partition key

Aba, August 19, 2001 - 4:39 pm UTC 

SQL> ED
Wrote file afiedt.buf

  1* SELECT ASCII('200DZZ') FROM DUAL
SQL> /

ASCII('200DZZ')
---------------
             50

SQL> ED
Wrote file afiedt.buf

  1* SELECT ASCII('200EASTA') FROM DUAL
SQL> /

ASCII('200EASTA')
-----------------
               50

SQL> ED
Wrote file afiedt.buf

  1* SELECT ASCII('200WDD') FROM DUAL
SQL> /

ASCII('200WDD')
---------------
             50

SQL> ED
Wrote file afiedt.buf

  1* SELECT ASCII('200WESTA') FROM DUAL
SQL> /

ASCII('200WESTA')
-----------------
               50


The concatanation of your columns in the partition key , Tom, is resulting in the same ASCII value. Then how do the records go into different partitions. I might be missing something simple here, or this question might be very trivial, but can you describe . 

Thank you

Tom Kyte
August 19, 2001 - 4:45 pm UTC 

ascii is a function that returns the numeric ascii code of the character you pass to it:

ops$tkyte@ORA8I.WORLD> select ascii('2') from dual;

ASCII('2')
----------
        50

CHR is its inverse:

ops$tkyte@ORA8I.WORLD> select chr(50) from dual;

C
-
2

An ascii value is for a single character -- not a string.


I really don't know what you are trying to do here -- partition ranges are compared column by column using their native types (numbers to numbers, strings to strings).  They are not glued together as a big string and compared.  

You have to compare VECTORS (if it is easier -- think of them as columns).  it is the vector (200,'WESTA') -- not the string '200WESTA' we are dealing with.

Chop off

READER, August 20, 2001 - 2:58 pm UTC

Hi, Tom,

Just a little suggestion:
In the Followup Tom, would you please start from the second line? The current format result in chopping off several columns in the right hand side when i print the whole topic out----- would you please just start from very left for each line instead of what it is right now? I know you comment MUST BE different from which created by others.

Thanks

Partition on Character keys

Swaminathan, September 05, 2001 - 3:55 am UTC

Hi

I find the explanation of "values less than "clause in partitioning to be more confusing.

Literally if I have a date column as say 16Th AUGUST 2001 and another keycolumn value as say "SGP" and I have a composite partition with key columns on date plus another Varchar2 type,

if i say
partition p1 values less than ( to_date('16-AUG-2001','dd-mon-yyyy'),'SNG') how can a data with 16-AUG-2001 but with second value as say KL go in this partition when the first column values are equal instead of the clause we specify as "less than"




Tom Kyte
September 07, 2001 - 11:58 am UTC

It is because we are doing SQL vector comparisons. We are comparing two VECTORS

v1 < v2

NOT two scalars!!! The rules for vector comparision are:

V1 = V2 if and only if V1[i] = V2[i] for all i

V1 < V2 if and only if V1[i] = V2[i] for all i < n and V1[n] < V2[n] for some n

V1 > V2 if and only if V1[i] = V2[i] for all i < n and V1[n] > V2[n] for some n

So the partition keys are compared from left to right with eachother. The FIRST partition key values in the two vectors are NOT equal stop the comparision. We look at their values and based on that decide if V1 is less than V2 or V1 is greater than V2.


The partition clause is made against vectors:

partition part1_20010815 values less than
(to_date('08/15/2001','mm/dd/yyyy'),'DZZ'),

Not against scalars. That is the key point. Look at the ORDER BY example that shows how these vectors would sort -- its the same thing. The "row" if you will is LESS THAN the other row based on the same rules as are used for the vector comparisons.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.