Counting whole table? [message #320346] |
Wed, 14 May 2008 14:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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 #320349 is a reply to message #320347] |
Wed, 14 May 2008 14:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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
|
|
|