--创建表结构(18毫秒) CREATE TABLE "T1"("DATE_" DATE,"C1" VARCHAR(10),"C2" VARCHAR(30),"C3" VARCHAR(30),"C4" VARCHAR(100),"C5" VARCHAR(30))PARTITION BY LIST("DATE_")( PARTITION "P20230101" VALUES(DATE'2023-01-01') , PARTITION "P20230102" VALUES(DATE'2023-01-02') , PARTITION "P20230103" VALUES(DATE'2023-01-03') , PARTITION "P20230104" VALUES(DATE'2023-01-04') , PARTITION "P20230105" VALUES(DATE'2023-01-05') , PARTITION "P20230106" VALUES(DATE'2023-01-06') , PARTITION "P20230107" VALUES(DATE'2023-01-07') , PARTITION "P20230108" VALUES(DATE'2023-01-08') , PARTITION "P20230109" VALUES(DATE'2023-01-09') , PARTITION "P20230110" VALUES(DATE'2023-01-10') , PARTITION "P20230111" VALUES(DATE'2023-01-11') , PARTITION "P20230112" VALUES(DATE'2023-01-12') , PARTITION "P20230113" VALUES(DATE'2023-01-13') , PARTITION "P20230114" VALUES(DATE'2023-01-14') , PARTITION "P20230115" VALUES(DATE'2023-01-15')); --插入数据(450万 49秒) declare vdate date; begin vdate:=to_date('20230101','yyyymmdd'); for i in 1..15 loop insert into T1 select vdate DATE_, 'CORE' C1, dbms_random.string ('a', 15) C2, dbms_random.string ('x', 20) C3, dbms_random.string ('x', 10) C4, '0101' C5 from dual connect by level <= 300000; commit; vdate:=vdate+1; end loop; end; --创建局部索引(8秒) create index idx_T1_C4 on T1(C4); --创建全局索引(10秒) create index idx_T1_C4 on T1(C4) global; --查询SQL(有索引时查询,无论是全局索引还是局部索引,性能都很好,差别不大) select * from T1 where C4 = 'J08O8HGFEG'; 无索引 2秒 局部索引 22毫秒 全局索引 9毫秒 --清空子分区 alter table T1 truncate partition P20230115; 局部索引 10毫秒 索引有效 对子分区的DDL操作,速度非常快,不会有索引重建的耗时,几乎感觉不到影响 全局索引 9秒 索引有效 对子分区的DDL操作,耗时很长,需重建全局索引,此过程耗时大,锁表 --结论 局部索引只索引和维护单个分区上的数据,全局索引维护全表数据,开销比局部索引大(优先局部索引,数据维护简单,DDL操作快)
01-24
5541