Re: What does "push a join predicate down" mean ?

From: Christian Antognini <christian.antognini_at_trivadis.com>
Date: Sat, 17 Jul 2004 00:17:11 +0200
Message-ID: <40f853bf@post.usenet.com>

• Post for FREE via your newsreader at post.usenet.com ****

> I read this stuff several times. And there is this
> hidden param. _PUSH_JOIN_UNION_VIEW that's specifically
> been set up to "enable pushing join predicate inside a
> union view" as X$KSPPI's doc. states it.
>
> But I understand *nothing* to the sense of "pushing a
> join down"..., and found nothing in the forums to enlighten
> me.

_PUSH_JOIN_UNION_VIEW is only used when a view containing a UNION ALL is joined with another table.

Here an example:

SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Linux: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

SQL> CREATE VIEW prs_name_v AS
  2 SELECT id, firstname, lastname
  3 FROM persons p1
  4 UNION ALL
  5 SELECT id, lastname, firstname
  6 FROM persons p2;

View created.

SQL> set autotrace trace exp

SQL> alter session set "_PUSH_JOIN_UNION_VIEW"=true;

Session altered.

SQL> SELECT lastname, firstname
  2 FROM addresses, prs_name_v
  3 WHERE addresses.prs_id = prs_name_v.id   4 AND city = 'Glattbrugg'
  5 AND street = 'Europastrasse';

Execution Plan

---

SELECT STATEMENT Optimizer=ALL_ROWS
  NESTED LOOPS
    TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES' (TABLE)       INDEX (RANGE SCAN) OF 'ADR_CITY$ZIP$STREET' (INDEX)     VIEW OF 'PRS_NAME_V' (VIEW)

      UNION-ALL (PARTITION)
        TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS' (TABLE)
          INDEX (UNIQUE SCAN) OF 'PRS_PK' (INDEX (UNIQUE))
        TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS' (TABLE)
          INDEX (UNIQUE SCAN) OF 'PRS_PK' (INDEX (UNIQUE))

SQL> alter session set "_PUSH_JOIN_UNION_VIEW"=false;

Session altered.

SQL> SELECT lastname, firstname
  2 FROM addresses, prs_name_v
  3 WHERE addresses.prs_id = prs_name_v.id   4 AND city = 'Glattbrugg'
  5 AND street = 'Europastrasse';

Execution Plan

---

SELECT STATEMENT Optimizer=ALL_ROWS
  HASH JOIN
    TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES' (TABLE)       INDEX (RANGE SCAN) OF 'ADR_CITY$ZIP$STREET' (INDEX)     VIEW OF 'PRS_NAME_V' (VIEW)

      UNION-ALL
        TABLE ACCESS (FULL) OF 'PERSONS' (TABLE)
        TABLE ACCESS (FULL) OF 'PERSONS' (TABLE)

As you can see in the second case the CBO is not able to push the join predicate (i.e. addresses.prs_id = prs_name_v.id) into the view to access the table PERSONS with an index scan. Therefore a FTS is performed.

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Fri Jul 16 2004 - 17:17:11 CDT