帮忙给看一下，分区的问题

显示全部楼层 · 2007-9-30 01:06:03

每个分区大概有100万条数据，而当前第一个分区里面有5万多条数据，第二个分区里面25万条数据，后面都没有数据，但是读取数据太慢，请各位给看一下下面的SQL有没有问题：
--创建性能评估历史数据表
CREATE TABLE PFMANA.PSPerfEvlMspHisDataT (
OccTime
DATE NOT NULL,
PumpSNo Char(7) NOT NULL,
FlowR
FLOAT NULL,
PumpH
FLOAT NULL,
EfficyP
FLOAT NULL,
Effic
FLOAT NULL,

RotSP
FLOAT NULL

)
TABLESPACE PSHISDATA
PARTITION BY RANGE (OccTime)
(
PARTITION PSPerfEvlMsp_0801 VALUES LESS THAN (to_date('2008-01-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0801,
PARTITION PSPerfEvlMsp_0802 VALUES LESS THAN (to_date('2008-02-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0802,
PARTITION PSPerfEvlMsp_0803 VALUES LESS THAN (to_date('2008-03-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0803,
PARTITION PSPerfEvlMsp_0804 VALUES LESS THAN (to_date('2008-04-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0804,
PARTITION PSPerfEvlMsp_0805 VALUES LESS THAN (to_date('2008-05-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0805,
PARTITION PSPerfEvlMsp_0806 VALUES LESS THAN (to_date('2008-06-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0806,
PARTITION PSPerfEvlMsp_0807 VALUES LESS THAN (to_date('2008-07-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0807,
PARTITION PSPerfEvlMsp_0808 VALUES LESS THAN (to_date('2008-08-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0808,
PARTITION PSPerfEvlMsp_0809 VALUES LESS THAN (to_date('2008-09-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0809,
PARTITION PSPerfEvlMsp_0810 VALUES LESS THAN (to_date('2008-10-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0810,
PARTITION PSPerfEvlMsp_0811 VALUES LESS THAN (to_date('2008-11-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0811,
PARTITION PSPerfEvlMsp_0812 VALUES LESS THAN (to_date('2008-12-01','YYYY-MM-DD')) TABLESPACE PSPerfEvlMspHIS_0812
);
CREATE INDEX PFMANA.XIE1PSPerfEvlMspHisDataT ON PFMANA.PSPerfEvlMspHisDataT
(
PumpSNo,OccTime
ASC
)
TABLESPACE PSHISDATA
REVERSE
LOGGING
LOCAL
(
PARTITION PSPerfEvlMsp_0801 TABLESPACE PSPerfEvlMspHIS_0801,
PARTITION PSPerfEvlMsp_0802 TABLESPACE PSPerfEvlMspHIS_0802,
PARTITION PSPerfEvlMsp_0803 TABLESPACE PSPerfEvlMspHIS_0803,
PARTITION PSPerfEvlMsp_0804 TABLESPACE PSPerfEvlMspHIS_0804,
PARTITION PSPerfEvlMsp_0805 TABLESPACE PSPerfEvlMspHIS_0805,
PARTITION PSPerfEvlMsp_0806 TABLESPACE PSPerfEvlMspHIS_0806,
PARTITION PSPerfEvlMsp_0807 TABLESPACE PSPerfEvlMspHIS_0807,
PARTITION PSPerfEvlMsp_0808 TABLESPACE PSPerfEvlMspHIS_0808,
PARTITION PSPerfEvlMsp_0809 TABLESPACE PSPerfEvlMspHIS_0809,
PARTITION PSPerfEvlMsp_0810 TABLESPACE PSPerfEvlMspHIS_0810,
PARTITION PSPerfEvlMsp_0811 TABLESPACE PSPerfEvlMspHIS_0811,
PARTITION PSPerfEvlMsp_0812 TABLESPACE PSPerfEvlMspHIS_0812
);

千问 · 2007-9-30 01:06:03

执行什么sql慢啊

千问 · 2007-9-30 01:06:03

读取数据的sql呢？

千问 · 2007-9-30 01:06:03

selectFlowR,PumpH,EfficyP,Effic ,RotSP,occtime from PSPerfEvlMspHisDataT
where PumpSNo='P0001' and occtime >=to_date('2007-12-1','yyyy-mm-dd')
and occtimeset autotrace on
SQL> selectFlowR,PumpH,EfficyP,Effic ,RotSP,occtime from PSPerfEvlMspHisDataT
2where PumpSNo='P0001' and occtime >=to_date('2007-12-1','yyyy-mm-dd')
3and occtime <to_date('2008-3-1','yyyy-mm-dd');
no rows selected

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=83)
10 PARTITION RANGE (ITERATOR)
21 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PSPERFEVLMSPHISD

ATAT' (Cost=2 Card=1 Bytes=83)
32 INDEX (RANGE SCAN) OF 'XIE1PSPERFEVLMSPHISDATAT' (NON-

UNIQUE)

看下是不是以上执行计划

千问 · 2007-9-30 01:06:03

在分区表中，是否跨分区查询会比较慢？请指教！

千问 · 2007-9-30 01:06:03

具体看每个分区的数据量
象楼上这个应该快,数据量比较少
慢可能执行计划有问题

千问 · 2007-9-30 01:06:03

跨分区一定是会慢一些,因为这里有多次I/O ,LZ 的SQL里条件就是跨多个分区,这个分区的建立是否过于细化了，当然如果一个月里的数据就达千万记录，哪就一个月一分，如果没有，则可以半年，或一年一分

千问 · 2007-9-30 01:06:03

不要用set autot trace,用explain