ayadav1186
asked on
How to improve the select query performance for to find the count in a table in oracle
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]'
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]'
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(*) ..."
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(*) ..."
To actonwang:
Did you notice that I only mentioned using num_rows after saying: "...analyze the table at regular intervals...".
Did you notice that I only mentioned using num_rows after saying: "...analyze the table at regular intervals...".
to markegeer,
>>it might not be up to date.
did you see that?
>>it might not be up to date.
did you see that?
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.)
(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.)
heahea :)
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!
(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