Select count(1) taking too long
Hi All,
I am putting this question here for the experts to answer because I am totally out of thoughts on this. I have a table in my DB which on selecting from the dba_tables shows the num_rows as 750,000,000. However, when i tried to do a select count(1) on the table, the query ran for 2 hours and then the session went non reponsive but never did retrieve the count of rows from the table. Can anyone help me with the reason behind this?
Also, just wanted to add here is that this table is present with the same name on 2 tablespaces belonging to the same schema and so when I do a select table_name,num_rows from dba_tables its retrieves me two rows one from tablespace1 and another from tablespace2. Could this possibly be a reason behind the count(1) taking so much of time?
Kindly advice.