Ora2pg之分区索引转换

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);