Partitioning a Oracle table using a function over a column

Question

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.

I can't ALTER my table to add the partitioning (Oracle requirements), so I create a backup table, to backup the data.
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.

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

Error at script execution:

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

have you looked at range partitioning, where the bounds of each range would be the first/last days of the month? if so, why wouldn't it work? — SlimsGhost, Aug 31 '16 at 16:09
I have looked this, but the problem is that I want fixed partitions that don't depend of the year — Angel Doza, Aug 31 '16 at 16:14
If I do range partitioning, I have to specify the year in the structure table. — Angel Doza, Aug 31 '16 at 16:20
For month (without year), it seems like you can use a "virtual column" to do this (same logic as creating a physical month column, but without the "physical" part) - see if the first answer here helps: http://stackoverflow.com/questions/14202945/oracle-partition-table-by-month — SlimsGhost, Aug 31 '16 at 16:23
A virtual column can work, but its still adding a column, which would violate rule 2 (no new columns per client requirements). If virtual is ok, I can add an example — tbone, Aug 31 '16 at 17:18
In case of virtual column, should it be like the 3 case? If yes, I get the error of the value, unless that I define the column MONTH like *VARCHAR2(10) GENERATED ALWAYS AS (SUBSTR(DATE_VARCHAR,6,2)) VIRTUAL )* — Angel Doza, Aug 31 '16 at 17:59
What problem are you actually trying to solve with this partitioning strategy? — BobC, Jan 06 '17 at 17:42
@BobC the problem was fixed using 2 characters instead of 2 bytes at the column definition, thanks — Angel Doza, Jan 06 '17 at 18:16
@Angel, my question was not really about the error per se, but rather what is the problem you re tying to solve at a high level. — BobC, Jan 06 '17 at 18:41
@BobC I wanted to create a partitioning over a existing table without have to create a backup table; but Oracle, don't allow me this. So I created a backup table, truncated the original table to could modify the structure and restore the data from the backup — Angel Doza, Jan 06 '17 at 19:01
@BobC because I needed to purge the table monthly due to the high volume of data (client requirements) — Angel Doza, Jan 06 '17 at 19:28

tbone · Accepted Answer · 2016-09-01T19:51:25.867

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.

It is the 3° case, when I execute it, I give the following error: *ORA-12899 value too large for column %s (actual: %s, maximum: %s)*, due to the format of **Month** column (VARCHAR2(2) instead of VARCHAR2(10) like DATE_VARCHAR — Angel Doza, Aug 31 '16 at 20:58
The code I posted works just fine for me (Oracle 11.2). Can you run my code as a script in your dev environment? — tbone, Sep 01 '16 at 00:00
Informe de error - Error SQL: ORA-12899: el valor es demasiado grande para la columna "MONTH" (real: 2, máximo: 8) 12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)" *Cause: An attempt was made to insert or update a column with a value which is too wide for the width of the destination column. The name of the column is given, along with the actual width of the value, and the maximum allowed width of the column. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes. — Angel Doza, Sep 01 '16 at 16:03
Works fine for me, I've updated my answer with the script output. — tbone, Sep 01 '16 at 16:50
My version is: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production — Angel Doza, Sep 01 '16 at 19:07
Weird. Did you run that as a script? Just a guess, but what are your NLS_LANG setting for the database and your client machine? For the database, use: SELECT USERENV ('language') FROM DUAL; For the client, if using windows, you need to pull it from the registry (use: regedit on cmd line). See [here](http://stackoverflow.com/questions/11522800/how-do-i-check-the-nls-lang-of-the-client) for more. — tbone, Sep 01 '16 at 19:37

score 1 · Answer 2 · answered Apr 03 '20 at 14:36

Not sure if you have gone to 12c yet on your database. But, if you have then you can use a virtual invisible column to achieve what you are trying to do without affecting the database.

CREATE TABLE example
(
    date_varchar    VARCHAR2(10)
  , some_columns    VARCHAR(50)
  , month           NUMBER(2, 0) INVISIBLE GENERATED ALWAYS AS(EXTRACT(MONTH FROM TO_DATE(date_varchar, 'YYYY-MM-DD'))) VIRTUAL
)
PARTITION BY LIST(month)
(
    PARTITION p1 VALUES (1, 5, 9)
  , PARTITION p2 VALUES (2, 6, 10)
  , PARTITION p3 VALUES (3, 7, 11)
  , PARTITION p4 VALUES (4, 8, 12)
);
No rows affected (0.023 seconds)

Because the column is invisible it isn't required for an insert.

INSERT INTO example VALUES ('2015-12-01', 'ABC');
1 row affected (0.056 seconds)

And the row gets put into the right partition.

SELECT * FROM example PARTITION(p4);
+--------------+--------------+
| DATE_VARCHAR | SOME_COLUMNS |
+--------------+--------------+
| 2015-12-01   | ABC          |
+--------------+--------------+
1 row selected (0.016 seconds)

Partitioning a Oracle table using a function over a column

2 Answers2