Oracle分区管理

最新推荐文章于 2022-04-25 17:31:35 发布

macower

最新推荐文章于 2022-04-25 17:31:35 发布

阅读量546

点赞数

分类专栏： Oracle 文章标签： oracle sql date less insert user

本文链接：https://blog.csdn.net/voyage_mh1987/article/details/5871926

版权

Oracle 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

基本理论可以参考以下这篇文章

http://hi.baidu.com/injava/blog/item/3928c23f28f182cb7c1e716b.html

讲述的很好

下面就是具体的SQL

SQL> --范围分区 SQL> CREATE TABLE SALES2 2 ( 3 PRODUCT_ID VARCHAR2(5), 4 SALES_DATE DATE NOT NULL, 5 SALES_COST NUMBER(10) 6 ) 7 PARTITION BY RANGE (SALES_DATE) 8 ( 9 PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01', 'YYYY-MM-DD')), 10 PARTITION P2 VALUES LESS THAN (TO_DATE('2004-01-01', 'YYYY-MM-DD')), 11 PARTITION P3 VALUES LESS THAN (MAXVALUE) 12 ); 表已创建。 SQL> --散列分区 SQL> CREATE TABLE EMPLOYEE 2 ( 3 EMP_ID NUMBER(4), 4 EMP_NAME VARCHAR2(14), 5 EMP_ADDRESS VARCHAR2(15), 6 DEPARTMENT VARCHAR2(10) 7 ) 8 PARTITION BY HASH (DEPARTMENT) PARTITIONS 4; 表已创建。 SQL> CREATE TABLE MY_EMP 2 ( 3 EMPNO NUMBER(4), 4 ENAME VARCHAR2(15) 5 ) 6 PARTITION BY HASH (EMPNO) 7 ( 8 PARTITION part_1, 9 PARTITION part_2 10 ); 表已创建。 SQL> --复合分区 SQL> CREATE TABLE SALES3 2 ( 3 PRODUCT_ID VARCHAR2 (5), 4 SALES_DATE DATE NOT NULL, 5 SALES_COST NUMBER (10) 6 ) 7 PARTITION BY RANGE (SALES_DATE) 8 SUBPARTITION BY HASH (PRODUCT_ID) 9 SUBPARTITIONS 5 10 ( 11 PARTITION P1 VALUES LESS THAN (DATE '2001-04-01'), 12 PARTITION P2 VALUES LESS THAN (DATE '2001-07-01'), 13 PARTITION P3 VALUES LESS THAN (DATE '2001-09-01'), 14 PARTITION P4 VALUES LESS THAN (MAXVALUE) 15 ); 表已创建。 SQL> ---列表分区 SQL> CREATE TABLE EMPLOYER 2 ( 3 EMP_ID NUMBER(4), 4 EMP_NAME VARCHAR2(14), 5 EMP_ADDRESS VARCHAR2(15), 6 DEPARTMENT VARCHAR2(10) 7 ) 8 PARTITION BY LIST (EMP_ADDRESS) 9 ( 10 PARTITION NORTH VALUES ('芝加哥'), 11 PARTITION WEST VALUES ('旧金山', '洛杉矶'), 12 PARTITION SOUTH VALUES ('亚特兰大', '达拉斯', '休斯顿'), 13 PARTITION EAST VALUES ('纽约', '波士顿') 14 ); 表已创建。 SQL> INSERT INTO SALES3 VALUES ('P001', '02-3月-2001', 2000); 已创建 1 行。 SQL> INSERT INTO SALES3 VALUES ('P002', '10-5月-2001', 2508); 已创建 1 行。 SQL> INSERT INTO SALES3 VALUES ('P003', '05-7月-2001', 780); 已创建 1 行。 SQL> INSERT INTO SALES3 VALUES ('P004', '12-9月-2001', 1080); 已创建 1 行。 SQL> COMMIT; 提交完成。 SQL> SQL> SELECT * FROM SALES3 PARTITION (P3); --在分区P3中查找 PRODU SALES_DATE SALES_COST ----- -------------- ---------- P003 05-7月 -01 780

查看具体的分区 SQL> ed 已写入 file afiedt.buf 1 select a.table_name,a.partitioned,b.partition_count,c.column_name 2 from user_tables a, user_part_tables b, user_part_key_columns c 3 where a.table_name='EMPLOYEE' 4 and b.table_name='EMPLOYEE' 5* and c.name='EMPLOYEE' SQL> / TABLE_NAME PAR PARTITION_COUNT COLUMN_NAME -------------------- --- --------------- -------------------- EMPLOYEE YES 4 DEPARTMENT

查看已使用的每个分区的大小： SQL> ed 已写入 file afiedt.buf 1 select segment_name,partition_name from user_segments 2* where segment_name ='EMPLOYEE' order by 2 desc SQL> / SEGMENT_NAME PARTITION_NAME --------------------------------------------------------------------------------- --------------- ------ EMPLOYEE SYS_P144 EMPLOYEE SYS_P143 EMPLOYEE SYS_P142 EMPLOYEE SYS_P141

查看整个表的大小： SQL> ed 已写入 file afiedt.buf 1 select segment_name,sum(bytes/1024/1024) from user_segments 2* where segment_name ='EMPLOYEE' group by segment_name SQL> / SEGMENT_NAME ------------------------------------------------------------------- SUM(BYTES/1024/1024) -------------------- EMPLOYEE .25 查看表的记录数： SQL> set timing on SQL> select count(*) from employee; COUNT(*) ---------- 0 已用时间: 00: 00: 00.01 2. 需要考虑的几个方面 1）创建的索引需要几个G的磁盘空间。 2）创建索引需要排序，使用pga_aggregate_target，要把这个值从 200M加大到2G。 3）如果内存不够，需要temp表空间，则要把temp表空间加大到8G——itpub上有一个帖子说过，15亿条记录用了34G空间。 4）在线创建，时间会比较长。讨论后，停止这个表的操作，非online创建。

3. 实际操作过程（假设有一张这样的表STAT_SUBMIT_CENTER，下面是一个牛人写的，写的很好所以搬过来） 1）数据文件够，不扩展；temp数据文件扩展： alter database tempfile '/bgdata/oracle/temp01.dbf' resize 8192m; 2）在workarea_size_policy=AUTO的情况下，改 pga_aggregate_target=2048m。对于串行操作，一个session能使用的 pga=MIN(5%PGA_AGGREGATE_TARGET,100MB)，这样可以使得pga用到最大的值： alter system set pga_aggregate_target=2048m; 3）因为这是一个比较长的过程，所以写脚本让后台运行： nohup time createind.sh & vi createind.sh #!/bin/sh sqlplus user/password < create index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORDTIME) local; exit EOF 4）创建过程中可以观察v$sort_segment，v$sort_usage看排序情况： nohup time createind.sh & vi createind.sh #!/bin/sh sqlplus user/password < create index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORDTIME) local; exit EOF 5）创建完成后，把tempfile和pga_aggregate_target改回原值： alter database tempfile '/bgdata/oracle/temp01.dbf' resize 4096m; alter system set pga_aggregate_target=500m; 4. 实际创建过程中观察到的情况 1）开始之前： SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment; TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ------------------------------- ------------- ------------ ----------- ----------- TEMP 0 431360 0 431360 SQL> select * from v$sort_usage; no rows selected 2）创建之初，抓到这么一条sql： insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,st ime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spar e1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14, :15,:16, :17) 3）然后v$sort_segment.USED_BLOCKS变大，v$sort_usage.BLOCKS变大，一直增长到： SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment; TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ------------------------------- ------------- ------------ ----------- ----------- TEMP 1 431360 46720 384640 SQL> select * from v$sort_usage; USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH ------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO# ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ---------- DPC DPC 00000003974CFFB0 6134 0000000399CAB288 1254950678 TEMP TEMPORARY SORT 201 431113 365 46720 1 这个过程中抓到的sql为： select file# from file$ where ts#=:1 4）v$sort_segment.USED_BLOCKS变为 0，v$sort_usage.BLOCKS变为0。 5）重复3，4两步，估计这个是创建一个分区的索引。需要解释一下的是，上面的sql只是我随机抓到的运行时间比较长的，整个create index过程会复杂很多，具体怎么样可以用sqltrace跟踪。这里主要看的是temp表空间的使用情况。同时，在创建的过程中： SQL> select segment_name,partition_name from user_segments where segment_name='IDX_SUBMIT_RECORDTIME'; no rows selected SQL> select index_name,partition_name from user_ind_partitions where INDEX_NAME='IDX_SUBMIT_RECORDTIME'; no rows selected 当时忘了查user_segments中其实是有一个segment_name为一串数字的记录，那个才是正在创建的索引；如果这个事务失败了，将回滚。最后耗时99分钟完成。 5. 创建完成后分析索引但是接下来还有一件事。创建完成后要分析索引，否则就是走了索引，查询速度也会很慢。 SQL> explain plan for select count(*) from stat_submit_center where recordtime>trunc(sysdate); Explained. SQL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 4 | | | | 1 | SORT AGGREGATE | | 1 | 9 | | | | | 2 | PARTITION RANGE ALL | | | | | 1 | 50 | |* 3 | INDEX FAST FULL SCAN| IDX_SUBMIT_RECORDTIME | 8878K| 76M| 4 | 1 | 50 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("STAT_SUBMIT_CENTER"."RECORDTIME">TRUNC(SYSDATE@!)) Note: cpu costing is off 16 rows selected. SQL> set autotrace on explain SQL> set timing on SQL> select count(*) from stat_submit_center where recordtime>trunc(sysdate); aa^Cselect count(*) from stat_submit_center where recordtime>trunc(sysdate) * ERROR at line 1: ORA-01013: user requested cancel of current operation Elapsed: 00:11:49.85 SQL> SQL> set autotrace off 上面可以看到，因为没有分析索引，虽然它走的是新建的 IDX_SUBMIT_RECORDTIME索引，但是查询速度很慢，10分钟后也没有结果。下面我们分析一下： SQL> Analyze index IDX_SUBMIT_RECORDTIME estimate statistics; Index analyzed. Elapsed: 00:00:06.84 SQL> set autotrace on explain SQL> select count(*) from stat_submit_center where recordtime>trunc(sysdate); COUNT(*) ---------- 926736 Elapsed: 00:00:05.37 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4360 Card=1 Bytes=9) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 INDEX (RANGE SCAN) OF 'IDX_SUBMIT_RECORDTIME' (NON-UNI QUE) (Cost=4360 Card=8878740 Bytes=79908660) SQL> set autotrace off 索引分析之后，查询时间为5分钟左右，效率大大提高。至此，完成全部操作。