Oracle performance finetuning…Select count(*)

Many times we used this query to show total number of pages or some other purpose.

SELECT count(*) from TABLE_NAME
Pros – Provides exact number
Cons: Takes longer time, based on total number of records

SELECT table_name, num_rows FROM ALL_TABLES where table_name = ‘TABLE_NAME’
Cons: Not accurate (may be 1%) numbers in run time, but good enough for business purpose in many cases.
Pros: Pretty fast

SELECT table_name, num_rows FROM ALL_TABLES where OWNER=’asdf’ order by num_rows desc

-o-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s