分区索引替代测试

-- 创建分区表（间隔分区，自动管理分区）
CREATE TABLE sales (
    sale_id     NUMBER(10)    NOT NULL,
    product_id  NUMBER(6)     NOT NULL,
    sale_date   DATE          NOT NULL,
    amount      NUMBER(10,2)  NOT NULL,
    region      VARCHAR2(50)
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY')) -- 按天自动分区
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);


-- 插入不同日期的数据（会自动分配到不同分区）
INSERT INTO sales VALUES (1, 100, TO_DATE('2022-12-15', 'YYYY-MM-DD'), 500, 'North');
INSERT INTO sales VALUES (2, 101, TO_DATE('2023-01-05', 'YYYY-MM-DD'), 300, 'South');
INSERT INTO sales VALUES (3, 102, TO_DATE('2023-01-06', 'YYYY-MM-DD'), 700, 'East');
INSERT INTO sales VALUES (4, 103, TO_DATE('2023-01-07', 'YYYY-MM-DD'), 900, 'West');
COMMIT;



-- 创建普通全局索引
CREATE unique INDEX gidx_sales_id ON sales(sale_id,sale_date) GLOBAL;
ALTER TABLE sales ADD CONSTRAINT pk_sales PRIMARY KEY (sale_id,sale_date) USING INDEX GLOBAL  ;
CREATE unique INDEX gidx_sales_id2 ON sales(sale_id,sale_date) local parallel 2 online INVISIBLE;
alter index gidx_sales_id2 noparallel

--修改分区表
alter table SALES  drop constraint PK_SALES cascade;
alter index gidx_sales_id INVISIBLE;
alter index gidx_sales_id2 VISIBLE;
drop index gidx_sales_id;
index gidx_sales_id2 rename to gidx_sales_id;
ALTER TABLE sales ADD CONSTRAINT pk_sales PRIMARY KEY (SALE_ID, SALE_DATE) USING INDEX ;