Search code examples
sqldatabaseoracle-databaseoracle11gdatabase-partitioning

Partitioning a Oracle table using a function over a column

I would like to partition a table using a function over a column, due to I don't want to create a new column. My table Example has a column DATE_VARCHAR (format 'YYYY-MM-DD') and I would like to partition it given the month of the date, but I have some problems.

  1. I can't ALTER my table to add the partitioning (Oracle requirements), so I create a backup table, to backup the data.

  2. I can't add a column in the new structure (client requirements), so if I want to do the partitioning using substring function over DATE_VARCHAR, I get a syntax error:

    CREATE TABLE PRUEBA(
DATE_VARCHAR VARCHAR2(10),
SOME_COLUMNS VARCHAR(50)
)

PARTITION BY LIST (SUBSTR(DATE_VARCHAR,6,2))
(PARTITION p1 VALUES ('01','05','09'),
PARTITION p2 VALUES ('02','06','10'),
PARTITION p3 VALUES ('03','07','11'),
PARTITION p4 VALUES ('04','08','12'));

    Due to it expects an identifier column (column name), in spite of I get the following error: ORA-00907 missing right parenthesis.

  3. If I create a column with the month value:

    CREATE TABLE Example(
DATE_VARCHAR VARCHAR2(10),
SOME_COLUMNS VARCHAR(50),
MONTH VARCHAR2(2) GENERATED ALWAYS AS
    (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL
)

PARTITION BY LIST (MONTH)
(PARTITION p1 VALUES ('01','05','09'),
PARTITION p2 VALUES ('02','06','10'),
PARTITION p3 VALUES ('03','07','11'),
PARTITION p4 VALUES ('04','08','12'));

I get the following error: ORA-12899 value too large for column %s (actual: %s, maximum: %s), due to the new column is only length 2 (and the source column is length 10); in spite of I'm doing a substring of length 2.

The only way to do this, is the 3? Exists another way to solve this?

Edit: If I do the next, it works for me:

CREATE TABLE Example(
DATE_VARCHAR VARCHAR2(10),
SOME_COLUMNS VARCHAR(50),
MONTH VARCHAR2(**10**) GENERATED ALWAYS AS
    (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL
)

PARTITION BY LIST (MONTH)
(PARTITION p1 VALUES ('01','05','09'),
PARTITION p2 VALUES ('02','06','10'),
PARTITION p3 VALUES ('03','07','11'),
PARTITION p4 VALUES ('04','08','12'));

But I don't understand why column needs have length equal 10, and not 2, having in count the substring.

Edit: Oracle version enter image description here

Error at script execution: enter image description here

Edit: Execution trough sqlplus:

SQL> CREATE TABLE tabx(
  2      DATE_VARCHAR VARCHAR2(10),
    SOME_COLUMNS VARCHAR(50),
    MONTH VARCHAR2(2) GENERATED ALWAYS AS (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL
)
PARTITION BY LIST (MONTH)
(PARTITION p1 VALUES ('01','05','09'),
PARTITION p2 VALUES ('02','06','10'),
PARTITION p3 VALUES ('03','07','11'),
PARTITION p4 VALUES ('04','08','12'),
-- need default in case of bad data format
partition others values (default)
);

-- NOTE: this WON'T work now after adding VIRTUAL column
--insert into tabx values ('2015-12-01', 'ABC');

-- but this will (must specify columns)
  3    4    5    6    7    8    9   10   11   12   13  insert into tabx(date_varchar,some_columns) values ('2016-01-01', 'XYZ');
    MONTH VARCHAR2(2) GENERATED ALWAYS AS (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL
    *
ERROR at line 4:
ORA-12899: value too large for column "MONTH" (actual: 2, maximum: 8)


SQL> SQL> SQL> SQL> SQL> SQL> insert into tabx(date_varchar,some_columns) values ('2016-01-01', 'XYZ')
            *
ERROR at line 1:
ORA-00942: table or view does not exist
Solution

  • Here is what worked for me. Its case 3 (virtual column). It techncally violates your client requirement that no new columns be created, although virtual, its still a column. Anyway:

    CREATE TABLE tabx(
    DATE_VARCHAR VARCHAR2(10),
    SOME_COLUMNS VARCHAR(50),
    MONTH VARCHAR2(2) GENERATED ALWAYS AS (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL
)
PARTITION BY LIST (MONTH)
(PARTITION p1 VALUES ('01','05','09'),
PARTITION p2 VALUES ('02','06','10'),
PARTITION p3 VALUES ('03','07','11'),
PARTITION p4 VALUES ('04','08','12'),
-- need default in case of bad data format
partition others values (default)
);

-- NOTE: this WON'T work now after adding VIRTUAL column
--insert into tabx values ('2015-12-01', 'ABC');

-- but this will (must specify columns)
insert into tabx(date_varchar,some_columns) values ('2016-01-01', 'XYZ');

commit;

    Note that the default partition is created as well.

    ADDING SCRIPT OUTPUT HERE:

    Running the above on 11.2 instance (with additional select from table) gives:

    SQL> set lines 500
SQL> drop table tabx
Table dropped.
SQL> CREATE TABLE tabx(
    DATE_VARCHAR VARCHAR2(10),
    SOME_COLUMNS VARCHAR(50),
    MONTH VARCHAR2(2) GENERATED ALWAYS AS (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL
)
PARTITION BY LIST (MONTH)
(PARTITION p1 VALUES ('01','05','09'),
PARTITION p2 VALUES ('02','06','10'),
PARTITION p3 VALUES ('03','07','11'),
PARTITION p4 VALUES ('04','08','12'),
-- need default in case of bad data format
partition others values (default)
)
Table created.
SQL> -- NOTE: this WON'T work now after adding VIRTUAL column
SQL> --insert into tabx values ('2015-12-01', 'ABC');
SQL> -- but this will (must specify columns)
SQL> insert into tabx(date_varchar,some_columns) values ('2016-01-01', 'XYZ')
1 row created.
SQL> commit
Commit complete.
SQL> select * from tabx partition(p1)

DATE_VARCHAR SOME_COLUMNS                                       MONTH
------------ -------------------------------------------------- -----
2016-01-01   XYZ                                                01   
1 row selected.

    EDIT:

    Just a guess, but if the above doesn't work for you, perhaps the issue is with multi-byte encoding (which is why I was asking about your NLS_LANG settings on client and server). Anyway, what happens if you specify the MONTH column as:

    MONTH VARCHAR2(2 CHAR) GENERATED ALWAYS AS (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL

    Here I'm specifying 2 characters instead of 2 bytes. Again, just a guess, but easy to check.