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

mohd_noeud_article, moh_marque,moh_articles_associes are tables, mohv_article is a view.

• Brian Wisniewski <brian_wisniewski_at_yahoo.com> a écrit : > 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
  > >
  >
  

  ---