Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> "select count('x') from BigTable" takes more than 1 minute !
Oracle 10g
I have a table with 20 millions of records
Name of Table: BigTable
Structure of Table:
NumID Char(8) LastName1 Varchar2(40) LastName2 Varchar2(40) FirstName Varchar2(35) StateID Integer CityID Integer StreetID Integer
Primary Key: NumID
Indexes:
Ind_Names: LastName1+LastName2
Ind_NumID: NumID
If I make a query searching for a NumID or for Names, it is very fast.
Get row immediately, like:
select * from BigTable
where NumID = '12345678'
I supose it means that indexes are OK
The problem is when I try to make a simple count(*), like:
select count('x') from BigTable
It takes 1min30 sec ! Is it normal? Or is it to be faster? I think that something is wrong with the table. Is it not supposed that Oracle can handle this amount of records very fast? Maybe there is some tuning that it is needed.
I tried:
select /*+ INDEX(BigTable Index_Of_NumID) */ count('x') from BigTable where numId = numId
and "where numId = numId" made the difference. Query was faster, but only 10 secs less than before.
I have seen that /*+ INDEX(BigTable Index_Of_NumID) */ makes no difference at all in time used.
About using GATHER_STATICTIS, that is used when a table has many rows deleted, inserted or updated. In this case, BigTable is a Table that does not change. The same 20 millions records stay there.
Here is my Execution Plan:
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=65010 Card=1 Bytes =17) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'Index_Of_NumID' (INDEX (UNIQUE)) (Cost=65010 Card=16486652 Bytes=280273084)
Statistics
1 recursive calls 0 db block gets 231638 consistent gets 231553 physical reads 0 redo size 425 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedReceived on Thu Aug 24 2006 - 17:07:21 CDT