Sql – Does an index on a unique field in a table allow a select count(*) to happen instantly? If not why not

indexingoraclesql

I know just enough about SQL tuning to get myself in trouble. Today I was doing EXPLAIN plan on a query and I noticed it was not using indexes when I thought it probably should. Well, I kept doing EXPLAIN on simpler and simpler (and more indexable in my mind) queries, until I did EXPLAIN on

select count(*) from table_name

I thought for sure this would return instantly and that the explain would show use of an index, as we have many indexes on this table, including an index on the row_id column, which is unique. Yet the explain plan showed a FULL table scan, and it took several seconds to complete. (We have 3 million rows in this table).

Why would oracle be doing a full table scan to count the rows in this table? I would like to think that since oracle is indexing unique fields already, and having to track every insert and update on that table, that it would be caching the row count somewhere. Even if it's not, wouldn't it be faster to scan the entire index than to scan the entire table?

I have two theories. Theory one is that I am imagining how indexes work incorrectly. Theory two is that some setting or parameter somewhere in our oracle setup is messing with Oracle's ability to optimize queries (we are on oracle 9i). Can anyone enlighten me?

Best Solution

Oracle does not cache COUNT(*).

MySQL with MyISAM does (can afford this), because MyISAM is transactionless and same COUNT(*) is visible by anyone.

Oracle is transactional, and a row deleted in other transaction is still visible by your transaction.

Oracle should scan it, see that it's deleted, visit the UNDO, make sure it's still in place from your transaction's point of view, and add it to the count.

Indexing a UNIQUE value differs from indexing a non-UNIQUE one only logically.

In fact, you can create a UNIQUE constraint over a column with a non-unique index defined, and the index will be used to enforce the constraint.

If a column is marked as non-NULL, the an INDEX FAST FULL SCAN over this column can be used for COUNT.

It's a special access method, used for cases when the index order is not important. It does not traverse the B-Tree, but instead just reads the pages sequentially.

Since an index has less pages than the table itself, the COUNT can be faster with an INDEX_FFS than with a FULL

Related Solutions

Oracle – How to force oracle to use index range scan

To "force" Oracle to use an index range scan, simply use an optimizer hint INDEX_RS_ASC. For example:

CREATE TABLE mytable (a NUMBER NOT NULL, b NUMBER NOT NULL, c CHAR(10)) NOLOGGING;

INSERT /*+ APPEND */ INTO mytable(a,b,c) 
SELECT level, mod(level,100)+1, 'a'  FROM dual CONNECT BY level <= 1E6;

CREATE INDEX myindex_ba ON mytable(b, a);
EXECUTE dbms_stats.gather_table_stats(NULL,'mytable');

SELECT /*+ FULL(m)         */ b FROM mytable m WHERE b=10; -- full table scan
SELECT /*+ INDEX_RS_ASC(m) */ b FROM mytable m WHERE b=10; -- index range scan
SELECT /*+ INDEX_FFS(m)    */ b FROM mytable m WHERE b=10; -- index fast full scan

Whether this will make your query actually run faster depends on many factors like the selectivity of the indexed value or the physical order of the rows in your table. For instance, if you change the query to WHERE b BETWEEN 10 AND <xxx>, the following costs appear in the execution plans on my machine:

b BETWEEN 10 AND    10     20      40     80
FULL               749    750     751    752
INDEX_RS_ASC        29    325     865   1943
INDEX_FFS          597    598     599    601

If you change the query very slightly to not only select the indexed column b, but also other, non-index columns, the costs change dramatically:

b BETWEEN 10 AND    10     20      40     80
FULL               749    750     751    754
INDEX_RS_ASC      3352  40540  108215 243563
INDEX_FFS         3352  40540  108215 243563

Mysql – How to add indexes to MySQL tables

ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)

Never compare integer to strings in MySQL. If id is int, remove the quotes.

Best Solution

Related Solutions

Oracle – How to force oracle to use index range scan

Mysql – How to add indexes to MySQL tables

Related Question