分区表-RANGE
range 分区表
2023-09-14 09:01:14 时间
1、创建普通表
create table p_temp(id number,init_date date);
2、往普通表中插入数据
set serveroutput on declare init_date date := to_date('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); begin for i in 0..364 loop insert into p_temp values(i + 1 , init_date + i); end loop; commit; end; /
3、
创建分区表
分区表的管理与维护
create table p_tab(id number,trn_date date) partition by range(trn_date) ( partition p_1 values less than(to_date('2014-02-01','YYYY-MM-DD')), partition p_2 values less than(to_date('2014-03-01','YYYY-MM-DD')), partition p_3 values less than(to_date('2014-04-01','YYYY-MM-DD')), partition p_4 values less than(to_date('2014-05-01','YYYY-MM-DD')), partition p_5 values less than(to_date('2014-06-01','YYYY-MM-DD')), partition p_6 values less than(to_date('2014-07-01','YYYY-MM-DD')), partition p_7 values less than(to_date('2014-08-01','YYYY-MM-DD')), partition p_8 values less than(to_date('2014-09-01','YYYY-MM-DD')), partition p_9 values less than(to_date('2014-10-01','YYYY-MM-DD')), partition p_10 values less than(to_date('2014-11-01','YYYY-MM-DD')), partition p_11 values less than(to_date('2014-12-01','YYYY-MM-DD')), partition p_12 values less than(to_date('2015-01-01','YYYY-MM-DD')), partition p_maxvalue values less than(maxvalue) );
4、向分区表中插入数据
SQL> insert into p_tab select * from p_temp; 365 rows created. SQL> commit; Commit complete.
5、查询某个分区表中的数据
select * from p_tab partition(p_1); select * from p_tab partition(p_2); select * from p_tab partition(p_3); select * from p_tab partition(p_4); select * from p_tab partition(p_5); select * from p_tab partition(p_6); select * from p_tab partition(p_7); select * from p_tab partition(p_8); select * from p_tab partition(p_9); select * from p_tab partition(p_10); select * from p_tab partition(p_11); select * from p_tab partition(p_12); select * from p_tab partition(p_maxvalue);
6、查看分区信息
col table_name for a30 col partition_name for a30 select table_name,partition_name from user_tab_partitions where table_name = 'P_TAB';
7、删除普通表、分区表
drop table p_temp purge; drop table p_tab purge;
相关文章
- 报SQL异常Parameter index out of range (1 > number of parameters, which is 0).「建议收藏」
- Postgresql分区表大量实例与分区建议(LIST / RANGE / HASH / 多级混合分区)
- tf.range()
- SAP QM QS21创建检验特性,报错- Number range interval 01 for number range object QMERKMALE
- ORA-19629: no files in specified archived log SCN range ORACLE 报错 故障修复 远程处理
- ORA-19650: Offline-range record RECID string STAMP string in file string has SCN string ORACLE 报错 故障修复 远程处理
- ORA-24122: invalid block range specification ORACLE 报错 故障修复 远程处理
- ORA-39098: Worker process received data objects while loading metadata. Invalid process order range is string..string ORACLE 报错 故障修复 远程处理
- ORA-40003: wordsize must be in the range string – string for BLAST-P ORACLE 报错 故障修复 远程处理
- MySQL Error number: 3587; Symbol: ER_WINDOW_RANGE_FRAME_ORDER_TYPE; SQLSTATE: HY000 报错 故障修复 远程处理
- ORA-00081: address range [string, string) is not readable ORACLE 报错 故障修复 远程处理
- ORA-00374: parameter db_block_size = string invalid ; must be a multiple of string in the range [string..string] ORACLE 报错 故障修复 远程处理
- ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index ORACLE 报错 故障修复 远程处理
- Go语言for range(键值循环)
- 新型穿墙监控雷达Range-R:让你的隐私无所遁形
- Oracle数据库中的范围管理(oracle range)
- Prototype使用指南之range.js