range partition on char column

From: mario <xnazsco_at_tiscali.it>
Date: Fri, 07 Jul 2006 10:49:15 +0200
Message-Id: <pan.2006.07.07.08.49.12.364221@tiscali.it>

Hi all,
I'm using 9.2.0.1 on Solaris machine.
I need to create a range partition table usign a CTAS syntax. The columns I have to use for range partition is a char(10) storing a date in the format yyyy-mm-dd (please don't ask me WHY!!! :) )

I know that when we use a function to casting a datatype to another one, the order results can be different from the one aspected.
But I've made some test, and the cast should work fine..but obviously it _does_ not work.

following the scenario:

original table:

CCID                                      NOT NULL NUMBER(10)
CDATE                                     NOT NULL CHAR(10)
............  other columns here  ................

new table (the one I should create partitioned)

CREATE TABLE NEW
 TABLESPACE TABLE_AREA
 PARALLEL
 NOLOGGING

        PARTITION BY RANGE (cdate)
        (partition q1_2003 values less than (to_Date('2003-05-01','yyyy-mm-dd')),
 	partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),
 	partition q3_2003 values less than (to_Date('2004-01-01','yyyy-mm-dd')),
 	partition q1_2004 values less than (to_Date('2004-05-01','yyyy-mm-dd')),
 	partition q2_2004 values less than (to_Date('2004-09-01','yyyy-mm-dd')),
 	partition q3_2004 values less than (to_Date('2005-01-01','yyyy-mm-dd')),
....... other partitions here ...................................... 	
	partition extra values less than (maxvalue))
        AS SELECT * FROM OLD WHERE 1=2;

When I try to create the table I receive the error:

partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),

                   *

ERROR at line 7:
ORA-14037: partition bound of partition "Q2_2003" is too high

To be confident with the order used by Oracle, I create a very simple table and tested the order by clause:

SQL> create table test (testo char(10));

SQL> insert into test values ( '2003-01-01'); 1 row created.  

... other inserts here ...........

then I tested the order by clause:

SQL> select * from test order by testo asc;  

TESTO

---

2003-01-01
2003-05-01
2003-09-01
2004-01-01
2004-05-01
2004-09-01
2005-01-01
2005-05-01
2005-09-01
 

9 rows selected.  

SQL> select * from test order by testo desc;  

TESTO

---

2005-09-01
2005-05-01
2005-01-01
2004-09-01
2004-05-01
2004-01-01
2003-09-01
2003-05-01
2003-01-01
 

9 rows selected.

I _think_ that when I create the partition using char() column, also if the records will be inserted using the format to_date('yyyy-mm-dd'), the order should be the right one. I mean, the alphabetical order should correspond to the chronological one.

Where I'm wrong??

any help will be appreciated
regards

M. Received on Fri Jul 07 2006 - 03:49:15 CDT