ORACLE 根据某个字段固定值进行分区

DROP TABLE TEST;
-- 根据产品PRODUCT_ID 进行分区
create table TEST
(
    PHONE_NO   VARCHAR2(20) NOT NULL,
    PRODUCT_ID VARCHAR2(30) NOT NULL,
    CHANNEL_ID VARCHAR2(20),
    SCORE      NUMBER(20, 4),
    REGION_ID  VARCHAR2(20)
)
    partition by list
(
    PRODUCT_ID
)
(
    --partition PLAN_20180802142529717 values('20180802142529717'),
    --partition PLAN_20170918153535905 values('20170918153535905'),
    --partition PLAN_20170918152012716 values('20170918152012716'),
    --partition PLAN_20170914154819661 values('20170914154819661'),
    partition p_other values (default)
)
;

-- 判断分区是否存在, 没有分区的是不是得查20亿数据？
select table_name, partition_name, high_value, tablespace_name
from all_tab_partitions a
where a.table_owner = '{SCHEMAL}'
  and table_name = UPPER('TEST')
  and partition_name = UPPER('PLAN_20180802142529717');
-- 创建分区
alter table TEST
    split partition p_other VALUES ('20180802142529717')
        into (partition PLAN_20180802142529717,partition p_other);