Query takes ages in Oracle 10G

From: Isabel Bedoya <isabel_bga_at_yahoo.ca>
Date: Thu, 21 Sep 2006 17:13:03 -0400 (EDT)
Message-ID: <20060921211303.69318.qmail@web37915.mail.mud.yahoo.com>

Hi all,   

I am trying to run a query with sub-queries in Oracle 10G Release 2 and it is taking ages, I divided the query into parts and I have no problems running the first and third subqueries (together and each one separated) but when I try to run the second subquery along with the other two or just itself, it takes ages (I generated a trace and returned a wait of 22 minutes executing the query giving no results at all). The table has 22567 records in it and runs PERFECTLY in Oracle 9i, we didn't change anything while migrating to 10G. It seems there is a problem with the date being invoked in the second subquery. Do you have any clue about what can be happening with this query?. Thanks    

  SELECT *
FROM TITULO

WHERE  TITULO_ESCENA_CODIGO___ = 'REAL'  AND
       TITULO_PORTAF_CODIGO___ = 'OBL'  AND
       TITULO_TIPTIT_CODIGO___ = 'TES_TF'  AND
       (TITULO_INDTITGAR IS NULL OR TITULO_INDTITGAR = 4) AND
       TITULO_FUTURO___ IS NULL  AND
       TITULO_ESTADO___ = 2 AND 
       TITULO_CLASIFICA <> 3 AND 
       TITULO_NUMERO___ NOT IN (SELECT OPETIT_TITULO_NUMERO___
                                FROM   OPETIT, OPERAC
                                WHERE  OPETIT_ESCENA_CODIGO____OPE =
'REAL' AND
                                       OPETIT_PORTAF_CODIGO____OPE = 'OBL'
AND
                                       OPETIT_OPERAC_NUMERO___ =
OPERAC_NUMERO___ AND
                                       OPERAC_ESCENA_CODIGO___ = 'REAL' AND
                                       OPERAC_PORTAF_CODIGO___ = 'OBL' AND
                                       OPERAC_TIPOPE_CODIGO___ = 2 AND
                                       OPERAC_ESTADO___ IN (2, 3)) AND
       TITULO_NUMERO___ NOT IN (SELECT VENCIM_TITULO_NUMERO___
                                FROM   VENCIM
                                WHERE  VENCIM_ESCENA_CODIGO___ = 'REAL' AND
                                       VENCIM_PORTAF_CODIGO___ = 'OBL' AND
                                       VENCIM_ESTADO___ = 2               
   AND
                                       VENCIM_FECCOBORI <= '21/09/2006') AND 
       TITULO_NUMERO___ NOT IN (SELECT T_TITU.TITULO_NUMERO___
                                FROM   T_TITU, T_OPTI
                                WHERE  T_TITU.TITULO_ESCENA_CODIGO___ = 'REAL' AND 
  T_TITU.TITULO_PORTAF_CODIGO___ = 'OBL' AND
                                       T_TITU.TITULO_NUMERO___        = OPETIT_TITULO_NUMERO___ AND
                                       OPETIT_ESCENA_CODIGO____OPE = 'REAL' AND
                                       OPETIT_PORTAF_CODIGO____OPE = 'OBL'
AND
                                       OPETIT_OPERAC_NUMERO___     = 58)

 		
---------------------------------

Lèche-vitrine ou lèche-écran ? Yahoo! Magasinage.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 21 2006 - 16:13:03 CDT