| Counting whole table? [message #320346] |
Wed, 14 May 2008 14:44  |
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  |
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 |
 |
Michel Cadot
Messages: 68665
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
