regexdatabase-partitioningoracle19coracle-database

Has anyone been able to use values regexp_like in partitioning by list in Oracle 19c?


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.

  1. I have successfully created the base table by partition by range (required_date).
  2. I want to create a partitioned materialized_view with the primary partition, required_date, and a subpartition of c_code.
  3. I am open to create the materialized_view partitioned by c_code then subpartitioned by required_date.

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.


Solution

  • 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.