Current Position:Home > Help needed to optimize the query

Help needed to optimize the query

Update:10-11Source: network consolidation
Advertisement
Help needed to optimize the query:
The requirement is to select the record with max eff_date from HIST_TBL and that max eff_date should be > = '01-Jan-2007'.
This is having high cost and taking around 15mins to execute.
Can anyone help to fine-tune this??
   SELECT c.H_SEC,
                c.S_PAID,
                c.H_PAID,
                table_c.EFF_DATE
   FROM    MTCH_TBL c
                LEFT OUTER JOIN
                   (SELECT b.SEC_ALIAS,
                           b.EFF_DATE,
                           b.INSTANCE
                      FROM HIST_TBL b
                     WHERE b.EFF_DATE =
                              (SELECT MAX (b2.EFF_DATE)
                                 FROM HIST_TBL b2
                                WHERE b.SEC_ALIAS = b2.SEC_ALIAS
                                      AND b.INSTANCE =
                                             b2.INSTANCE
                                      AND b2.EFF_DATE >= '01-Jan-2007')
                           OR b.EFF_DATE IS NULL) table_c
                ON  table_c.SEC_ALIAS=c.H_SEC
                   AND table_c.INSTANCE = 100;

The Best Answer

Advertisement
To start with, I would avoid scanning HIST_TBL twice.
Try this
select c.h_sec
     , c.s_paid
     , c.h_paid
     , table_c.eff_date
  from mtch_tbl c
  left
  join (
          select sec_alias
               , eff_date
               , instance
            from (
                    select sec_alias
                         , eff_date
                         , instance
                         , max(eff_date) over(partition by sec_alias, instance) max_eff_date
                      from hist_tbl b
                     where eff_date >= to_date('01-jan-2007', 'dd-mon-yyyy')
                        or eff_date is null
           where eff_date = max_eff_date
              or eff_date is null
       ) table_c
    on table_c.sec_alias = c.h_sec
   and table_c.instance  = 100;

Database error: [Table 'ac_posts' is marked as crashed and should be repaired]

SELECT a.*, b.post_content FROM ac_posts a INNER JOIN ac_posts_content b ON a.ID = b.ID WHERE a.ID IN(40444,373036,1580035,2966204,3171965,3172017,3553722,1140476,1302985,2192060) ORDER BY FIELD(a.ID, 40444,373036,1580035,2966204,3171965,3172017,3553722,1140476,1302985,2192060);