This discussion is archived
6 Replies Latest reply: Nov 26, 2012 1:11 AM by Christian Erlinger RSS

Performance issues

975623 Newbie
Currently Being Moderated
Hi

I am Using below query on 200 millions record and it takes around 2 Hours to copy all data in cursor .
can some one help me how to optimize the query ?

Cursor Cus_1 is
Select Account,count(*)
from EMP
where Account_status = 'X'
group by account
having count(*) > 1 ;

Thanks
Abhishek Singh
abhisheksinghocp@gmail.com
  • 1. Re: Performance issues
    user186844 Newbie
    Currently Being Moderated
    1- rebuild all index on this table
    2- create index on Account_status
    3- analyze table
  • 2. Re: Performance issues
    O.Developer Journeyer
    Currently Being Moderated
    Cursor Cus_1 is
    Select Account,count(*)
    from EMP
    where Account_status = 'X'
    group by account
    having count(*) > 1 ;


    -------

    Beside index creation(rebuilding),

    you use Count(Account) instead of count (*) - as counts(*) will count all field if not specifed.



    ---------Try and post your feed back here
  • 3. Re: Performance issues
    larssjostrom Newbie
    Currently Being Moderated
    I have, for twenty years used count(*) and what does a column spec doing there for me?
  • 4. Re: Performance issues
    Christian Erlinger Guru
    Currently Being Moderated
    Lars Sjöström wrote:
    I have, for twenty years used count(*) and what does a column spec doing there for me?
    And you shall continue in doing so :D
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

    To the OP: I can't see a connection to oracle forms, and SQL Performance related problems should be better posted in the SQL and PL/SQL forum: SQL and PL/SQL

    But without the table structure and a query plan most certainly there is not much they can do.
    Take a look here to start: SQL and PL/SQL FAQ

    cheers
  • 5. Re: Performance issues
    O.Developer Journeyer
    Currently Being Moderated
    I have, for twenty years used count(*) and what does a column spec doing there for me?

    -----In general it is okay,

    But did you tried with column name , and tell me what is your effect ?
  • 6. Re: Performance issues
    Christian Erlinger Guru
    Currently Being Moderated
    And why should it work better with count(<column>) instead of count(*)?

    The only condition where it might have an impact would be if there is a not null constraint or an index on the column. If there is no nut null constraint on the column the database would have to check if the column is not null and count those instead of simply count all. If there is an index the database might decide to use the index to count the rows. So far it isn't known if any of the above is the case, and as far as we know the column is allowed to have null values, so count(<column>) and count(*) can also give you different results which might be wrong.

    cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points