Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> "select count('x') from BigTable" takes more than 1 minute !

"select count('x') from BigTable" takes more than 1 minute !

From: Big George <jbeteta_at_gmail.com>
Date: 24 Aug 2006 15:07:21 -0700
Message-ID: <1156457240.940662.300270@b28g2000cwb.googlegroups.com>


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 processed
Received on Thu Aug 24 2006 - 17:07:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US