oracle 表分区问题
遇到一个问题 ORACLE的 大家给点意见哦
我创建分区表, 现在查询其中某个分区的数据量COUNT(*)
有30万条数据 ,结果需花费40秒左右时间,执行计划是这样的
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pst
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6319 | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL | AGGS_01 | 821K| | 6319 | 8 |
--------------------------------------------------------------------------------
想问下是出什么问题了, 是进行了全表遍历还是咋地 为啥这么慢
我的分区表是这样建的 , 我该怎样改 谢谢
-- Create table
create table AGGS_01
(
IDENTIFIER VARCHAR2(255),
SERIAL NUMBER(16),
NODE VARCHAR2(64),
NODEALIAS VARCHAR2(64),
MANAGER VARCHAR2(64),
AGENT VARCHAR2(64),
ALERTGROUP VARCHAR2(64),
ALERTKEY VARCHAR2(255),
SEVERITY NUMBER(1),
SUMMARY VARCHAR2(255),
STATECHANGE DATE,
FIRSTOCCURRENCE DATE,
LASTOCCURRENCE DATE,
INTERNALLAST DATE,
POLL NUMBER(16),
TYPE NUMBER(16),
TALLY NUMBER(16),
CLASS NUMBER(16),
GRADE NUMBER(16),
LOCATION VARCHAR2(64),
OWNERUID NUMBER(16),
OWNERGID NUMBER(16),
ACKNOWLEDGED NUMBER(16),
FLASH NUMBER(16),
EXPIRETIME NUMBER(16),
SUPPRESSESCL NUMBER(16),
CUSTOMER VARCHAR2(64),
TASKLIST NUMBER(6),
X733EVENTTYPE NUMBER(16),
X733PROBABLECAUSE NUMBER(16),
X733SPECIFICPROB VARCHAR2(64),
X733CORRNOTIF VARCHAR2(64),
SERVICE VARCHAR2(64),
LASTMODIFIED DATE,
DELETEDAT DATE,
ORIGINALSEVERITY NUMBER(16),
SERVERNAME VARCHAR2(64) not null,
SERVERSERIAL NUMBER(16) not null,
ASB_EQUIP_MANUFACTURER VARCHAR2(64),
ASB_EQUIP_MODEL VARCHAR2(32),
ASB_TECH_DOMAIN NUMBER(16),
ASB_CUSTOMER_LEVEL NUMBER(16),
ASB_ALARM_SEQ VARCHAR2(64),
ASB_ORIGSEVERITY NUMBER(16),
ASB_IMPACTSTATUS NUMBER(16),
ASB_ALARMSTATE NUMBER(16),
ASB_FIRSTACKNOWLEDGED DATE,
ASB_ACKNOWLEDGEDBY VARCHAR2(32),
ASB_TTOPENTIME DATE,
ASB_TTCLOSETIME DATE,
ASB_TTID VARCHAR2(32),
ASB_TTOPENEDBY VARCHAR2(32),
ASB_TTCLOSEDBY VARCHAR2(32),
ASB_ALERTTYPE NUMBER(16),
ASB_SERVICESIMPACTED NUMBER(16),
ASB_SERVICETYPE VARCHAR2(64),
ASB_SERVICEID VARCHAR2(64),
ASB_SERVICESLA NUMBER(16),
ASB_CUSTOMERID VARCHAR2(64),
ASB_LOCATION VARCHAR2(64),
ASB_LOCATION_CITY NUMBER(16),
ASB_LOCATION_DISTRICT VARCHAR2(32),
ASB_LOCATION_EXCHANGE VARCHAR2(32),
ASB_LOCATION_ROOM VARCHAR2(255),
ASB_DISTRICT_ID VARCHAR2(32),
ASB_EXCHANGE_ID VARCHAR2(32),
ASB_ROOM_ID VARCHAR2(32),
ASB_LASTCLEAREDAT DATE,
ASB_ASSOCIATIONID NUMBER(16),
ASB_POSSIBLERC NUMBER(16),
ASB_CIRCUITID VARCHAR2(64),
ASB_ALARMSYNC NUMBER(6)
)
partition by list (OWNERGID)
(
partition PART_731731 values ('731731')
tablespace TBS_AGG_CS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731730 values ('731730')
tablespace TBS_AGG_YUEY
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731732 values ('731732')
tablespace TBS_AGG_XT
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731733 values ('731733')
tablespace TBS_AGG_ZZ
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731734 values ('731734')
tablespace TBS_AGG_HY
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731735 values ('731735')
tablespace TBS_AGG_CZ
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731736 values ('731736')
tablespace TBS_AGG_CD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731737 values ('731737')
tablespace TBS_AGG_YY
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731738 values ('731738')
tablespace TBS_AGG_LD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731739 values ('731739')
tablespace TBS_AGG_SY
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731743 values ('731743')
tablespace TBS_AGG_JS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731744 values ('731744')
tablespace TBS_AGG_ZJJ
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731745 values ('731745')
tablespace TBS_AGG_HH
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731746 values ('731746')
tablespace TBS_AGG_YZ
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731000 values ('731000')
tablespace TBS_AGG_SG
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731001 values ('731001')
tablespace TBS_AGG_OTHERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_731002 values ('731002')
tablespace TBS_AGG_OTHERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_0 values ('0')
tablespace TBS_AGG_OTHERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_1 values ('1')
tablespace TBS_AGG_OTHERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition PART_DEFAULT values (default)
tablespace TBS_AGG_IDX
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table AGGS_01
add constraint PK_AGG_SERVERNAME_SERIAL1_01 primary key (SERVERNAME, SERVERSERIAL)
using index
tablespace TBS_AGG_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IDX_AGGS_FIRST_AGENT1 on AGGS_01 (AGENT, FIRSTOCCURRENCE)
tablespace TBS_AGG_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_AGGS_LASTOCCUR_FIRST1 on AGGS_01 (AGENT, LASTOCCURRENCE)
tablespace TBS_AGG_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);