REPOST: Re: Query puzzler

Could be the original columns are all retrievable from indexes while the added columns have to be retrieved from the table itself.

What does EXPLAIN PLAN say in both cases? What is the event that is using up time in the second case (V$SESSION_EVENT)?

Jaap.

On Sat, 29 Dec 2001 01:15:47 GMT, hboswel1_at_bellsouth.net (Harry Boswell) wrote:

>On Fri, 28 Dec 2001 16:06:51 -0500, "Alan" <alanshein_at_erols.com>
>wrote:
>
>>Are the columns added to the SELECT clause or the WHERE clause?
>
>Select clause. Just pulling in two more columns from the A. table.
>
>Thanks,
>Harry
>
>>
>><hboswel1_at_bellsouth.net> wrote in message
>>news:3c2cae15.98363659_at_news1.lig.bellsouth.net...
>>> I've got a fairly complex query - which runs fine:
>>>
>>> (sorry about pasting the entire thing, but I'm baffled
>>> by what's happening)
>>>
>>> SELECT A.MASTER_PERSON_ID PERSON_ENSITE_ID,
>>> A.MASTER_PERSON_PREFIX_CODE PREFIX_ID,
>>> A.MASTER_PERSON_FIRST_NAME PERSON_FIRST_NAME,
>>> A.MASTER_PERSON_MIDDLE_INITIAL PERSON_MID_INITIAL,
>>> A.MASTER_PERSON_LAST_NAME PERSON_LAST_NAME,
>>> A.MASTER_PERSON_SUFFIX_CODE SUFFIX_ID,
>>> E.MASTER_AI_NAME PERSON_COMPANY,
>>> F.RELATIONSHIP_CODE RELATIONSHIP_TYPE_ID,
>>> A.TITLE_CODE TITLE_ID,
>>> A.PERSON_TYPE_CODE PERSON_TYPE_ID,
>>> B.ADDRESS_OR_PHONE WORK_PHONE,
>>> C.ADDRESS_OR_PHONE HOME_PHONE,
>>> D.ADDRESS_OR_PHONE EMAIL,
>>> G.PHYSICAL_ADDRESS_LINE_1 P_ADDR_1,
>>> G.PHYSICAL_ADDRESS_LINE_2 P_ADDR_2,
>>> G.PHYSICAL_ADDRESS_LINE_3 P_ADDR_3,
>>> G.PHYSICAL_ADDRESS_MUNICIPALITY P_CITY,
>>> G.PHYSICAL_ADDRESS_STATE_CODE P_STATE_ID,
>>> G.PHYSICAL_ADDRESS_ZIP P_ZIP,
>>> G.MAILING_ADDRESS_LINE_1 M_ADDR_1,
>>> G.MAILING_ADDRESS_LINE_2 M_ADDR_2,
>>> G.MAILING_ADDRESS_LINE_3 M_ADDR_3,
>>> G.MAILING_ADDRESS_MUNICIPALITY M_CITY,
>>> G.MAILING_ADDRESS_STATE_CODE M_STATE_ID,
>>> G.MAILING_ADDRESS_ZIP M_ZIP,
>>> A.START_DATE PERSON_START_DATE,
>>> A.END_DATE PERSON_END_DATE
>>> FROM PERSON A, PERSON_TELECOM B, PERSON_TELECOM C, PERSON_TELECOM D,
>>> AGENCY_INTEREST E, AGENCY_INTEREST_PERSON_XREF F, PERSON_ADDRESS G
>>> WHERE (( B.TELECOM_TYPE_CODE = 'wp') AND
>>> (A.MASTER_PERSON_ID = B.MASTER_PERSON_ID) AND
>>> (A.INT_DOC_ID = B.INT_DOC_ID) ) AND
>>> (( C.TELECOM_TYPE_CODE = 'hp') AND
>>> (A.MASTER_PERSON_ID = C.MASTER_PERSON_ID) AND
>>> (A.INT_DOC_ID = C.INT_DOC_ID) ) AND
>>> (( D.TELECOM_TYPE_CODE = 'em') AND
>>> (A.MASTER_PERSON_ID = D.MASTER_PERSON_ID) AND
>>> (A.INT_DOC_ID = D.INT_DOC_ID) ) AND
>>> (A.MASTER_PERSON_ID = F.MASTER_PERSON_ID) AND
>>> (A.INT_DOC_ID = F.INT_DOC_ID) AND
>>> (E.MASTER_AI_ID = E.MASTER_AI_ID) AND
>>> (E.INT_DOC_ID = F.INT_DOC_ID) AND
>>> ( (A.MASTER_PERSON_ID = G.MASTER_PERSON_ID) AND
>>> (A.INT_DOC_ID = G.INT_DOC_ID) )
>>>
>>>
>>> As I said, this runs fine. But if I add two more columns from
>>> the PERSON table, it wanders off into Limbo Land and never
>>> completes (never == runs at least 1 1/2 hours with no response).
>>> What would adding two more columns possibly do to blow this out of
>>> the water?
>>>
>>> Thanks,
>>> Harry Boswell
>>>
>>
>>
>
>

• WAS CANCELLED BY =======: Path: news.sol.net!spool1-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!uunet!ash.uu.net!sac.uu.net!lax.uu.net!news.navix.net!u-n-c-a-n-c-e-l-l-e-r From: j.w.vandijk_at_hetnet.nl (Jaap W. van Dijk) Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server Subject: cmsg cancel <3c2f0ddc.2400435_at_news.freeler.nl> Control: cancel <3c2f0ddc.2400435_at_news.freeler.nl> Date: Mon, 31 Dec 2001 05:14:02 GMT Organization: Navix Internet Subscribers Lines: 2 Message-ID: <cancel.3c2f0ddc.2400435_at_news.freeler.nl> NNTP-Posting-Host: 166.102.15.34 X-Trace: iac5.navix.net 1009788819 26548 166.102.15.34 (31 Dec 2001 08:53:39 GMT) X-Complaints-To: abuse_at_navix.net NNTP-Posting-Date: 31 Dec 2001 08:53:39 GMT X-No-Archive: yes Comment: Anarchy! Fuck You! X-Commentary: I love NewsAgent 1.10, Sandblaster Build 74 (19 March 1999) and the Polaris Cancel Engine V. 6.1 X-Unacanc3l: yes

This message was cancelled from within Mozilla...not Received on Sun Dec 30 2001 - 06:54:03 CST