Ora2pg分区索引转换

Oracle Local索引类型与Postgresql分区表的索引类似： 1.Oracle在分区表上创建LOCAL索引同步会在其子分区表上面创建相同数量的索引；Postgresql在分区主表创建索引,同样也在各子表上创建索引；Postgresql可以在分区子表中单创建索引。 2.在查询时,查询条件包括分区键都是先通过过滤分区键定位至具体的子分区，然后根据创建的索引在子分区内部进行索引扫描查询；

测试数据

--创建分区表(oracle) CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate) ( partition y2018 values less than (to_date('2018-01-01','yyyy-MM-dd')), partition y2019 values less than (to_date('2019-01-01','yyyy-MM-dd')) , partition y2020 values less than (to_date('2020-01-01','yyyy-MM-dd')) , partition ymaxvalue values less than (maxvalue) ); --创建分区表(postgresql) CREATE TABLE measurement( city_id numeric, logdate timestamp, peaktemp numeric, unitsales numeric, CHECK(city_id IS NOT NULL), CHECK(logdate IS NOT NULL))PARTITION BY RANGE (logdate); CREATE TABLE measurement_y2018 PARTITION OF measurement (logdate) FOR VALUES FROM (MINVALUE) TO ('2018-01-01') ; CREATE TABLE measurement_y2019 PARTITION OF measurement (logdate) FOR VALUES FROM ('2018-01-01') TO ('2019-01-01') ; CREATE TABLE measurement_y2020 PARTITION OF measurement (logdate) FOR VALUES FROM ('2019-01-01') TO ('2020-01-01') ; CREATE TABLE measurement_ymaxvalue PARTITION OF measurement (logdate) FOR VALUES FROM ('2020-01-01') TO (maxvalue)

查询条件只有分区键logdate

select * from measurement where logdate=to_date('2019-03-19','yyyy-MM-dd');--查询条件只有分区键logdate

创建Oracle Local分区索引,各个子分区创建索引

--oracle create index idx_measurement_logdate on measurement(logdate) local; --各个子分区创建索引 SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=upper('idx_measurement_logdate'); INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------- IDX_MEASUREMENT_LOGDATE Y2018 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE IDX_MEASUREMENT_LOGDATE Y2019 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE IDX_MEASUREMENT_LOGDATE Y2020 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USABLE IDX_MEASUREMENT_LOGDATE YMAXVALUE MAXVALUE USABLE --开启跟踪 set autotrace on; --oracle执行计划 SQL> explain plan for select * from measurement where logdate=to_date('2019-03-19','yyyy-MM-dd'); Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1483329978 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | B -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | PARTITION RANGE SINGLE | | 1 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MEASUREMENT | 1 | |* 3 | INDEX RANGE SCAN | IDX_MEASUREMENT_LOGDATE | 1 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("LOGDATE"=TO_DATE(' 2019-03-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss' Note ----- - dynamic sampling used for this statement (level=2) --先进入具体子分区，再通过分区子表索进行索引扫描查询

PARTITION RANGE SINGLE表示只扫描单个分区TABLE ACCESS BY LOCAL INDEX ROWID表示通过索引扫描分区内的数据

创建postgresql索引,各子表分区自动创建索引

--postgresql CREATE INDEX idx_measurement_logdate ON measurement(logdate); --各个子分区创建索引 postgres=# select * from pg_indexes where tablename like '%measurement%' order by tablename; schemaname | tablename | indexname | tablespace | indexdef ------------+-----------------------+-----------------------------------+------------+----------------------------------------------------------------- ------------------------------------- public | measurement_y2018 | measurement_y2018_logdate_idx | | CREATE INDEX measurement_y2018_logdate_idx ON public.measurement _y2018 USING btree (logdate) public | measurement_y2019 | measurement_y2019_logdate_idx | | CREATE INDEX measurement_y2019_logdate_idx ON public.measurement _y2019 USING btree (logdate) public | measurement_y2020 | measurement_y2020_logdate_idx | | CREATE INDEX measurement_y2020_logdate_idx ON public.measurement _y2020 USING btree (logdate) public | measurement_ymaxvalue | measurement_ymaxvalue_logdate_idx | | CREATE INDEX measurement_ymaxvalue_logdate_idx ON public.measure ment_ymaxvalue USING btree (logdate) (4 rows) --postgresql执行计划 postgres=# explain select * from measurement where logdate=to_date('2019-03-19','yyyy-MM-dd'); QUERY PLAN -------------------------------------------------------------------------------------------------- Append (cost=4.18..45.22 rows=12 width=104) Subplans Removed: 3 -> Bitmap Heap Scan on measurement_y2020 (cost=4.18..11.29 rows=3 width=104) Recheck Cond: (logdate = to_date('2019-03-19'::text, 'yyyy-MM-dd'::text)) -> Bitmap Index Scan on measurement_y2020_logdate_idx (cost=0.00..4.18 rows=3 width=0) Index Cond: (logdate = to_date('2019-03-19'::text, 'yyyy-MM-dd'::text)) (6 rows) --先进入measurement_y2020分区，再通过分区子表索引measurement_y2020_logdate_idx进行索引扫描查询

总结：这种情况下oracle和postgresql基本都是先扫描到具体的分区子表，再在子表里通过索引进行查询；

查询条件有区分键也有非分区键

select * from measurement where logdate=to_date('2019-03-19','yyyy-MM-dd') and city_id=1;--查询条件有区分键也有非分区键

–oracle,都会在各个子分区创建索引 create index idx_measurement_peaktemp_city_id on measurement(city_id) local;

–postgresql,同步在各个子分区创建索引 CREATE INDEX idx_measurement_peaktemp_city_id ON measurement(city_id);