?
Solved

How to improve the select query performance for to find the count in a table in oracle

Posted on 2006-06-22
9
Medium Priority
?
1,272 Views
Last Modified: 2008-01-09
i have a huge table which consists nearly 2 million recors in oracle.
when i fire a query for to find the number for record .. it is taking very long time.

Query is : select count(*) from schema.tablename;

what should i do to improve the Performance.


Database : oracle 9.X , windows Machine.

0
Comment
Question by:ayadav1186
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 3

Accepted Solution

by:
JacekMycha earned 600 total points
ID: 16960428
Buid a bitmap index on low cardinality colum (few distinct values). Bitmap indexes have entries for null values as well, so you can pick any column. Few distinct values will keep the index small. For example, a 'GENDER' column with 3 values: M, F and NULL would be perfect.
You can also build a b-tree index on a NOT NULL  colum.
Analyze table, analyze index... You can also build index using CREATE INDEX statement with COMPUTE STATISTICS clause.

You can lookup data dictionary (SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES ...)  to see number of rows when the table was last time analyzed. This is enough accurate for space estimates, but not good for application code.

JacekMycha
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16961588
>>select count(*) from schema.tablename;

(1) do you have index on this table? if you have index, the query will likely to access only index to get better performance instead of full table scan on table data.

(2) This query usually will use FTS ( full table scan). FTS will scan till HWM. In order to let FTS scan less blocks, you could do is to
      1.  reorgnize your table to lower blocks used thus HWM.
      2. You could use "parallel query" to improve the query performance.


acton      
     
       
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16961843
Why do you want to do a "select count(*) from schema.tablename" on a large table?  Do you need an exact count that is up-to-date?  Or, could you analyze the table at regular intervals (like at night or on the weekend) and just do:
select num_rows from all_tables where owner = '[schema]' and table_name = '[table_name]'

or:
select table_name, num_rows from all_tables
where owner = '[schema]'
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 19

Expert Comment

by:actonwang
ID: 16962223
to markgeer:

num_rows  is popluated only after you do statistics analysis. Also,  It might not be up to date.

Therefore I don't think you can use it to substitue for  "select count(*) ..."
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16963166
To actonwang:

Did you notice that I only mentioned using num_rows after saying: "...analyze the table at regular intervals...".
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16963219
to markegeer,

     >>it might not be up to date.

     did you see that?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16963431
Right, the value for "num_rows" will only be as up-to-date as the last time that the table was analyzed.

(Also, I had asked the questioner if he/she needs an up-to-date count and we are still waiting for an answer to that.)
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16963485
heahea :)
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16968338
If you have a reporting database (a data warehouse) then a bit-mapped index may be a good option.  *DO NOT* create a bit-mapped index though on a table used in a transaction-processing system by multiple users!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question