使用本地索引提速5倍+

记录：NO.252

场景

在Oracle数据库一张数据表T_SENSOR_DATA，每日增加1000万条数据。使用日期字段GATHER_DATE建立分区表。

全局索引

初期，由于业务使用频繁，为了提高查询速度，在T_SENSOR_DATA建立一个联合索引和一个联合主键，均使用了全局索引。全局索引建表语句如下：

create table T_SENSOR_DATA_GLOBAL
(
  id            NUMBER(12) not null,
  gather_date   DATE not null,
  position_dir  NUMBER(2) not null,
  gather_time   DATE,
  t1        NUMBER(12,2),
  t2        NUMBER(12,2),
  t3        NUMBER(12,2),
  t4        NUMBER(12,2),
  t5        NUMBER(12,2),
  t6        NUMBER(12,2)
)
partition by range (GATHER_DATE)
(
  partition T20210701 values less than 
   (TO_DATE(' 2021-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SENSOR_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition T20210702 values less than 
   (TO_DATE(' 2021-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SENSOR_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    )
  );
create index IDX_T_SENSOR_DATA_GLOBAL_ID on T_SENSOR_DATA_GLOBAL (ID,GATHER_DATE, T1);
alter table T_SENSOR_DATA_GLOBAL
  add constraint PK_T_SENSOR_DATA_GLOBAL primary key (ID,GATHER_DATE, POSITION_DIR);

本地索引

数据表T_SENSOR_DATA在连续运行一周后，表内存储7个分区数据共计7000万+。每天插入1000万数据耗时15分钟以上不等。甚至在任务高峰期时，插入1000万数据超过120分钟。已经影响到下游任务开展。为此，经过验证，在T_SENSOR_DATA建立的联合索引和联合主键由全局索引变更为本地索引。插入1000万数据维持在3分钟内。持续运行2周均保持在3分钟内。本地索引建表语句如下：

create table T_SENSOR_DATA_LOCAL
(
  id            NUMBER(12) not null,
  gather_date   DATE not null,
  position_dir  NUMBER(2) not null,
  gather_time   DATE,
  t1        NUMBER(12,2),
  t2        NUMBER(12,2),
  t3        NUMBER(12,2),
  t4        NUMBER(12,2),
  t5        NUMBER(12,2),
  t6        NUMBER(12,2)
)
partition by range (GATHER_DATE)
(
  partition T20210701 values less than 
   (TO_DATE(' 2021-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SENSOR_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition T20210702 values less than 
   (TO_DATE(' 2021-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SENSOR_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    )
  );
create index IDX_T_SENSOR_DATA_LOCAL_ID on T_SENSOR_DATA_LOCAL (ID, GATHER_DATE, T1)
  local;
alter table T_SENSOR_DATA_LOCAL
  add constraint PK_T_SENSOR_DATA_LOCAL primary key (ID, GATHER_DATE, POSITION_DIR)
  using index 
  local;

常用SQL

本例中1000万级别数据全局索引和本地索引，插入速度相差5倍+。因此记录排查过程中使用过的SQL。

-- 查看表列字段信息
select usc.column_name from user_tab_columns usc where table_name='T_SENSOR_DATA_LOCAL';
-- 查看表注释信息
select * from user_tab_comments usc where table_name='T_SENSOR_DATA_LOCAL';
-- 查看表字段注释信息
select * from user_col_comments usc where table_name='T_SENSOR_DATA_LOCAL';
-- 查看表
select * from user_tables;
select * from all_tables  where owner='SENSOR_DEMO';
select * from dba_tables  where owner='SENSOR_DEMO';
-- 查看分区表分区信息
select * from dba_tab_partitions where table_name='T_SENSOR_DATA_LOCAL';
-- 查看分区表索引信息
select * from dba_ind_partitions where index_name='PK_T_SENSOR_DATA_LOCAL';
-- 查看表的索引信息
select * from dba_indexes where table_name='T_SENSOR_DATA_LOCAL';
-- 查看表空间大小分布
select * from dba_data_files where tablespace_name='SENSOR_DATA';
select * from dba_segments where segment_name='T_SENSOR_DATA_LOCAL';
select * from dba_free_space where tablespace_name='SENSOR_DATA';
-- 查看视图
select * from dba_views aa where  aa.view_name like 'DBA_%';
-- 查看分区表字段
select * from dba_part_key_columns where name='PK_T_SENSOR_DATA_LOCAL';
-- 查看索引使用的表字段
select * from dba_ind_columns where table_name='T_SENSOR_DATA_LOCAL';

以上，感谢。