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

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

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Posts
    353

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

    I am selecting 36 columns from two tables
    with simple join condition which is taking
    more than 30 minutes.
    Both tables are indexed.

    select column1,column2,column3,.....,column36
    from tableA,TableB where tableA.Column1=TableB.column8;

    But If I issue the
    same above query with count(*) it is coming in less than a second.

    select count(*) from
    tableA,TableB where tableA.Column1=TableB.column8;

    What is the problem here..?
    Immediate responses will be highly appriciated..

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    learn to look the plan

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Compare execution plan for both queries.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Apr 2003
    Posts
    353
    The db is in another location.
    Requested the plan results. Thanks for the response immd.

  5. #5
    Join Date
    Apr 2003
    Posts
    353
    Please find the plan for both the queries in attachment and advice.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Very nice

    Have you:
    1) looked at it ?
    2) found out what the differences are ?
    3) thought about what it means ?
    Last edited by DaPi; 02-09-2004 at 06:56 AM.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    I will answer for him

    1. no
    2. no
    3. no

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    pando, I though you only spent your time on difficult questions!

  9. #9
    Join Date
    Apr 2003
    Posts
    353
    Originally posted by DaPi
    Very nice

    Have you:
    1) looked at it ?
    2) found out what the differences are ?
    3) thought about what it means ?
    I am sorry, Please brief.. me. I am not well aware of that Plan table.
    I am just studying the same.

    But I changed the query as below and it finished very fast.
    I created a small table and inserted the values from the MINUS
    query.
    and given the below query.
    FROM ORDRHEAD,ORDRDETL WHERE ordh_no in(
    select d.ordh_no from ordh_check d) and ORDH_STATUS='Y'
    AND ORDH_NO = ORDT_ORDH_NO;

    which is finished below 1 second for 600 record.

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by engiri
    I am sorry, Please brief.. me. I am not well aware of that Plan table.
    I am just studying the same.

    But I changed the query as below and it finished very fast.
    I created a small table and inserted the values from the MINUS
    query.
    and given the below query.
    FROM ORDRHEAD,ORDRDETL WHERE ordh_no in(
    select d.ordh_no from ordh_check d) and ORDH_STATUS='Y'
    AND ORDH_NO = ORDT_ORDH_NO;

    which is finished below 1 second for 600 record.
    First of all, did you find what is the differnce between "select col1, col2.." and "select count(*).." ???

    Your execution plans tell it all.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

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