I am attempting to partition a table based on a varchar2 column. I would like to partition it by 1) all alpha characters, 2) all numeric characters and 3) both alpha and numeric characters.
column to partition on example "A3F21", "AZEDF", "02134". Each of these would land in a different partition.
Regardless what I try, I get an error:
ORA-14308: partition bound element must be one of: string, datetime or interval literal, number or NULL.
I have attempted to just partition via c_code and I still get the error, so I know it is in how I am writing "VALUES (regexp_like(C_CODE,xxxxx)).
My Code:
CREATE MATERIALIZED VIEW ppirs.c_report_mv
PARALLEL PARTITION BY RANGE (REQUIRED_DATE)
SUBPARTITION BY LIST(C_CODE)
( PARTITION pre2015
VALUES LESS THAN (TO_DATE('12/31/2014','MM/DD/YYYY'))
TABLESPACE users
PCTFREE 10
INITRANS 1
MAXTRANS 255
NOLOGGING
(SUBPARTITION pre2015a VALUES (regexp_like(C_CODE,'[:alpha:]')),
SUBPARTITION pre2015n VALUES (regexp_like(C_CODE,'[:numeric:]')),
SUBPARTITION pre2015b VALUES (regexp_like(C_CODE,'[:alnum:]'))),
PARTITION p2015
VALUES LESS THAN (TO_DATE('12/31/2015','MM/DD/YYYY'))
TABLESPACE users
PCTFREE 10
INITRANS 1
MAXTRANS 255
NOLOGGING
(SUBPARTITION p2015a VALUES (regexp_like(C_CODE,'[:alpha:]')),
SUBPARTITION p2015n VALUES (regexp_like(C_CODE,'[:numeric:]')),
SUBPARTITION p2015b VALUES (regexp_like(_CODE,'[:alnum:]'))
)
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT C_CODE,
REQUIRED_DATE,
CANCEL_DATE,
SHIP_DATE,
RECEIPT_DATE,
ADDED_DATE,
UPDATE_DATE
FROM contract
Has anyone used regexp_like in list partitioning?
I have tried just creating the partitions with the c_code column using regexp_like and I am still getting the ORA-14308 error.
You can do this with a virtual column that implements whatever complex logic you need; the virtual column can then be specified as the partition key. You can optionally hide it from select *
and insert values
by marking it invisible
:
Example:
create table tmp (c_code varchar2(10),
c_code_type invisible as
(case when (regexp_like(C_CODE,'^[[:alpha:]]+$')) then 'alpha'
when (regexp_like(C_CODE,'^[[:digit:]]+$')) then 'numeric'
when (regexp_like(C_CODE,'^[[:alnum:]]+$')) then 'alnum'
else 'other'
end) virtual)
partition by list(c_code_type)
(partition p_alpha values ('alpha'),
partition p_numeric values ('numeric'),
partition p_alnum values ('alnum'),
partition p_other values ('other')
);
Testing:
insert into tmp values ('A3F21');
insert into tmp values ('AZEDF');
insert into tmp values ('02134');
SELECT * FROM tmp partition (p_alpha);
SELECT * FROM tmp partition (p_numeric);
SELECT * FROM tmp partition (p_alnum);
-- shows one row in each
You'll obviously need to adapt this to your table and composite range-list with subpartitions, but this demonstrates the concept. I also had to change your regexp a bit to get it to work like I think you're wanting.