QUERY:
------
SELECT FIRST  20  h.pos,
 h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d')
    FROM ngcb_hist h
    WHERE h.pos>= 6543 
    AND h.publ= 2 
    AND h.trdate=mdy( 7 , 13 , 2005 )
    ORDER BY  1 

Estimated Cost:  55 
Estimated # of Rows Returned:  242 

   1 ) denn.h: INDEX PATH

        Filters: denn.h.publ =  2 

    ( 1 ) Index Keys: trdate pos   (Serial, fragments: ALL)
        Lower Index Filter: (denn.h.trdate =  13 - 07 - 2005  AND denn.h.pos >=  6543  )

CREATE INDEX XIE2fbbonds_hist2_id_trdate_board ON fbbonds_hist
(
       id                             ASC,
       trdate                         ASC,
       boardGroup                     ASC,
       numtrades                      ASC
);

CREATE INDEX XIE2fbbonds_hist2_id_trdate_board ON fbbonds_hist
(
       id                             ASC,
       trdate                         ASC,
       boardGroup                     ASC,
       numtrades                      ASC,
       pos                      ASC
);

    AND h.boardGroup IN
    (SELECT boardGroup FROM boardSelect WHERE boardGroupSet='repo|smal|'
       AND idSector='securies')

CREATE INDEX XIE3ngcb_hist2_trdate_id_board ON ngcb_hist
(
       trdate                         ASC,
       id                             ASC
);

SELECT FIRST  20  h.pos, h.id, h.idBoard, TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI WHERE idSecList='INDEXSECS'
      AND idSector='securies' AND sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    ORDER BY  1 

QUERY:
------
SELECT FIRST  20  h.pos, h.id, h.idBoard,
TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE EXISTS
    (SELECT li.id FROM secsLI li WHERE
      h.id=li.id
      AND li.idSecList='INDEXSECS'
      AND li.idSector='securies' AND li.sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    AND EXISTS
    (SELECT boardGroup FROM boardSelect bs WHERE
       h.boardGroup=bs.boardGroup
       AND bs.boardGroupSet='repo|smal|'
       AND bs.idSector='securies')
    ORDER BY  1 ;

Estimated Cost:  22 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By

   1 ) wad.li: INDEX PATH

        Filters: wad.li.sec_publ =  1 

    ( 1 ) Index Keys: idseclist idsector   (Serial, fragments: ALL)
        Lower Index Filter: (wad.li.idseclist = 'INDEXSECS' AND wad.li.idsector = 'securies' )

   2 ) wad.h: INDEX PATH

        Filters: (wad.h.publ =  2  AND wad.h.pos >=  6543  )

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (wad.h.id = wad.li.id AND wad.h.trdate =  13 - 07 - 2005  )
        Key-First Filters:  (wad.h.numtrades >  0  )
NESTED LOOP JOIN

   3 ) wad.bs: INDEX PATH

    ( 1 ) Index Keys: idsector boardgroupset boardgroup   (Serial, fragments: ALL)
        Lower Index Filter: ((wad.h.boardgroup = wad.bs.boardgroup AND wad.bs.boardgroupset = 'repo|smal|' ) AND wad.bs.idsector = 'securies' )
NESTED LOOP JOIN

   4 ) wad.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (wad.s.id = wad.li.id AND wad.s.idsector = 'securies' )
NESTED LOOP JOIN


QUERY:
------
SELECT FIRST  20  h.pos, h.id, h.idBoard,
TO_CHAR(h.trdate, '%Y-%m-%d'), h.*
    FROM ngcb_hist h, secs s
    WHERE h.id IN
    (SELECT id FROM secsLI li WHERE
      li.idSecList='INDEXSECS'
      AND li.idSector='securies' AND li.sec_publ= 1 )
    AND h.pos>= 6543 
    AND h.publ= 2 
    AND h.id=s.id
    AND h.trdate=mdy( 7 , 13 , 2005 )
    AND h.numtrades> 0 
    AND s.idSector='securies'
    AND h.boardGroup IN
    (SELECT boardGroup FROM boardSelect WHERE
       boardGroupSet='repo|smal|'
       AND idSector='securies')
    ORDER BY  1 

Estimated Cost:  5 
Estimated # of Rows Returned:  1 
Temporary Files Required For: Order By

   1 ) wad.h: INDEX PATH

        Filters: (wad.h.publ =  2  AND wad.h.pos >=  6543  )

    ( 1 ) Index Keys: trdate id boardgroup numtrades   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: (wad.h.trdate =  13 - 07 - 2005  AND wad.h.id = ANY <subquery> )
        Key-First Filters:  (wad.h.boardgroup = ANY <subquery> ) AND
                            (wad.h.numtrades >  0  )

   2 ) wad.s: INDEX PATH

    ( 1 ) Index Keys: idsector id   (Serial, fragments: ALL)
        Lower Index Filter: (wad.h.id = wad.s.id AND wad.s.idsector = 'securies' )
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost:  3 
    Estimated # of Rows Returned:  21 

       1 ) wad.li: INDEX PATH

            Filters: wad.li.sec_publ =  1 

        ( 1 ) Index Keys: idseclist idsector   (Serial, fragments: ALL)
            Lower Index Filter: (wad.li.idseclist = 'INDEXSECS' AND wad.li.idsector = 'securies' )


    Subquery:
    ---------
    Estimated Cost:  1 
    Estimated # of Rows Returned:  2 

       1 ) wad.boardselect: SEQUENTIAL SCAN

            Filters: (wad.boardselect.boardgroupset = 'repo|smal|' AND wad.boardselect.idsector = 'securies' )

registered  100 -- всего пар запросов было
totals  16.7241873741 16.503223896 -- сумма времен выполнения
avarage  0.167241873741 0.16503223896 -- средние значения
percents  1.33891098782 -- в процентах насколько первый медленнее второго