Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: execution plan with distinct clause

    Hi guys:
    I have this query, then i need to obtein from there the distinct set of the rows. The problem is that the whole execution plan is changed and the tables in query 1 searched entirely if the distinct clause is include. My intension is to execute the distinct but just in the result of query1. Any way to force that?

    Query1:
    SELECT ( LTrim(RTrim(MOC_OHNUMBER)) || '_' || LTrim(RTrim(MOC_REF_NO))) RQ_JobLot, MOC_OHNUMBER, MOC_REF_NO
    FROM MatQry_4
    WHERE RPAD(LTrim(RTrim(MOC_OHNUMBER)) || '_' || LTrim(RTrim(MOC_REF_NO)), 55, ' ') IN
    (
    SELECT RPAD(LTrim(RTrim(RQ_MOSJOB)) || '_' || (LTrim(RQ_MOSLOT, '0')), 55, ' ') RQ_JobLot
    FROM MatQry_7 LotMaterials
    WHERE (RQ_IMKey='IMKeyCode')
    )

    Query2:
    SELECT DISTINCT MOC_OHNUMBER FROM Query1

    thanks 4 ur help... Aishel

  2. #2
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    I may be way off here but won't an inline query produce the results?

    Somthing like:
    SELECT DISTINCT MOC_OHNUMBER FROM
    (SELECT ( LTrim(RTrim(MOC_OHNUMBER)) || '_' || LTrim(RTrim(MOC_REF_NO))) RQ_JobLot, MOC_OHNUMBER, MOC_REF_NO
    FROM MatQry_4
    WHERE RPAD(LTrim(RTrim(MOC_OHNUMBER)) || '_' || LTrim(RTrim(MOC_REF_NO)), 55, ' ') IN
    (
    SELECT RPAD(LTrim(RTrim(RQ_MOSJOB)) || '_' || (LTrim(RQ_MOSLOT, '0')), 55, ' ') RQ_JobLot
    FROM MatQry_7 LotMaterials
    WHERE (RQ_IMKey='IMKeyCode')
    ))

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    yes that was initially the way i wrote it, but still takes 45 second to execute cause the distinct force a full search in the tables.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    The CBO can be an amazing beast, but an awfully frustrating one at times. You might consider forcing it along your path...


    select /*+ INDEX( tbl, indexname ) */
    column1, column2
    from tablename tbl
    where....

    This explains more http://www.billmagee.co.uk/oracle/sqltune/index.html

    or more specifically http://www.billmagee.co.uk/oracle/sq...70_single.html

    Hth
    Bill

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Thanks a lot Bill, as yu said, yesterday frustation is the excitement of today, and all about CBO. This query now execute in less than one second.

    SELECT /*+ INDEX(MOC, KSMOC)*/ Distinct *
    FROM Query1

    Cheers

    Aishel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •