Query tuning help, please

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Rob
    #1

    Query tuning help, please

    HI,

    Query from a 9i db. Suggestions? Explain plan cost is 296787. Ouch.
    Any help would be much appreciated.

    SELECT WWLINK_THA_DATA SET.FX_ID, WWLINK_DIE.UPDA TE_DM,
    WWLINK_DIE.DIAT _ORAT_DT, WWLINK_DIE.FYAT _ID,
    WWLINK_DIE.OL_I D, WWLINK_DIE.OS_N R,
    WWLINK_DIE.OR_R OW_NR, WWLINK_DIE.OR_C OL_NR,
    WWLINK_THA_DATA SET.OL_ID, WWLINK_THA_DATA SET.OS_NR,
    WWLINK_THA_DATA SET.OR_ROW_NR, WWLINK_THA_DATA SET.OR_COL_NR,
    WWLINK_THA_DATA SET.DIAT_ORAT_D T, WWLINK_THA_DATA SET.FYAT_ID,
    WWLINK_THA_DATA SET.UPDATE_DM FROM WWLINK_DIE,
    WWLINK_THA_DATA SET
    WHERE ( WWLINK_DIE.UPDA TE_DM >= to_date('10/08/2003 05:23:25')
    OR WWLINK_THA_DATA SET.UPDATE_DM >= to_date('10/15/2003
    02:02:22'))
    AND WWLINK_THA_DATA SET.WF_ID = WWLINK_DIE.WF_I D
    AND WWLINK_THA_DATA SET.DI_ROW_NR = WWLINK_DIE.DI_R OW_NR
    AND WWLINK_THA_DATA SET.DI_COL_NR = WWLINK_DIE.DI_C OL_NR
    AND WWLINK_THA_DATA SET.WF_ID is not null
    AND WWLINK_THA_DATA SET.DI_ROW_NR is not null
    AND WWLINK_THA_DATA SET.DI_COL_NR is not null

    Cheers,


    Rob
    rroot -at criadvantage -dot com
    Tags: None
    • Ryan Gaffuri
      #2
      Re: Query tuning help, please

      smithsummit@yah oo.com (Rob) wrote in message news:<fd4d5483. 0310161411.369a 47c6@posting.go ogle.com>...
      HI,
      >
      Query from a 9i db. Suggestions? Explain plan cost is 296787. Ouch.
      Any help would be much appreciated.
      >
      SELECT WWLINK_THA_DATA SET.FX_ID, WWLINK_DIE.UPDA TE_DM,
      WWLINK_DIE.DIAT _ORAT_DT, WWLINK_DIE.FYAT _ID,
      WWLINK_DIE.OL_I D, WWLINK_DIE.OS_N R,
      WWLINK_DIE.OR_R OW_NR, WWLINK_DIE.OR_C OL_NR,
      WWLINK_THA_DATA SET.OL_ID, WWLINK_THA_DATA SET.OS_NR,
      WWLINK_THA_DATA SET.OR_ROW_NR, WWLINK_THA_DATA SET.OR_COL_NR,
      WWLINK_THA_DATA SET.DIAT_ORAT_D T, WWLINK_THA_DATA SET.FYAT_ID,
      WWLINK_THA_DATA SET.UPDATE_DM FROM WWLINK_DIE,
      WWLINK_THA_DATA SET
      WHERE ( WWLINK_DIE.UPDA TE_DM >= to_date('10/08/2003 05:23:25')
      OR WWLINK_THA_DATA SET.UPDATE_DM >= to_date('10/15/2003
      02:02:22'))
      AND WWLINK_THA_DATA SET.WF_ID = WWLINK_DIE.WF_I D
      AND WWLINK_THA_DATA SET.DI_ROW_NR = WWLINK_DIE.DI_R OW_NR
      AND WWLINK_THA_DATA SET.DI_COL_NR = WWLINK_DIE.DI_C OL_NR
      AND WWLINK_THA_DATA SET.WF_ID is not null
      AND WWLINK_THA_DATA SET.DI_ROW_NR is not null
      AND WWLINK_THA_DATA SET.DI_COL_NR is not null
      >
      Cheers,
      >
      >
      Rob
      rroot -at criadvantage -dot com
      cost is completely irrelevant. Ignore it. posting the explain plan and
      the number of records in each table is far more helpful.

      some quick points:

      WHERE ( WWLINK_DIE.UPDA TE_DM >= to_date('10/08/2003 05:23:25')
      OR WWLINK_THA_DATA SET.UPDATE_DM >= to_date('10/15/2003
      02:02:22'))
      forces a full table scan. do you want an index scan? then you need a
      function based index.

      i cant give you any more info than that. i need more information.

        Comment

        • dias
          #3
          Re: Query tuning help, please

          Hi,

          Don't need function based index here.

          Dias



          rgaffuri@cox.ne t (Ryan Gaffuri) wrote in message news:<1efdad5b. 0310170811.1609 df94@posting.go ogle.com>...
          smithsummit@yah oo.com (Rob) wrote in message news:<fd4d5483. 0310161411.369a 47c6@posting.go ogle.com>...
          HI,

          Query from a 9i db. Suggestions? Explain plan cost is 296787. Ouch.
          Any help would be much appreciated.

          SELECT WWLINK_THA_DATA SET.FX_ID, WWLINK_DIE.UPDA TE_DM,
          WWLINK_DIE.DIAT _ORAT_DT, WWLINK_DIE.FYAT _ID,
          WWLINK_DIE.OL_I D, WWLINK_DIE.OS_N R,
          WWLINK_DIE.OR_R OW_NR, WWLINK_DIE.OR_C OL_NR,
          WWLINK_THA_DATA SET.OL_ID, WWLINK_THA_DATA SET.OS_NR,
          WWLINK_THA_DATA SET.OR_ROW_NR, WWLINK_THA_DATA SET.OR_COL_NR,
          WWLINK_THA_DATA SET.DIAT_ORAT_D T, WWLINK_THA_DATA SET.FYAT_ID,
          WWLINK_THA_DATA SET.UPDATE_DM FROM WWLINK_DIE,
          WWLINK_THA_DATA SET
          WHERE ( WWLINK_DIE.UPDA TE_DM >= to_date('10/08/2003 05:23:25')
          OR WWLINK_THA_DATA SET.UPDATE_DM >= to_date('10/15/2003
          02:02:22'))
          AND WWLINK_THA_DATA SET.WF_ID = WWLINK_DIE.WF_I D
          AND WWLINK_THA_DATA SET.DI_ROW_NR = WWLINK_DIE.DI_R OW_NR
          AND WWLINK_THA_DATA SET.DI_COL_NR = WWLINK_DIE.DI_C OL_NR
          AND WWLINK_THA_DATA SET.WF_ID is not null
          AND WWLINK_THA_DATA SET.DI_ROW_NR is not null
          AND WWLINK_THA_DATA SET.DI_COL_NR is not null

          Cheers,


          Rob
          rroot -at criadvantage -dot com
          >
          cost is completely irrelevant. Ignore it. posting the explain plan and
          the number of records in each table is far more helpful.
          >
          some quick points:
          >
          WHERE ( WWLINK_DIE.UPDA TE_DM >= to_date('10/08/2003 05:23:25')
          OR WWLINK_THA_DATA SET.UPDATE_DM >= to_date('10/15/2003
          02:02:22'))
          >
          forces a full table scan. do you want an index scan? then you need a
          function based index.
          >
          i cant give you any more info than that. i need more information.

            Comment

            Previous template Next
            Working...