oracle Select count(*)

dba_damion
dba_damion used Ask the Experts™
on
Select count(*) is taking some time in oracle to run. This could is in a vendor enterprise system and they can not change the code to select count(row_id) with out sending us a new update. Is there a way to trick the database to intreprite select count(*) from mytable to select count(row_id) from mytable. This table has long datatype. I tried to do a shrink in the test database  but it will not let you  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
No. unfortunately this is not possible. You have to change the code to either count(1) or count(row_id).

count(*) will definitely take lot more time if you have a long field in the table.
Lead Oracle DBA Team
Commented:
Yes there is a way.
Create Primary Key if possible. PK doesn't contain null values, therefore its not possible that total number of records <> total entry in index. Therefore instead of scanning table, oracle will scan PK.

There are other solutions as well, but before implementing this we need to know the purpose because they may take more resources can cause overall performance issues.
Like you can think of increasing parallel degree of table. In that way you Oracle scan full table scan and use parallelism. You can use the same for PK, which is not recommended right now.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
You can also think of increasing PARALLEL_EXECUTION_MESSAGE_SIZE or DB_FILE_MULTIBLOCK_READ_COUNT=0, therefore while doing Parallelism or fulltable scan oracle will read more resp.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Can you do explain plan on that particular SQL (select count(*))? I'd expect to see Index FFS if you've an index on any of those fields. Like the above post it should read the INDEX to get the count and that should be fast. Did you check you stat? Is it up to date?

Author

Commented:
virdi_ds
Adding primary key did it thank you

Author

Commented:
Great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial