oracle分区及分区索引partition_partition index_维护(一)

最新推荐文章于 2021-04-03 09:49:17 发布

clg10051

最新推荐文章于 2021-04-03 09:49:17 发布

阅读量378

点赞数

文章标签：数据库

-- Create table
create table PORTAL_REQUESTWS_HISTORY_01
(
id                 VARCHAR2(32) not null,
call_time          TIMESTAMP(6),
call_ip            VARCHAR2(200),
xml_contents       CLOB,
response_result    VARCHAR2(200),
response_content   CLOB,
response_time      TIMESTAMP(6),
transactionid      VARCHAR2(200),
actioncode         VARCHAR2(200),
buscode            VARCHAR2(200),
servicecode        VARCHAR2(200),
servicecontractver VARCHAR2(200),
servicelevel       VARCHAR2(200),
srcorgid           VARCHAR2(200),
srcsysid           VARCHAR2(200),
srcsyssign         VARCHAR2(200),
dstorgid           VARCHAR2(200),
dstsysid           VARCHAR2(200),
reqtime            TIMESTAMP(6),
createdateppm      VARCHAR2(200),
disable_opid       VARCHAR2(200),
disable_date       TIMESTAMP(6),
create_opid        VARCHAR2(200),
create_date        TIMESTAMP(6),
rec_status         NUMBER(1) not null,
remark1            VARCHAR2(200),
remark2            VARCHAR2(200),
remark3            VARCHAR2(200),
remark4            VARCHAR2(200),
remark5            VARCHAR2(200),
remark6            VARCHAR2(200),
remark7            VARCHAR2(200),
remark8            VARCHAR2(200),
remark9            VARCHAR2(200),
remark10           VARCHAR2(200),
remark11           VARCHAR2(200),
remark12           VARCHAR2(200),
remark13           VARCHAR2(200),
remark14           VARCHAR2(200),
remark15           VARCHAR2(200)
)
tablespace TBS_ZXKF
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Create/Recreate indexes
create index P_REQUESTWS_HIS_01_REMARK13 on PORTAL_REQUESTWS_HISTORY_01 (REMARK13)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index P_REQUESTWS_HIS_01_REQTIME on PORTAL_REQUESTWS_HISTORY_01 (REQTIME)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index P_REQUESTWS_HIS_01_SVER on PORTAL_REQUESTWS_HISTORY_01 (SERVICECONTRACTVER)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index P_REQUESTWS_HIS_01_TID on PORTAL_REQUESTWS_HISTORY_01 (TRANSACTIONID)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table PORTAL_REQUESTWS_HISTORY_01
add constraint PK_PORTAL_REQUESTWS_HISTORY_01 primary key (ID)
using index
tablespace TBS_ZXKF
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Grant/Revoke object privileges
grant select on PORTAL_REQUESTWS_HISTORY_01 to APP_ZUOQY;
grant select on PORTAL_REQUESTWS_HISTORY_01 to WT_APP;

---1,分区测试
SQL> show user
USER 为 "SCOTT"

----------创始时间分区表
SQL> create table t_test_partition_1(id int,reqtime timestamp(6),call_ip varchar
2(200))
2 partition by range(reqtime)
3 (
4   partition p1 values less than (to_timestamp('2013-01-07 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
5   partition p2 values less than (to_timestamp('2013-01-14 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
6   partition p3 values less than (to_timestamp('2013-01-21 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
7   partition p4 values less than (to_timestamp('2013-01-28 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
8   partition p5 values less than (to_timestamp('2013-01-31 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff'))
9 );

表已创建。

SQL>

-------确认是否分区成功
SQL> select table_name,partitioned from user_tables where table_name='T_TES
TITION_1';

TABLE_NAME PAR
------------------------------ ---
T_TEST_PARTITION_1 YES

SQL> desc dba_tab_partitions;
名称是否为空? 类型
----------------------------------------- -------- -----------------------

TABLE_OWNER                                        VARCHAR2(30)
TABLE_NAME                                         VARCHAR2(30)
COMPOSITE                                          VARCHAR2(3)
PARTITION_NAME                                     VARCHAR2(30)
SUBPARTITION_COUNT                                 NUMBER
HIGH_VALUE                                         LONG
HIGH_VALUE_LENGTH                                  NUMBER
PARTITION_POSITION                                 NUMBER
TABLESPACE_NAME                                    VARCHAR2(30)
PCT_FREE                                           NUMBER
PCT_USED                                           NUMBER
INI_TRANS                                          NUMBER
MAX_TRANS                                          NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENT                                         NUMBER
MAX_EXTENT                                         NUMBER
MAX_SIZE                                           NUMBER
PCT_INCREASE                                       NUMBER
FREELISTS                                          NUMBER
FREELIST_GROUPS                                    NUMBER
LOGGING                                            VARCHAR2(7)
COMPRESSION                                        VARCHAR2(8)
COMPRESS_FOR                                       VARCHAR2(12)
NUM_ROWS                                           NUMBER
BLOCKS                                             NUMBER
EMPTY_BLOCKS                                       NUMBER
AVG_SPACE                                          NUMBER
CHAIN_CNT                                          NUMBER
AVG_ROW_LEN                                        NUMBER
SAMPLE_SIZE                                        NUMBER
LAST_ANALYZED                                      DATE
BUFFER_POOL                                        VARCHAR2(7)
FLASH_CACHE                                        VARCHAR2(7)
CELL_FLASH_CACHE                                   VARCHAR2(7)
GLOBAL_STATS                                       VARCHAR2(3)
USER_STATS                                         VARCHAR2(3)
IS_NESTED                                          VARCHAR2(3)
PARENT_TABLE_PARTITION                             VARCHAR2(30)
INTERVAL                                           VARCHAR2(3)
SEGMENT_CREATED                                    VARCHAR2(3)

----------确认分区表的各个分表名称及分表键的值分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows,high_value f
ba_tab_partitions where table_name='T_TEST_PARTITION_1';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME NUM_ROWS
------------------------------ ----------
HIGH_VALUE
---------------------------------------------------------------------------

T_TEST_PARTITION_1 P1
USERS
TIMESTAMP' 2013-01-07 00:00:00'

T_TEST_PARTITION_1 P2
USERS
TIMESTAMP' 2013-01-14 00:00:00'

T_TEST_PARTITION_1 P3
USERS
TIMESTAMP' 2013-01-21 00:00:00'

T_TEST_PARTITION_1 P4
USERS

TIMESTAMP' 2013-01-28 00:00:00'

T_TEST_PARTITION_1 P5
USERS
TIMESTAMP' 2013-01-31 00:00:00'

-----------创建本地分区索引
SQL> create index idx_t_test_partition_1_id on t_test_partition_1(reqtime)
2 local
3 (partition p1_idx1,
4   partition p2_idx2,
5   partition p3_idx3,
6   partition p4_idx4,
7   partition p5_idx5
8 )
9 /

索引已创建。

--授权分区表给其它用户
SQL> grant select on t_test_partition_1 to sys;

授权成功。

SQL> desc user_ind_partitions;
名称是否为空? 类型
----------------------------------------- -------- ------------------------

INDEX_NAME                                         VARCHAR2(30)
COMPOSITE                                          VARCHAR2(3)
PARTITION_NAME                                     VARCHAR2(30)
SUBPARTITION_COUNT                                 NUMBER
HIGH_VALUE                                         LONG
HIGH_VALUE_LENGTH                                  NUMBER
PARTITION_POSITION                                 NUMBER
STATUS                                             VARCHAR2(8)
TABLESPACE_NAME                                    VARCHAR2(30)
PCT_FREE                                           NUMBER
INI_TRANS                                          NUMBER
MAX_TRANS                                          NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENT                                         NUMBER
MAX_EXTENT                                         NUMBER
MAX_SIZE                                           NUMBER
PCT_INCREASE                                       NUMBER
FREELISTS                                          NUMBER
FREELIST_GROUPS                                    NUMBER
LOGGING                                            VARCHAR2(7)
COMPRESSION                                        VARCHAR2(8)
BLEVEL                                             NUMBER
LEAF_BLOCKS                                        NUMBER
DISTINCT_KEYS                                      NUMBER
AVG_LEAF_BLOCKS_PER_KEY                            NUMBER
AVG_DATA_BLOCKS_PER_KEY                            NUMBER
CLUSTERING_FACTOR                                  NUMBER
NUM_ROWS                                           NUMBER
SAMPLE_SIZE                                        NUMBER
LAST_ANALYZED                                      DATE
BUFFER_POOL                                        VARCHAR2(7)
FLASH_CACHE                                        VARCHAR2(7)
CELL_FLASH_CACHE                                   VARCHAR2(7)
USER_STATS                                         VARCHAR2(3)
PCT_DIRECT_ACCESS                                  NUMBER
GLOBAL_STATS                                       VARCHAR2(3)
DOMIDX_OPSTATUS                                    VARCHAR2(6)
PARAMETERS                                         VARCHAR2(1000)
INTERVAL                                           VARCHAR2(3)
SEGMENT_CREATED                                    VARCHAR2(3)

-----查本地分区索引的相关数据
SQL> select index_name,partition_name,high_value,num_rows,tablespace_name,st
from user_ind_partitions;