Re: Left outer join

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 14 Nov 2003 22:53:27 +0000
Message-ID: <bnlarv0olqickm2rbkc8usobbojft8hj8d@4ax.com>

On Fri, 14 Nov 2003 22:39:08 +0100, "Erik" <no_at_spam.com> wrote:

>> On Thu, 13 Nov 2003 22:53:39 +0100, "Erik" <no_at_spam.com> wrote:
>>
>>>> Predicate Information (identified by operation id):
>>>> ---------------------------------------------------
>>>>
>>>> 1 - filter("CC"."ATT"='C1')
>>>> 5 - access("AC"."ACCOUNT"="CC"."ACCOUNT"(+))
>>>
>>>How do I do to see this information?
>>
>> Provided you have Oracle 9.2, look up DBMS_XPLAN
>
>Is there any possibility to see the Predicate information with 8.1.7?

 Only 9.2 explain plan populates the new ACCESS_PREDICATES and FILTER_PREDICATES columns of the PLAN_TABLE (9.0 doesn't either).

 There is an awkward way of getting hold of the predicates without explain plan, using an event and table:

SQL> create table kkoipt_table (
  2 n1 number,
  3 v1 varchar2(255)
  4 );

Table created

SQL> alter session set events '10060 trace name context forever';

Session altered

SQL> select /*+ALL_ROWS*/ *
  2 from ar1 ac, ar2 cc
  3 where ac.account = cc.account (+)
  4 and cc.att = 'C1';

ACCOUNT ACCOUNT ATTVALUE ATT
------- ------- ------------ ---

    100 100 A COSTC C1 SQL> select * from kkoipt_table order by n1;

N1 V1

-- ------------------------------------

 1 Table:
 2 AR2
 3 frofand
 4 "CC"."ATT"='C1'
 5 Table:
 6 AR1
 7 frofkks[i] (index start key)
 8 "AC"."ACCOUNT"="CC"."ACCOUNT"
 9 frofkke[i] (index stop key)
10 "AC"."ACCOUNT"="CC"."ACCOUNT" 10 rows selected

 Only works for CBO, only populates the table on hard parses, and since it's undocumented there's no guarantees on it.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Received on Fri Nov 14 2003 - 16:53:27 CST