oracle分区表增加索引

oracle分区表查询比较慢，以前是按CID分区，欲增加索引。

1、普通表创建index
CREATE INDEX IDX_LOGIN_MESSAGE_CID on LOGIN_MESSAGE(COMPANY_ID);
2、create index idx_viewer_201603_CID on viewer_201603(COMPANY_ID)
local
(
partition PT_10028196 tablespace VIEWER_DATA_TS_201603,
partition PT_20000831 tablespace VIEWER_DATA_TS_201603,
partition PT_20000621 tablespace VIEWER_DATA_TS_201603,
partition PT_20000156 tablespace VIEWER_DATA_TS_201603,
partition PT_20000593 tablespace VIEWER_DATA_TS_201603,
partition PT_10028196 tablespace VIEWER_DATA_TS_201603
)
err:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
3、create index idx_viewer_201603_CID on viewer_201603(COMPANY_ID) local unusable;
4、select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where INDEX_NAME='IDX_VIEWER_201603_CID';
5、重建分区索引
alter index IDX_VIEWER_201603_CID rebuild partition PT_1002;
alter index IDX_VIEWER_PAGE_201603_CID rebuild partition PT_1008;
6、set linesize 200
select INDEX_NAME,PARTITION_NAME,STATUS,TABLESPACE_NAME from user_ind_partitions where INDEX_NAME='IDX_VIEWER_201603_CID' and PARTITION_NAME='PT_1002';
7、脚本
#!/bin/sh

. ~/.bash_profile

ym=`date +%Y%m`

vt=VIEWER_${ym}
pt=VIEWER_PAGE_${ym}
idx_vt=IDX_VIEWER_${ym}_CID
idx_pt=IDX_VIEWER_PAGE_${ym}_CID

sqlplus 'tv/123456' << EOF
alter table ${pt} modify PAGE_ID NUMBER(12);
create index $idx_vt on ${vt}(COMPANY_ID) local unusable;
create index $idx_pt on ${pt}(COMPANY_ID) local unusable;
EOF

8、
#!/bin/sh

cid=$1;
ym=`date +%Y%m`
idx_vt=IDX_VIEWER_${ym}_CID
idx_pt=IDX_VIEWER_PAGE_${ym}_CID

if [[ -z $1 ]];then
echo "Usage: $0 company_id "
exit 0;
fi

sqlplus 'talk99_viewer/talk99123456' << EOF
alter index ${idx_vt} rebuild partition PT_$cid;
alter index ${idx_pt} rebuild partition PT_$cid;
set linesize 200
select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,status from user_ind_partitions where INDEX_NAME='${idx_vt}'
and PARTITION_NAME='PT_${cid}';
quit;
EOF

9、SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS where PARTITION_NAME='PT_20001326';查询单个分区情况

相关日志