Home » RDBMS Server » Performance Tuning » Counting whole table? (oracle9i)
Counting whole table? [message #320346] Wed, 14 May 2008 14:44 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Hello,

My understanding is, when we fire the query select count(*) from tab1, it should be faster when we have index column with NOT NULL constraint. Is this not correct??


I ran the query with no index on NOT NULL column. It took 14 sec. I ran the query with index on NOT NULL column. It took the same time. Here is the below example

SQL> SET TIME ON
15:22:16 SQL>
15:22:16 SQL> SELECT COUNT(*) FROM EMP1;

COUNT(*)
----------
7340032

15:22:30 SQL> ALTER TABLE EMP1 MODIFY(EMPNO NOT NULL);

Table altered.

15:23:59 SQL> CREATE INDEX IDX ON EMP1(EMPNO);

Index created.

15:32:00 SQL>
15:32:05 SQL>
15:32:05 SQL>
15:32:06 SQL> SELECT COUNT(*) FROM EMP1;

COUNT(*)
----------
7340032

15:32:20 SQL>
15:32:21 SQL>
Re: Counting whole table? [message #320347 is a reply to message #320346] Wed, 14 May 2008 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
should be faster when we have index column with NOT NULL constraint. Is this not correct??

It depends.
Does Oracle use the index?
Does Oracle have statistics that allow it to choose the index?
Is wall clock a good tool to measure performances?
What is the percentage of time spent in network during your query?
And so on.

Regards
Michel

Re: Counting whole table? [message #320349 is a reply to message #320347] Wed, 14 May 2008 14:58 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Michel, I appreciate your immediate reply. I would like to Thank you for your response.

Now i analyzed the table. Now it ran 5 seconds. It was 14 seconds. It means that, table should be analyzed when we have index on not null column. Thanks.

Re: Counting whole table? [message #320351 is a reply to message #320349] Wed, 14 May 2008 15:19 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle needs statistics on all objects in order to choose the optimal plan.
So yes, when you create a new index you have to compute the statistics afterwards.

Note that you can see the plan used by Oracle by previously executing "set autotrace on".

Regards
Michel
Previous Topic: Need info regarding DATABASE PARTITIONING
Next Topic: challenge to Sr.DBA
Goto Forum:
  


Current Time: Sat Dec 10 16:46:45 CST 2016

Total time taken to generate the page: 0.09234 seconds