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
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
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?
You have a set of vectors:
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) );
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> select * from t order by d, c;
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.
it has more examples and explains this as well.