Refactored queries needing validation of syntactic equivalence

Поиск
Список
Период
Сортировка
Искать
От
Mike Adams
Тема
Refactored queries needing validation of syntactic equivalence
Дата
Msg-id
470D5F81.5090403@comcast.net
Список
Дерево обсуждения
Refactored queries needing validation of syntactic equivalence Mike Adams <madams55075@comcast.net>
Re: Refactored queries needing validation of syntactic equivalence Richard Huxton <dev@archonet.com>
Re: Refactored queries needing validation of syntactic equivalence Mike Adams <madams55075@comcast.net>
Re: Refactored queries needing validation of syntactic equivalence Richard Huxton <dev@archonet.com>
Re: Refactored queries needing validation of syntactic equivalence Mike Adams <madams55075@comcast.net>
Hello! I'm a long time lurker who has become responsible for maintaining 
/ updating utility queries at work.  I've reworked two queries (as text 
attachment as they are wide lines) to enhance the planner's chance of 
speeding up the queries (Oracle8i's).

I'm looking for someone to eyeball them and let me know if I've folded 
the sub-selects up correctly (I'm the ONLY sql speaking person at work 
so having a coworker do so is unfortunately not possible).

Also unfortunately, there currently aren't any issues in the database 
that these queries are designed to find.  All I can say for sure is (as 
you can see below each query) my refactored queries *at the least* 
return *no* data faster than the legacy queries...

Thank you in advance and I wish the application at work used postgresql 
as it's backend!

Michael Adams
                               legacy                                |                              refactor
---------------------------------------------------------------------+--------------------------------------------------------------------
 select m.co_cd,                                                     | select m.co_cd,
        m.ve_cd,                                                     |        m.ve_cd,
        m.ivc_cd,                                                    |        m.ivc_cd,
        m.po_cd,                                                     |        m.po_cd,
        m.itm_cd,                                                    |        m.itm_cd,
        m.qty,                                                       |        m.qty,
        m.unit_cst,                                                  |        m.unit_cst,
        (m.qty*m.unit_cst) as ext_cst,                               |        (m.qty*m.unit_cst) as ext_cst,
        to_char(m.rcv_dt,'YYYY-MM-DD') as received,                  |        to_char(m.rcv_dt,'YYYY-MM-DD') as received,
        origin_cd,                                                   |        m.origin_cd,
        to_char(m.assigned_dt,'YYYY-MM-DD') as assigned              |        to_char(m.assigned_dt,'YYYY-MM-DD') as assigned
 from   rcv_mo m                                                     | from   rcv_mo m, rcv_mo r
 where  ( m.origin_cd    = 'MOM' )                                   | where  ( m.origin_cd    = 'MOM' )
    and ( m.ASSIGNED_DT <= '31-Oct-2007'                             |    and ( m.ASSIGNED_DT <= '31-Oct-2007'
          or                                                         |          or
          m.ASSIGNED_DT is null                                      |          m.ASSIGNED_DT is null
    )                                                                |    )
    and ( exists ( select 1                                          |    and (   m.po_cd     =  r.po_cd  )
                   from   rcv_mo  o                                  |    and (   m.itm_cd    =  r.itm_cd )
                   where  ( m.po_cd        = o.po_cd  )              |    and ( r.assigned_dt is null       )
                      and ( m.itm_cd       = o.itm_cd )              |    and ( r.rcv_dt      <= '31-Oct-2007')
                      and ( o.assigned_dt is null )                  | order by m.VE_CD, m.po_cd, m.itm_cd
                      and ( o.rcv_dt      <= '31-Oct-2007')          |
                 )                                                   | -- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    )                                                                | -- [Executed: 10/10/07 9:24:09 AM CDT ] [Execution: 937/ms]
 order by m.VE_CD, m.po_cd, m.itm_cd                                 |
                                                                     |
 -- 0 record(s) selected [Fetch MetaData: 16/ms] [Fetch Data: 0/ms]  |
 -- [Executed: 10/10/07 8:47:39 AM CDT ] [Execution: 2054333/ms]     |
                                                                     |
---------------------------------------------------------------------+--------------------------------------------------------------------
---------------------------------------------------------------------+--------------------------------------------------------------------
 select o.co_cd,                                                     | select o.co_cd,
        o.ve_cd,                                                     |        o.ve_cd,
        o.ivc_cd,                                                    |        o.ivc_cd,
        o.po_cd,                                                     |        o.po_cd,
        o.itm_cd,                                                    |        o.itm_cd,
        o.qty,                                                       |        o.qty,
        o.unit_cst,                                                  |        o.unit_cst,
        (o.qty*o.unit_cst) as ext_cst,                               |        (o.qty*o.unit_cst) as ext_cst,
        to_char(o.rcv_dt,'YYYY-MM-DD') as received,                  |        to_char(o.rcv_dt,'YYYY-MM-DD') as received,
        o.origin_cd,                                                 |        o.origin_cd,
        to_char(o.assigned_dt,'YYYY-MM-DD') as assigned              |        to_char(o.assigned_dt,'YYYY-MM-DD') as assigned
 from   rcv_mo o                                                     | from   rcv_mo o, rcv_mo m
 where  ( o.assigned_dt is null               )                      | where  ( o.assigned_dt is null           )
    and ( o.rcv_dt      <= '31-Oct-2007' )                           |    and ( o.rcv_dt      <= '31-Oct-2007'  )
    and ( exists ( select 1                                          |    and ( m.origin_cd    = 'MOM'          )
                   from   rcv_mo  m                                  |    and ( o.po_cd        = m.po_cd        )
                   where  ( m.origin_cd    = 'MOM'         )         |    and ( o.itm_cd       = m.itm_cd       )
                      and ( o.po_cd        = m.po_cd       )         |    and ( m.assigned_dt <= '31-Oct-2007'
                      and ( o.itm_cd       = m.itm_cd      )         |        or
                      and ( m.assigned_dt <= '31-Oct-2007'           |          m.assigned_dt is null
                            or                                       |    )
                            m.assigned_dt is null                    | order by o.ve_cd, o.po_cd, o.itm_cd
                      )                                              |
                 )                                                   | -- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    )                                                                | -- [Executed: 10/10/07 9:32:03 AM CDT ] [Execution: 344/ms]
 order by o.ve_cd, o.po_cd, o.itm_cd                                 |
                                                                     |
 -- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]   |
 -- [Executed: 10/10/07 9:31:28 AM CDT ] [Execution: 148562/ms]      |
                                                                     |
---------------------------------------------------------------------+---------------------------------------------------------------------
---------------------------------------------------------------------+---------------------------------------------------------------------
В списке pgsql-sql по дате отправления
От: Richard Huxton
Дата:
От: TJ O'Donnell
Дата:
Сообщение: seg data type
FAQ