Re: Hint for self-join connect by

That doesn't seem to tally with any of your plans - neither has both id 5 and 6 with an asterisk. May you please check it out and repost both plans with the predicate infos ?

It would also interesting to know num_distinct, num_null and density (from dba_tab_columns) for the columns
 acct_id, forward_surrogate_id and surrogate_id.

450k out of 70M - that's 0.6%. Maybe an index on acct_id, forward_surrogate_id
may help the first selection, and another on surrogate_id, forward_surrogate_id
*might* help the hierarchy visit (or at least turn the FTS into an index FFS). Why not giving it a shot.

How many levels, on average, has your connect by ?

I meant gathering at 100% just to experiment, not as standard practice - even if 70million is not very big, but of course it depends on the HW and the other activities on the system.

On 5/1/07, Herring Dave - dherri <Dave.Herring_at_acxiom.com> wrote:
> Got it:
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("from$_subquery$_001"."RN"=1)
> 3 - filter(ROW_NUMBER() OVER ( PARTITION BY
> SUBSTR("from$_subquery$_003"."TREE",1,INSTR("from$_subquery$_003"."TREE"
> ,'|')-1)
> ORDER BY "from$_subquery$_003"."LEV" DESC )<=1)
> 4 - filter(INSTR("from$_subquery$_003"."TREE",'|')>0)
> 5 - filter("DCH_WORK_SURR_MGMT_ONE"."ACCT_ID"='_MERGED_')
> 6 - filter("DCH_WORK_SURR_MGMT_ONE"."ACCT_ID"='_MERGED_')
>
> I haven't tried gathering at 100%, mostly because there are 70 million
> rows. I do plan on trying various histogram options on ACCT_ID.
>
> As for the count of '_MERGED_' rows, its about 450,000.
>
> Dave
> ___________________________________
>
> David C. Herring, DBA | A c x i o m Delivery Center Organization
>
> 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax
>
> > -----Original Message-----
> > From: Alberto Dell'Era [mailto:alberto.dellera_at_gmail.com]
> > Sent: Tuesday, May 01, 2007 11:14 AM
> > To: Herring Dave - dherri
> > Cc: amit poddar; oracle-l
> > Subject: Re: Hint for self-join connect by
> >
> > I think Amit was asking for the "filter predicates" section at the
> > end of the explain plan ;)
> >
> > By the way, have you tried collecting statistics at 100%, to see
> > whether that makes any difference ?
> >
> > How many rows (starting rows) with acct_id = '_MERGED_' there are
> > in the tables - a few, many, almost all ?
> ***************************************************************************
> 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 resend this
> communication to the sender and delete the original message or any copy
> of it from your computer system.
>
> Thank You.
> ****************************************************************************
>

-- 
Alberto Dell'Era
"dulce bellum inexpertis"
--
http://www.freelists.org/webpage/oracle-l