RE: FW: [ Danone ] - Hung Oracle Query

How many distinct values does the city_code have? If ,,say around 10 or so, a bit map index may be something to consider.  

• Kirti

> -----Original Message-----
> From: lerobe - Lee Robertson [SMTP:LEROBE_at_acxiom.co.uk]
> Sent: Wednesday, February 14, 2001 11:01 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: FW: [ Danone ] - Hung Oracle Query
>
> yep, and before everyone else tries to waste time looking at this, I
> mentioned to the developer that he was using a non-indexed column (a.city
> code) in the where clause and that is why he is doing a full table scan on
> one of the large tables (address). His reply, exactly, now how do I get
> around this, AAARRGGHHH !!!! This is why when he removes references to the
> x_city table the query runs really quickly.
>
> Once again thanks for everyone who has had a crack at this and apologies
> for
> wasting your time.
>
> Off to sharpen my hatchet.
>
> > Lee Robertson
> > Acxiom
> > Tel: 0191 525 7344
> > Fax: 0191 525 7007
> > Email: lerobe_at_acxiom.co.uk
> >
>
>
> -----Original Message-----
> Sent: 14 February 2001 15:18
> To: Multiple recipients of list ORACLE-L
>
>
> Have you rerun the analyzer to generate new statistics? Just a thot...RBG
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, February 14, 2001 9:26 AM
>
>
> > Thanks for the responses from you guys. I went back to our developer
> with
> > your suggestions and he said, wait for this one !!!
> >
> > Oh I did select from all the tables in the query, I just chopped them
> out
> > when I passed it onto you as I didn't think it was necessary.
> >
> > Original problem was ...
> > Since the database was bounced Thursday morning I have a query which no
> > > longer runs - it just hangs. If I take out the join to X_CITY it
> works
> > > fine. I haven't changed any indexes and all the indexes are correct.
> > > Any ideas ?
> >
> > Real SQL is as follows.
> >
> > SELECT i.surname,
> > i.forename,
> > a.address_line_1,
> > a.address_line_2,
> > a.address_line_3,
> > a.address_line_4,
> > a.postcode,
> > c.city_desc,
> > cp.phone_number,
> > ce.email_address,
> > i.date_of_birth,
> > i.gender_code,
> > i.ind_is_child,
> > i.date_of_birth_calc_code,
> > i.individual_id,
> > h.household_id
> > FROM changed_hh_keys chh,
> > household h,
> > address a,
> > x_city c,
> > individual i,
> > consumer_phone cp,
> > consumer_email ce
> > WHERE h.household_id = chh.household_id
> > AND a.household_id = h.household_id
> > AND i.household_id = h.household_id
> > AND c.city_code = a.city_code
> > AND cp.consumer_id(+) = i.household_id
> > AND ce.consumer_id(+) = i.individual_id
> > AND i.consumer_type_code != 'H';
> >
> > and the plan is..
> >
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=7224597105017
> > > Card=8024558972338880000000 Bytes=1492567968855030000000000)
> > > NESTED LOOPS (Cost=7224597105017 Card=8024558972338880000000
> > > Bytes=1492567968855030000000000)
> > > MERGE JOIN (OUTER) (Cost=7224597105017 Card=501781749669299000
> > > Bytes=86808242692788700000)
> > > SORT (JOIN) (Cost=7224597102585 Card=69188658282644
> > > Bytes=11070185325223000)
> > > NESTED LOOPS (Cost=3327368730 Card=69188658282644
> > > Bytes=11070185325223000)
> > > MERGE JOIN (Cost=3327368730 Card=6918865828264370
> > > Bytes=1017073276754860000)
> > > SORT (JOIN) (Cost=3327245772 Card=55237676133
> > > Bytes=6683758812093)
> > > NESTED LOOPS (Cost=831 Card=55237676133
> Bytes=6683758812093)
> > > MERGE JOIN (OUTER) (Cost=831 Card=1054167
> Bytes=86441694)
> > > SORT (JOIN)
> > > TABLE ACCESS (FULL) OF INDIVIDUAL (Cost=826
> > > Card=1285570 Bytes=88704330)
> > > SORT (JOIN) (Cost=4 Card=82 Bytes=1066)
> > > TABLE ACCESS (FULL) OF CONSUMER_EMAIL (Cost=1
> Card=82
> > > Bytes=1066)
> > > INDEX (UNIQUE SCAN) OF HOUSEHOLD_PK (UNIQUE)
> > > SORT (JOIN) (Cost=122958 Card=12525628 Bytes=325666328)
> > > TABLE ACCESS (FULL) OF ADDRESS (Cost=23275 Card=12525628
> > > Bytes=325666328)
> > > INDEX (UNIQUE SCAN) OF X_CITY_PK (UNIQUE)
> > > SORT (JOIN) (Cost=2432 Card=725237 Bytes=9428081)
> > > INDEX (FULL SCAN) OF CONSUMER_PHONE_PK (UNIQUE) (Cost=26
> > > Card=725237 Bytes=9428081)
> > > INDEX (UNIQUE SCAN) OF CHANGED_HH_KEYS_PK (UNIQUE)
> >
> > > Lee Robertson
> > > Acxiom
> > > Tel: 0191 525 7344
> > > Fax: 0191 525 7007
> > > Email: lerobe_at_acxiom.co.uk
> > >
> >
> >
> > -----Original Message-----
> > Sent: 09 February 2001 17:52
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Why do you even have the tables consumer_phone and consumer_email
> > included in the query? Obviously you don't care if they have entries in
> > them, and you're not selecting any info out of them. Remove them and
> see
> > what happens.
> >
> > >>> LEROBE_at_acxiom.co.uk 02/09/01 11:41AM >>>
> > Can someone have alook at the problem below please and advise. I am
> stumped.
> >
> > Regards
> >
> >
> > > -----Original Message-----
> > > From: jmydde - James Myddelton
> > > Sent: 09 February 2001 16:33
> > > To: lerobe - Lee Robertson
> > > Cc: rkilbe - Robert Kilbey
> > > Subject: [ Danone ] - Hung Oracle Query
> > >
> > >
> > > Lee,
> > >
> > > Since the database was bounced Thursday morning I have a query which
> no
> > > longer runs - it just hangs. If I take out the join to X_CITY it
> works
> > > fine. I haven't changed any indexes and all the indexes are correct.
> > > Any ideas ?
> > >
> > > James
> > >
> > > SELECT i.surname
> > > FROM changed_hh_keys chh,
> > > household h,
> > > address a,
> > > x_city c,
> > > individual i,
> > > consumer_phone cp,
> > > consumer_email ce
> > > WHERE h.household_id = chh.household_id
> > > AND a.household_id = h.household_id
> > > AND i.household_id = h.household_id
> > > AND i.consumer_type_code != 'H'
> > > AND c.city_code = a.city_code
> > > AND cp.consumer_id(+) = i.household_id
> > > AND ce.consumer_id(+) = i.individual_id;
> > >
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=7224597105017
> > > Card=8024558972338880000000 Bytes=1492567968855030000000000)
> > > NESTED LOOPS (Cost=7224597105017 Card=8024558972338880000000
> > > Bytes=1492567968855030000000000)
> > > MERGE JOIN (OUTER) (Cost=7224597105017 Card=501781749669299000
> > > Bytes=86808242692788700000)
> > > SORT (JOIN) (Cost=7224597102585 Card=69188658282644
> > > Bytes=11070185325223000)
> > > NESTED LOOPS (Cost=3327368730 Card=69188658282644
> > > Bytes=11070185325223000)
> > > MERGE JOIN (Cost=3327368730 Card=6918865828264370
> > > Bytes=1017073276754860000)
> > > SORT (JOIN) (Cost=3327245772 Card=55237676133
> > > Bytes=6683758812093)
> > > NESTED LOOPS (Cost=831 Card=55237676133
> Bytes=6683758812093)
> > > MERGE JOIN (OUTER) (Cost=831 Card=1054167
> Bytes=86441694)
> > > SORT (JOIN)
> > > TABLE ACCESS (FULL) OF INDIVIDUAL (Cost=826
> > > Card=1285570 Bytes=88704330)
> > > SORT (JOIN) (Cost=4 Card=82 Bytes=1066)
> > > TABLE ACCESS (FULL) OF CONSUMER_EMAIL (Cost=1
> Card=82
> > > Bytes=1066)
> > > INDEX (UNIQUE SCAN) OF HOUSEHOLD_PK (UNIQUE)
> > > SORT (JOIN) (Cost=122958 Card=12525628 Bytes=325666328)
> > > TABLE ACCESS (FULL) OF ADDRESS (Cost=23275 Card=12525628
> > > Bytes=325666328)
> > > INDEX (UNIQUE SCAN) OF X_CITY_PK (UNIQUE)
> > > SORT (JOIN) (Cost=2432 Card=725237 Bytes=9428081)
> > > INDEX (FULL SCAN) OF CONSUMER_PHONE_PK (UNIQUE) (Cost=26
> > > Card=725237 Bytes=9428081)
> > > INDEX (UNIQUE SCAN) OF CHANGED_HH_KEYS_PK (UNIQUE)
> >
> >
> > The information contained in this communication is
> > confidential, is intended only for the use of the recipient
> > named above, and may be legally privileged. If the reader
> > of this message is not the intended recipient, you are
> > hereby notified that any dissemination, distribution or
> > copying of this communication is strictly prohibited.
> > If you have received this communication in error, please
> > re-send this communication to the sender and delete the
> > original message or any copy of it from your computer
> > system.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: lerobe - Lee Robertson
> > INET: LEROBE_at_acxiom.co.uk
> >
> > 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Tim Sawmiller
> > INET: sawmillert_at_state.mi.us
> >
> > 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: lerobe - Lee Robertson
> > INET: LEROBE_at_acxiom.co.uk
> >
> > 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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ruth Gramolini
> INET: rgramolini_at_tax.state.vt.us
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: lerobe - Lee Robertson
> INET: LEROBE_at_acxiom.co.uk
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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).