Select 36 columns versus Count(*) High difference in performance why? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Select 36 columns versus Count(*) High difference in performance why?

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Try using Ordered hint in the outer query
    and HASH_AJ hint in the sub query.

    SELECT /*+ ordered */
    ORDH_NO,ORDH_DATE,ORDH_REF_NO,xxx....
    FROM ORDRHEAD I,
    ORDRDETL
    WHERE ORDH_NO = ORDT_ORDH_NO
    AND ORDH_STATUS='Y'
    AND I.ORDH_NO NOT in
    (SELECT /*+ hash_aj */ ORDH_NO
    FROM PS_ORDH_CNTL ) ;

    Do not omit any column in the Select statement.
    Post the explain plan output here.

    The 2nd approach is:

    SELECT /*+ ORDERED */ C1, C2, C3 , ......
    FROM ( SELECT /*+ NO_MERGE */
    ORDH_NO, ORDH_DATE ,....
    FROM ORDRHEAD H
    WHERE H.ORDH_STATUS = 'Y'
    AND H.ORDH_NO NOT IN
    ( SELECT /*+ hash_aj */ ORDH_NO
    FROM PS_ORDH_CNTL)
    ) I,
    ORDRDETL D
    WHERE I.ORDH_NO = D.ORDT_ORDH_NO ;


    The idea is filter the HEADER table as much as possible before join with DETAIL table.

    Read some of the tuning books. That will help you in the long run.

    Tamil

  2. #12
    Join Date
    Jan 2004
    Posts
    15
    Hi guys.
    What i know is, if you have an index on the table
    selecting COUNT(*) will scan the index in order to count rows instead of scan the table itself, that way it will be faster than selecting columns. (It's written in documentation).
    Regards.

  3. #13
    Join Date
    Apr 2003
    Posts
    353
    Originally posted by SANJAY_G
    First of all, did you find what is the differnce between "select col1, col2.." and "select count(*).." ???

    Your execution plans tell it all.
    I just started going through Explain Plans..
    Thanks for your guidances..
    Your posts inducing me to study..
    Thanks

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by engiri
    Your posts inducing me to study..
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #15
    Join Date
    Aug 2001
    Posts
    184
    Originally posted by engiri
    I just started going through Explain Plans..
    Thanks for your guidances..
    Your posts inducing me to study..
    Thanks
    this is what you get when you outsource...
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  6. #16
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    this is what you get when you outsource...

    ???
    Cheers!
    OraKid.

Posting Permissions

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



Click Here to Expand Forum to Full Width