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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query takes ages in Oracle 10G

Re: Query takes ages in Oracle 10G

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 21 Sep 2006 23:35:10 +0200
Message-ID: <4513058E.6080901@roughsea.com>


Isabel,

     I guess from what you say about the subquery returning no row that the various criteria that you provide are likely to be selective (taken together, at least).

Just two remarks:

HTH Stéphane Faroult

Isabel Bedoya wrote:
> 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://cf.shopping.yahoo.com>.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 21 2006 - 16:35:10 CDT

Original text of this message

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