RE: Tracing SQL: where is the select in the select ?

Stephane, I believe the mohd_noeud_article is either a view or a synonym pointing to mohd_article.

If you look at the access plan mohd_article is accessed but it isn't in the select stmt. Also you select from mohv_article but it isn't in the access plan either

mohd_noeud_article - > view/synonym for mohd_article? mohv_article - > view for moh_article?

• Brian
  • paquette stephane <stephane_paquette_at_yahoo.com> wrote:
    > Hi,
    >
    > As you 'll see, there is no mohd_noeud_article table
    > in the access plan.
    > SELECT
    > decode(ass.quantite_associe,null,1,ass.quantite_associe)
    > as quantite_associe,
    > art.nom_court as nom_court,
    > art.id_article,
    > (SELECT max(dna.id_noeud) FROM mohd_noeud_article dna
    > WHERE dna.id_article = art.id_article_generique)
    > id_noeud,
    > art.etat as etat,
    > art.majeur as majeur,
    > art.quantite_dispo_vente as dispo,
    > art.quantite_seuil as seuil,
    > upper(m.nom) as nom_marque,
    > concat(to_char(art.prix_frf,'99G9990D99MI'),' FRF') as
    > prix,
    > art.quantite_maximum as quantite_maximum,
    > art.quantite_dispo_vente as dispo,
    > art.quantite_seuil as seuil,
    > art.majeur as majeur,
    > art.etat as etat
    > FROM moh_articles_associes ass,
    > mohv_article art,
    > moh_marque m
    > WHERE ass.id_article = 1110857
    > AND ass.type = 'TAS_PACK'
    > AND art.id_article = ass.id_article_associe
    > AND m.id_marque = art.id_marque
    >
    >
    > 0 SELECT STATEMENT GOAL: CHOOSE
    > 2 NESTED LOOPS
    > 3 NESTED LOOPS
    > 3 NESTED LOOPS
    > 3 NESTED LOOPS
    > 3 TABLE ACCESS GOAL: ANALYZED (BY INDEX
    > ROWID) OF
    > 'MOH_ARTICLES_ASSOCIES'
    > 3 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    > 'MOH_LIEN_33_FK' (NON-UNIQUE)
    > 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX
    > ROWID) OF
    > 'MOH_ARTICLE'
    > 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
    > 'MOH_ARTICLE_PK' (UNIQUE)
    > 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX
    > ROWID) OF
    > 'MOH_ARTICLE'
    > 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
    > 'MOH_ARTICLE_PK'
    > (UNIQUE)
    > 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX
    > ROWID) OF
    > 'MOH_MARQUE'
    > 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
    > 'MOH_MARQUE_PK'
    > (UNIQUE)
    > 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX
    > ROWID) OF
    > 'MOHD_ARTICLE'
    > 4 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
    > 'MOHD_ARTICLE_PK'
    > (UNIQUE)
    >
    > --- "Weerd Kirsten, E.C. de"
    > <Kirsten.deWeerd_at_Oranjewoud.nl> a écrit : > Hi,
    > >
    > > I don't see any reaction to this as of yet.
    > >
    > > I'm not sure I can help, but it could be easier if
    > > I'd have the full
    > > select-statement and de tkprof-output ...
    > >
    > > Greets,
    > >
    > > Kirsten
    > >
    > > > -----Original Message-----
    > > > From: paquette stephane
    > > [SMTP:stephane_paquette_at_yahoo.com]
    > > > Sent: Friday, August 11, 2000 5:56 PM
    > > > To: Multiple recipients of list ORACLE-L
    > > > Subject: Tracing SQL: where is the select in the
    > > select ?
    > > >
    > > > Hi all,
    > > >
    > > > I'm tracing a select containing a select :
    > > > select c1, c2, (select c4,c5 from t1
    > > where...),c3...
    > > > from ..
    > > > where ...
    > > >
    > > > In tkprof, there is no sign of the query "select
    > > c4,c5
    > > > from t1 where..." query .
    > > >
    > > > Is that normal ?
    > > >
    > > > =====
    > > > Stephane Paquette
    > > > DBA Oracle
    > > > stephane_paquette_at_yahoo.com
    > > > spaquette_at_houra.fr
    > > > (33) 01 53 93 06 50
    > > >
    > > > __________________________________________________
    > > > Do You Yahoo!?
    > > > Kick off your party with Yahoo! Invites.
    > > > http://invites.yahoo.com/
    > > > --
    > > > Author: =?iso-8859-1?q?paquette=20stephane?=
    > > > INET: stephane_paquette_at_yahoo.com
    > > >
    > > > Fat City Network Services -- (858) 538-5051
    > > FAX: (858) 538-5051
    > > > San Diego, California -- Public Internet
    > > access / Mailing Lists
    > > >
    > >
    > --------------------------------------------------------------------
    > > > To REMOVE yourself from this mailing list, send an
    > > E-Mail message
    > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > > 'ListGuru') and in
    > > > the message BODY, include a line containing: UNSUB
    > > ORACLE-L
    > > > (or the name of mailing list you want to be
    > > removed from). You may
    > > > also send the HELP command for other information
    > > (like subscribing).
    >
    >
    > =====
    > Stephane Paquette
    > DBA Oracle
    > stephane_paquette_at_yahoo.com
    > spaquette_at_houra.fr
    > (33) 01 53 93 06 50
    >
    > __________________________________________________
    > Do You Yahoo!?
    > Send instant messages & get email alerts with Yahoo! Messenger.
    > http://im.yahoo.com/
    > --
    > Author: =?iso-8859-1?q?paquette=20stephane?=
    > INET: stephane_paquette_at_yahoo.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    > San Diego, California -- Public Internet access / Mailing
    > Lists
    > --------------------------------------------------------------------
    > To REMOVE yourself from this mailing list, send an E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB ORACLE-L
    > (or the name of mailing list you want to be removed from). You may
    > also send the HELP command for other information (like subscribing).