6 Replies Latest reply: Nov 26, 2012 3:11 AM by Christian Erlinger RSS

    Performance issues

    975623
      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
          hamdan
          1- rebuild all index on this table
          2- create index on Account_status
          3- analyze table
          • 2. Re: Performance issues
            O.Developer
            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
              Lars Sjöström
              I have, for twenty years used count(*) and what does a column spec doing there for me?
              • 4. Re: Performance issues
                Christian Erlinger
                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
                  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
                    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