Query takes 1min 15 secs and tables are indexed !

From: Big Charles <cherediatech_at_yahoo.com>
Date: 31 Jul 2006 08:55:51 -0700
Message-ID: <1154361351.091429.98630@h48g2000cwc.googlegroups.com>

I make a query using three tables on Oracle 10g. The query lasts 1 min 15 sec !

SELECT CA.cod_dni AS cod_dni,

         P.apepat AS apepat ,
         P.apemat AS apemat,
         P.nombre AS nombres,
         OP.DES_OP AS DES_OP,
         OP.cod_op AS cod_op,
         CA.FECHA  AS FECHA,
         CA.flg_renuncia AS flg_renuncia,
      'COMITES DE AFILIADOS' AS des_parte

FROM PADRON P, TBL_COMITES_AFIL CA, TBL_OP OP

 WHERE CA.cod_op  = OP.cod_op  AND
       CA.cod_dni = P.numdle(+) AND
       CA.flg_renuncia <> 0

I analyze the three tables:

Table TBL_COMITES_AFIL:

Total Records: 510,647
SELECT COUNT('x') FROM TBL_COMITES_AFIL Time: 1 sec

Table TBL_OP:

Total Records: 631
SELECT COUNT('x') FROM TBL_OP Time: immediately

Table PADRON:

Total records: 16,494,906
SELECT COUNT('x') FROM PADRON Time: 45-50 sec

It seems that the problem is table PADRON, because it has about 16 millions records and a simple COUNT('x') on the table takes 45-50 secs, even that the table is indexed: Primary Key on numdle field. If I make a query like:
SELECT * from PADRON where numdle='12345678', the results are immediately.

I would like to know what is missing, because I think that Oracle 10g can manage tables of 16 millions records without taking almost 1 minute for a simple Record Count. Received on Mon Jul 31 2006 - 10:55:51 CDT