Re: Query takes 1min 15 secs and tables are indexed !

Big Charles wrote:
> 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.

Oracle can easily handle 10X or 100X the number of records you are dealing with: Even on a little notebook computer. Here are some questions to help us help you.

1. Which version of 10g?
2. Are statistics collected with DBMS_STATS current?
3. What are the values of in v$parameter for optimizer_index_cost_adj and optimizer_index_caching?
4. Where is the EXPLAIN PLAN or AUTOTRACE? -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org