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?
Oracledoes not cache
MyISAMdoes (can afford this), because
MyISAMis transactionless and same
COUNT(*)is visible by anyone.
Oracleis transactional, and a row deleted in other transaction is still visible by your transaction.
Oracleshould 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.
UNIQUEvalue differs from indexing a non-
UNIQUEone only logically.
In fact, you can create a
UNIQUEconstraint 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 SCANover this column can be used for
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
COUNTcan be faster with an
INDEX_FFSthan with a