как бы ускорить выборку

Thu, 19 Jun 2008 01:35:22 -0700 


Всем доброго дня.

вот такой запрос


select * from GET_DATA(169, 0, 28, '24.05.2008')
union all
select * from GET_DATA(174, 0, 28, '24.05.2008')
union all
select * from GET_DATA(165, 0, 28, '24.05.2008')
union all
select * from GET_DATA(122, 0, 28, '24.05.2008')
union all
select * from GET_DATA(111, 0, 28, '24.05.2008')
union all
select * from GET_DATA(119, 0, 28, '24.05.2008')
union all
select * from GET_DATA(28, 4, 28, '24.05.2008')
union all
select * from GET_DATA(38, 4, 28, '24.05.2008')
union all
select * from GET_DATA(28, 0, 28, '24.05.2008')
union all
select * from GET_DATA(42, 0, 28, '24.05.2008')
union all
select * from GET_DATA(49, 0, 28, '24.05.2008')
union all
select * from GET_DATA(60, 0, 28, '24.05.2008')
union all
select * from GET_DATA(187, 0, 28, '24.05.2008')
union all
select * from GET_DATA(186, 0, 28, '24.05.2008')
union all
select * from GET_DATA(179, 0, 28, '24.05.2008')
union all
select * from GET_DATA(182, 0, 28, '24.05.2008')
union all
select * from GET_DATA(196, 0, 28, '24.05.2008')
union all
select * from GET_DATA(201, 0, 28, '24.05.2008')
union all
select * from GET_DATA(191, 0, 28, '24.05.2008')
union all
select * from GET_DATA(1, 0, 28, '24.05.2008')
union all
select * from GET_DATA(15, 0, 28, '24.05.2008')
union all
select * from GET_DATA(79, 0, 28, '24.05.2008')
union all
select * from GET_DATA(80, 0, 28, '24.05.2008')
union all
select * from GET_DATA(81, 0, 28, '24.05.2008')
union all
select * from GET_DATA(85, 0, 28, '24.05.2008')
union all
select * from GET_DATA(101, 0, 28, '24.05.2008')
union all
select * from GET_DATA(106, 0, 28, '24.05.2008')
union all
select * from GET_DATA(128, 0, 28, '24.05.2008')
union all
select * from GET_DATA(129, 0, 28, '24.05.2008')


выдает такую статистику


723629 fetches, 0 marks, 52152 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 229549 index, 0 seq.
Delta memory: 77104 bytes.
Execute time: 00:05:24.



есть ли идеи как эту штуку можно ускорить?


get_data это такая хранимка


SET TERM ^ ;
ALTER PROCEDURE GET_DATA (
    DEVID Integer,
    TYPEDEVICE Integer,
    TYPECHANNEL Integer,
    D Date )
RETURNS (
    HH Integer,
    T Float )
AS
declare variable d1 timestamp;
declare variable d2 timestamp;
declare variable t1 time;
BEGIN
  hh = 0;
  while (hh < 24) do
  begin
    t1 = '00:00:00';
    t1 = t1 + (60 * 60 * hh);
    d1 = d + t1;
    t1 = '00:00:00';
    if (hh < 23) then
    begin
    t1 = t1 + (60 * 60 * (hh+1));
    end
    else
    begin
    t1 = t1 + (60 * 60 * (hh+1) - 1);
    end
    d2 = d + t1;
    t = null;
    select doublevalue
    from MOMDATA
    where
    exists (select ardtimestamp from MOMDATA
    where (devid = :devid) and (channelid = :typechannel) and
      (typedevice = :typedevice) and (ardtimestamp between :d1 and :d2)) and
      (devid = :devid) and (channelid = :typechannel) and
      (typedevice =:typedevice) and ardtimestamp =
    (select min(ardtimestamp) from MOMDATA
    where (devid = :devid) and (channelid = :typechannel) and
      (typedevice = :typedevice) and (ardtimestamp between :d1 and :d2))
    into t;
    suspend;
    hh = hh + 1;
  end
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE GET_DATA TO SYSDBA;




momdata это такая таблица


CREATE TABLE MOMDATA(
  DEVID Integer,
  TYPEDEVICE Integer,
  CHANNELID Integer,
  ARDTIMESTAMP Timestamp,
  DOUBLEVALUE Float
);
CREATE INDEX IDX_MOMDATA ON MOMDATA (DEVID,ARDTIMESTAMP,CHANNELID,TYPEDEVICE);
CREATE INDEX IDX_MOMDATA1 ON MOMDATA (ARDTIMESTAMP);
CREATE DESCENDING INDEX IDX_MOMDATA2 ON MOMDATA (ARDTIMESTAMP);
CREATE INDEX IDX_MOMDATA3 ON MOMDATA (DEVID,TYPEDEVICE);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON MOMDATA TO SYSDBA WITH GRANT OPTION;


индексы.

IDX_MOMDATA > ASC >
DEVID, ARDTIMESTAMP, CHANNELID, TYPEDEVICE
0.000000 статистика (не знаю что это )

IDX_MOMDATA1
ASC
ARDTIMESTAMP
0.000000  статистика (не знаю что это )



IDX_MOMDATA2
DESC
ARDTIMESTAMP
0.000000  статистика (не знаю что это )




IDX_MOMDATA3
ASC
DEVID, TYPEDEVICE
0.004425   статистика (не знаю что это )


select count(*) from momdata
88405879
select min(ardtimestamp) from momdata
04.03.2008, 16:12:18.000
select max(ardtimestamp) from momdata
30.05.2008, 10:53:54.000

ЗюЫю
заранее спасибо и извините за такой длинный пост.

Ответить