oracle 位图索引重建,关于位图索引，带来的性能问题

本帖最后由 hupeng614 于 2012-5-24 09:13 编辑

update /*+ parallel(a,10) */   Tb_b_ft_cstgrp_prd_inst_mon a

set a.cust_group_id_01 = cust_group_id,

a.cust_group_id_02 = cust_group_id,

a.cust_group_id_03 = cust_group_id

where month_id <=201112 ;

整个表的数据在3000万的样子是 分区的

month_id上建立了分区位图索引

我执行了100多分钟都没有出结果

于是猜测是位图索引照成的，于是按区域使用循环更新， partition(latn_562) 中的562一共17个

update /*+ parallel(a,10) */    Tb_b_ft_cstgrp_prd_inst_mon partition(latn_562) a  set a.edp_cust_group_id_01 = edp_cust_group_id,      a.edp_cust_group_id_02 = edp_cust_group_id,      a.edp_cust_group_id_03 = edp_cust_group_id

where month_id <=201112能完成

问题

1、如果最开始的有问题，是位图索引照成的吗

2、有没有办法检测这样的问题

下面是建表语句

-- Create table

create table EDA.TB_B_FT_CSTGRP_PRD_INST_MON

(

month_id             NUMBER(6),

latn_id              NUMBER(4),

prd_inst_id          NUMBER(12) not null,

own_cust_id          NUMBER(12),

crm_prd_id           NUMBER(9),

market_channel_id    NUMBER(2),

cust_region_id       NUMBER(9),

sch_code             NUMBER(20),

business_area        VARCHAR2(2),

spec_area_id         NUMBER(9),

dev_region_id        NUMBER(9),

edp_cust_group_id_01 NUMBER(2),

cust_group_id_01     NUMBER(2),

edp_cust_group_id_02 NUMBER(2),

cust_group_id_02     NUMBER(2),

edp_cust_group_id_03 NUMBER(2),

cust_group_id_03     NUMBER(2),

edp_cust_group_id    NUMBER(1),

cust_group_id        NUMBER(3),

dev_cust_group_name  VARCHAR2(20),

dev_code             VARCHAR2(50)

)

partition by list (LATN_ID)

(

partition LATN_550 values (550)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_551 values (551)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_552 values (552)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_553 values (553)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_554 values (554)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_555 values (555)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_556 values (556)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_557 values (557)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_558 values (558)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_559 values (559)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_560 values (560)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_561 values (561)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_562 values (562)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_563 values (563)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_564 values (564)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_565 values (565)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

),

partition LATN_566 values (566)

tablespace TBS_BSS_FACT

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

)

);

-- Add comments to the columns

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.market_channel_id

is '网格的客户群1政企2公众3校园';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.cust_region_id

is '客户网格，划配后的';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.sch_code

is '校园编码(ods加工)';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.business_area

is '城乡属性1城市2农村3未知';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.spec_area_id

is '网格专区';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.dev_region_id

is '发展人对应的网格(大门户)';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.edp_cust_group_id_01

is '方案1客户群';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.cust_group_id_01

is '方案1客户群细分市场';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.edp_cust_group_id_02

is '方案2客户群';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.cust_group_id_02

is '方案2客户群细分市场';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.edp_cust_group_id_03

is '方案3客户群';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.cust_group_id_03

is '方案3客户群细分市场';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.edp_cust_group_id

is '客户群，根据本地网采用三套规则中的一种';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.cust_group_id

is '客户群标识';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.dev_cust_group_name

is '发展人对应的客户群大类';

comment on column EDA.TB_B_FT_CSTGRP_PRD_INST_MON.dev_code

is '发展人编码TB_B_DIM_DEVELOPER_ORG_MONTH';

-- Create/Recreate indexes

create bitmap index EDA.B_CSTGRP_MONTH on EDA.TB_B_FT_CSTGRP_PRD_INST_MON (MONTH_ID)

local;

create unique index EDA.U_IDX_CSTGRP_PRD_INST_MON on EDA.TB_B_FT_CSTGRP_PRD_INST_MON (LATN_ID, PRD_INST_ID)

local;

-- Create/Recreate check constraints

alter table EDA.TB_B_FT_CSTGRP_PRD_INST_MON

add constraint LATN_NN

check ("LATN_ID" IS NOT NULL);