Does the index in a unique field in the table indicate the instant choice of a number (*)? If not, why?

Question

I know exactly enough about SQL tuning to get into trouble. Today I did an EXPLAIN plan on request, and I noticed that it did not use indexes when I thought it probably should be. Well, I continued to do EXPLAIN on simpler and simpler (and more indexed in my mind) queries until I made EXPLAIN on

select count(*) from table_name

I thought this would return instantly and that the explanation would show the use of the index, since we have many indexes in this table, including the index in the row_id column, which is unique. However, the explanation plan showed a full scan of the table, and it took several seconds. (We have 3 million rows in this table).

Why should the oracle perform a full table scan to count the rows in that table? I would like to think that since oracle is already indexing unique fields and needs to track every insert and update in this table, it will cache the row counter somewhere. Even if this is not the case, would it not be faster to scan the entire index than to scan the entire table?

I have two theories. One theory is that I imagine how indexes work incorrectly. Theory 2 is that some parameters or parameters somewhere in our oracle installation fiddled with Oracle's ability to optimize queries (we are in oracle 9i). Can anyone enlighten me?

Answer 1

Oracle COUNT(*).

MySQL MyISAM ( ), MyISAM , COUNT(*) .

Oracle , , , - .

Oracle , , , UNDO, , .

a UNIQUE -t29 > .

UNIQUE , .

NULL, INDEX FAST FULL SCAN COUNT.

, , . B-Tree, .

, , COUNT INDEX_FFS, FULL

Answer 2

, Oracle ( , INDEX FAST FULL SCAN).

, :

• Oracle , ​​ - , NULL, . , .
• Oracle , . , , .

, .

Answer 3

"". , , "" . , .

, "" .

MySQL MyISAM , , , , . . InnoDB MySQL , Oracle, "".

Perhaps you can get a faster query by counting the different values ​​in the primary key, then only the index will be available.