RE: Hint for self-join connect by

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Wed, 2 May 2007 08:43:20 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360326BAB0@CWYMSX04.Corp.Acxiom.net>

With ACCT_ID having about 450,000 at '_MERGED_' and the other 70 million or so being unique, a histogram on this could should help. It does, at least most of the times. I couldn't get the right results under 9.2.0.8, but then again it may be that 5% sample size with the histogram (skewonly) wasn't enough.

I bailed on this and decided to review the SQL, to see if there's a different approach that can be used. I came up with the following, which for now takes around 5 minutes under 9.2.0.6 and 9.2.0.8, with any CPU patch (much faster than the 45 minute "good" plan and 4 to 8 hrs "bad" plan):

70 million rows with FORWARD_SURROGATE_ID is NULL. These at best could
only be the ROOT_SURROGATE_ID, so skill those for a second, equi-join. */ CREATE TABLE dch_work_surr_mgmt_two PCTFREE 0 NOLOGGING PARALLEL AS SELECT SUBSTR(sys_connect_by_path(surrogate_id, '|'), 2) tree, LEVEL AS lev , forward_surrogate_id FROM (SELECT acct_id, surrogate_id, forward_surrogate_id FROM dch_work_surr_mgmt_one WHERE forward_surrogate_id IS NOT NULL) START WITH acct_id = '&ACCT_MERGE' CONNECT BY PRIOR forward_surrogate_id = surrogate_id;

EXEC
dbms_stats.gather_table_stats(USER,'DCH_WORK_SURR_MGMT_TWO',NULL,1);

CREATE TABLE dch_work_surr_mgmt_three PCTFREE 0 NOLOGGING PARALLEL AS SELECT surrogate_id, root_surrogate_id
FROM (SELECT surrogate_id, root_surrogate_id

             , ROW_NUMBER () OVER (PARTITION BY surrogate_id 
                                       ORDER BY lev DESC) AS rn
          FROM (SELECT CAST(SUBSTR(tree, 1, INSTR(tree, '|') - 1) AS
VARCHAR2(11)) AS surrogate_id
                     , CAST(SUBSTR(SUBSTR(tree, INSTR(tree, '|', -1)),
2) AS VARCHAR2(11)) AS root_surrogate_id
                     , lev
                  FROM (SELECT d2.tree || '|' || d1.surrogate_id tree
                             , lev + 1 lev
                          FROM dch_work_surr_mgmt_one d1
                             , dch_work_surr_mgmt_two d2
                         WHERE d2.forward_surrogate_id = d1.surrogate_id
                           AND d1.forward_surrogate_id IS NULL)
                 WHERE INSTR(tree, '|') > 0
               )
       )

WHERE rn = 1;

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 12:36 PM
> To: Herring Dave - dherri
> Cc: amit poddar; oracle-l
> Subject: 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.
*************************************************************************

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 02 2007 - 08:43:20 CDT