oracle通过分区优化查询性能

nealzhi

2021-12-31

oracle

255

oracle通过分区优化查询性能

范围分区方式

固定分区然后通过定时任务来拓展分区

    // ddl 创建语句 +
    partition by range (CREATE_TIME)  {
       partition PART_22_01 values less than(to_date('2022-01-01','yyyy-mm-dd')),  
       partition PART_22_02 values less than(to_date('2022-02-01','yyyy-mm-dd')), 
       partition PART_22_03 values less than(to_date('2022-03-01','yyyy-mm-dd')), 
       partition PART_22_04 values less than(to_date('2022-04-01','yyyy-mm-dd')), 
       partition PART_22_05 values less than(to_date('2022-05-01','yyyy-mm-dd')), 
       partition PART_22_06 values less than(to_date('2022-06-01','yyyy-mm-dd')), 
       partition PART_22_07 values less than(to_date('2022-07-01','yyyy-mm-dd')), 
       partition PART_22_08 values less than(to_date('2022-08-01','yyyy-mm-dd')), 
       partition PART_22_09 values less than(to_date('2022-09-01','yyyy-mm-dd')), 
       partition PART_22_10 values less than(to_date('2022-10-01','yyyy-mm-dd')), 
       partition PART_22_11 values less than(to_date('2022-11-01','yyyy-mm-dd')), 
       partition PART_22_12 values less than(to_date('2022-12-01','yyyy-mm-dd'))
     }
     // 拓展分区(在定时任务中执行)
     ALTER TABLE 表名 ADD partition PART_23_02 VALUES less than ( to_date( '2023-01-01', 'yyyy-mm-dd' ) );

自动拓展分区(oracle会根据时间自动拓展分区，但是分区名称将随机系统生成)

按年分区

// ddl 创建表+
partition by range (CREATE_TIME) interval (numtoyminterval(1, 'year'))
(partition  values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

按月分区

// ddl 创建表+
partition by range (CREATE_TIME)  interval (numtoyminterval(1, 'month'))
(  
       partition values less than(to_date('2022-01-01','yyyy-mm-dd'))
 )

按周分区

partition by range (CREATE_TIME) interval (numtodsinterval(7, 'day'))
(partition values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

按天分区

partition by range (CREATE_TIME) interval (numtodsinterval(1, 'day'))
(partition values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

查看自己账号下分区相关信息

select * from user_tab_partitions;

SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS

select * from user_part_tables;

select * from user_tab_partitions;

select * from user_ind_partitions;