oracle 子查询解嵌套和谓词推入（sqlsubquery unnest and pre_push）

来源：互联网发布：复旦金融硕士知乎编辑：程序博客网时间：2024/06/11 05:53

Subquery Unnesting In subquery unnesting, the optimizer transforms a nested query

into an equivalent join statement, and then optimizes the join. This transformation

enables the optimizer to take advantage ofthe join optimizer technique. The optimizer

can perform thistransformation only if the resulting join statement is guaranteed to

return exactly the samerows as the origin al statement, and if subqueries do not

contain aggregatefunctions such as AVG .

If the optimizer cannot transform a complexst atement into a join statement, it selects

execution plans for the parent statementand the subquery as though they were

separate statements. The optimizer then ex ecutesthe subquery and uses the rows

returned to execute the parent query. Toimprove execution speed of the overall query

plan, the optimizer orders the subplansefficiently.

Predicate Pushing In predicate pushing, the optimizer"pushes" the relevant predicates

from the containing query block into theview query block. For views that are not

merged, this technique improves the subplanof the unmerged view because the

database can use the pushed-in predicatesto access indexes or to use as filters.

hr@ORCL>

hr@ORCL> create table t1 as select *from dba_objects where object_id<3000;

Table created.

hr@ORCL> create table t2 as select *from dba_objects ;

Table created.

hr@ORCL> create index idx_id1_t2 ont2(object_id,owner);

Index created.

hr@ORCL> create index idx_id1_t1 ont1(object_id);

Index created.

hr@ORCL> analyze table t1 computestatistics for all indexed columns;

Table analyzed.

hr@ORCL> analyze table t2 computestatistics for all indexed columns;

Table analyzed.

hr@ORCL> set autot traceonly

hr@ORCL> select object_id from t1 whereexists(

2 select 1 from t2 wheret1.object_id=t2.object_id-10);

2874 rows selected.

Execution Plan

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

Plan hash value: 2210107937

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

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

| 0| SELECT STATEMENT | | 1 | 17 | 294 (1)| 00:00:04 |

|* 1| HASH JOIN SEMI | | 1 | 17 | 294 (1)| 00:00:04 |

| 2| INDEX FAST FULL SCAN| IDX_ID1_T1| 2931 | 38103 | 5 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL | T2 | 62741 | 245K| 289 (1)| 00:00:04 |

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

Predicate Information (identified byoperation id):

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

1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID"-10)

Note

-----

-dynamic sampling used for this statement (level=2)

Statistics

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

32 recursive calls

0 db block gets

1345 consistent gets

7 physical reads

0 redo size

50399 bytes sent via SQL*Net toclient

2620 bytes received via SQL*Netfrom client

193 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

2874 rows processed

上面的例子中，oracle自动将子查询进行了展开，下面我们使用no_unnest hint来不让oracle在这里进行子查询展开，就通过
嵌套在子查询里面，那么这样比如就会走fiter了，如下：

hr@ORCL> alter system flush shared_pool;

System altered.

hr@ORCL> select object_id

2 from t1

3 where exists (select /*+no_unnest */

4 1

5 from t2

6 wheret1.object_id=t2.object_id-10);

2874 rows selected.

Execution Plan

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

Plan hash value: 895956251

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

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

| 0| SELECT STATEMENT | | 1 | 3 | 4414 (1)| 00:00:53 |

|* 1| FILTER | | | | | |

| 2| TABLE ACCESS FULL| T1 | 2931 | 8793 | 13 (0)| 00:00:01 |

|* 3| TABLE ACCESS FULL| T2 | 627 | 2508 | 3 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

1- filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2"WHERE

"T2"."OBJECT_ID"-10=:B1))

3- filter("T2"."OBJECT_ID"-10=:B1)

Note

-----

-dynamic sampling used for this statement (level=2)

Statistics

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

735 recursive calls

0 db block gets

123829 consistent gets

1090 physical reads

0 redo size

50399 bytes sent via SQL*Net toclient

2620 bytes received via SQL*Netfrom client

193 SQL*Net roundtrips to/fromclient

20 sorts (memory)

0 sorts (disk)

2874 rows processed

那么什么情况下存在子查询的情况，oracle不会进行子查询的展开呢

l 包含rownum

hr@ORCL> select object_id

2 fromt1

3 where exists (select 1

4 fromt2 where t1.object_id=t2.object_id-10 and rownum<20);

2874 rows selected.

Execution Plan

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

Plan hash value: 4265634519

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 4414 (1)| 00:00:53 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | T1 | 2931 | 8793 | 13 (0)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

|* 4 | TABLE ACCESS FULL| T2 | 627 | 2508 | 3 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 - filter( EXISTS (SELECT 0 FROM"T2" "T2" WHERE ROWNUM<20 AND

"T2"."OBJECT_ID"-10=:B1))

3 - filter(ROWNUM<20)

4 -filter("T2"."OBJECT_ID"-10=:B1)

l 包含cube rollup 等函数

hr@ORCL> select object_id

2 fromt1 where exists(select 1 ,sum(object_id) from t2 where t1.object_id =t2.object_id-10 group by rollup(t2.object_id-10));

Execution Plan

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

Plan hash value: 3739889183

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 424K (1)| 01:24:58 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | T1 | 2931 | 8793 | 13 (0)| 00:00:01 |

| 3 | SORT GROUP BY ROLLUP| | 627 | 2508 | 290 (1)| 00:00:04 |

|* 4 | TABLE ACCESS FULL | T2 | 627 | 2508 | 289 (1)| 00:00:04 |

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

Predicate Information(identified by operation id):

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

1 - filter( EXISTS (SELECT 0 FROM"T2" "T2" WHERE

"T2"."OBJECT_ID"-10=:B1 GROUP BY ROLLUP("T2"."OBJECT_ID"-10)))

4 -filter("T2"."OBJECT_ID"-10=:B1)

包含union all 、union intersect、minus

hr@ORCL> selectobject_id

2 fromt1

3 where exists (select 1 from t2 where t1.object_id=t2.object_id-10

4 union all

5 select object_id from t2 where object_id<500);

2931 rows selected.

Execution Plan

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

Plan hash value: 2568596142

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

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

| 0 | SELECT STATEMENT | | 1 | 4 | 427K (1)| 01:25:35 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | T1 | 2931 | 11724 | 13 (0)| 00:00:01 |

| 3 | UNION-ALL | | | | | |

|* 4 | TABLE ACCESS FULL| T2 | 725 | 3625 | 289 (1)| 00:00:04 |

|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 483 | 2415 | 3 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 - filter( EXISTS ( (SELECT 1 FROM"T2" "T2" WHERE

"T2"."OBJECT_ID"-10=:B1) UNION ALL (SELECT "OBJECT_ID" FROM"T2" "T2"

WHERE"OBJECT_ID"<500)))

4 -filter("T2"."OBJECT_ID"-10=:B1)

5 - access("OBJECT_ID"<500)

union

1 select object_id

2 fromt1

3 where exists (select 1 from t2 where t1.object_id=t2.object_id-10

4 union

5* select object_id from t2 whereobject_id<500)

hr@ORCL> /

2931 rows selected.

Execution Plan

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

Plan hash value: 583540251

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

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

| 0 | SELECT STATEMENT | | 1 | 4 | 430K (2)| 01:26:10 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | T1 | 2931 | 11724 | 13 (0)| 00:00:01 |

| 3 | SORT UNIQUE | | 1208 | 6040 | 294 (3)| 00:00:04 |

| 4 | UNION-ALL | | | | | |

|* 5 | TABLE ACCESS FULL| T2 | 725 | 3625 | 289 (1)| 00:00:04 |

|* 6 | INDEX RANGE SCAN | IDX_ID1_T2 | 483 | 2415 | 3 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 - filter( EXISTS ( (SELECT 1 FROM"T2" "T2" WHERE

"T2"."OBJECT_ID"-10=:B1)UNION (SELECT"OBJECT_ID" FROM "T2" "T2" WHERE

"OBJECT_ID"<500)))

5 -filter("T2"."OBJECT_ID"-10=:B1)

6 - access("OBJECT_ID"<500)

minus

1 select object_id

2 fromt1

3 where exists (select 1 from t2 where t1.object_id=t2.object_id-10

4 minus

5* select object_id from t2 whereobject_id<500)

hr@ORCL> /

2874 rows selected.

Execution Plan

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

Plan hash value: 1945478487

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

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

| 0 | SELECT STATEMENT | | 1 | 4 | 430K (2)| 01:26:10 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | T1 | 2931 | 11724 | 13 (0)| 00:00:01 |

| 3 | MINUS | | | | | |

| 4 | SORT UNIQUE NOSORT| | 725 | 3625 | 290 (1)| 00:00:04 |

|* 5 | TABLE ACCESS FULL| T2 | 725 | 3625 | 289 (1)| 00:00:04 |

| 6 | SORT UNIQUE NOSORT| | 483 | 2415 | 4 (25)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | IDX_ID1_T2 | 483 | 2415 | 3 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 - filter( EXISTS ( (SELECT 1 FROM"T2" "T2" WHERE

"T2"."OBJECT_ID"-10=:B1)MINUS (SELECT"OBJECT_ID" FROM "T2" "T2" WHERE

"OBJECT_ID"<500)))

5 -filter("T2"."OBJECT_ID"-10=:B1)

7 - access("OBJECT_ID"<500)

视图合并

hr@ORCL> create table t3as select * from dba_objects where object_id <10000;

Table created.

hr@ORCL> analyze table t3compute statistics;

Table analyzed.

hr@ORCL> CREATE OR REPLACEVIEW t_view

2 AS

3 SELECT t1.* from t1,t2

4 where t1.object_id=t2.object_id;

View created.

hr@ORCL> select/*+push_pred(t_view)*/ t3.object_name

2 fromt3,t_view

3 where t3.object_name=t_view.object_name(+)

4 andt3.object_id=888;

Execution Plan

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

Plan hash value: 3014823912

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

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

| 0 | SELECT STATEMENT | | 1 | 23 | 53 (0)| 00:00:01 |

| 1 | NESTED LOOPS OUTER | | 1 | 23 | 53 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW PUSHED PREDICATE | T_VIEW | 1 | 2 | 14 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 27 | 14 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 22 | 13 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | IDX_ID1_T2| 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

2 -filter("T3"."OBJECT_ID"=888)

5 -filter("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")

6 -access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

hr@ORCL> selectt3.object_name

2 fromt3,t_view

3 where t3.object_name = t_view.object_name(+)

4 andt3.object_id=888;

Execution Plan

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

Plan hash value: 1962414821

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 115 (2)| 00:00:02 |

|* 1 | HASH JOIN OUTER | | 1 | 87 | 115 (2)| 00:00:02 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW |T_VIEW | 2931 | 188K| 75 (2)| 00:00:01 |

|* 4 | HASH JOIN | | 2931 | 79137 | 75 (2)| 00:00:01 |

| 5 | TABLE ACCESS FULL | T1 | 2931 | 64482 | 13 (0)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 72498 | 353K| 61 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 -access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))

2 -filter("T3"."OBJECT_ID"=888)

4 -access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

从上面可以看到t3的条件进行了fiter操作，并没有推进到view里面去，下面我们使用push_pred hint进行强制推进下

hr@ORCL> select/*+push_pred(t_view)*/ t3.object_name

2 fromt3,t_view

3 where t3.object_name=t_view.object_name(+)

4 andt3.object_id=888;

Execution Plan

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

Plan hash value: 3014823912

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

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

| 0 | SELECT STATEMENT | | 1 | 23 | 53 (0)| 00:00:01 |

| 1 | NESTED LOOPS OUTER | | 1 | 23 | 53 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW PUSHED PREDICATE | T_VIEW | 1 | 2 | 14 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 27 | 14 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 22 | 13 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | IDX_ID1_T2| 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

2 -filter("T3"."OBJECT_ID"=888)

5 -filter("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")

6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

使用merge

1 select /*+merge(t_view)*/ t3.object_name

2 fromt3,t_view

3 where t3.object_name=t_view.object_name(+)

4* and t3.object_id=888

hr@ORCL> /

Execution Plan

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

Plan hash value: 1962414821

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 115 (2)| 00:00:02 |

|* 1 | HASH JOIN OUTER | | 1 | 87 | 115 (2)| 00:00:02 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW |T_VIEW | 2931 | 188K| 75 (2)| 00:00:01 |

|* 4 | HASH JOIN | | 2931 | 79137 | 75 (2)| 00:00:01 |

| 5 | TABLE ACCESS FULL | T1 | 2931| 64482 | 13 (0)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 72498 | 353K| 61 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 -access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))

2 -filter("T3"."OBJECT_ID"=888)

4 -access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

no merge

1 select /*+no_merge(t_view)*/ t3.object_name

2 fromt3,t_view

3 where t3.object_name=t_view.object_name(+)

4* and t3.object_id=888

hr@ORCL> /

Execution Plan

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

Plan hash value: 1962414821

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 115 (2)| 00:00:02 |

|* 1 | HASH JOIN OUTER | | 1 | 87 | 115 (2)| 00:00:02 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW |T_VIEW | 2931 | 188K| 75 (2)| 00:00:01 |

|* 4 | HASH JOIN | | 2931 | 79137 | 75 (2)| 00:00:01 |

| 5 | TABLE ACCESS FULL | T1 | 2931 | 64482 | 13 (0)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 72498 | 353K| 61 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 -access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))

2 -filter("T3"."OBJECT_ID"=888)

4 -access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

当sql查询中出现view，实际上使用merge/no_merge和push_pred，no_push_pred我感觉有些类似。

如下一段关于merge 和push_pred的描述：

Merge /no_merge: if you use a complex view (e.g. aggregate view, or join view) in yourquery, should you rewrite
the query to merge the tables in the view into a single from clause with allthe other tables (merge), or should
you evaluate the view to produce a “standalone” result set and then join theresult set to the remaining tables (no_merge).

Push_pred/ no_push_pred: If you have a non-mergeable view (possible because of ano_merge hint) in your query, how
should you operate the join from other tables; should you create one large viewresult and join it once (no_push_pred)
or should you push the join predicate down into the view definition andrecreate the view result set for every driving
row from another table (push_pred).

merge 就是把view展开，那么你查看执行计划时就看不到viewacces patch的信息了，no_merge则相反。
所以push_pred（谓词推入）就是是说将谓词条件推入到view中。

由于要发现pushpred操作，必须保证试图不能被merge，所以通常在实验观察时会同时使用no_mergehint.

细心一点我们还能发现，针对谓词推入的操作，其外部操作只能是nestloop。

到最后我们可以简单的总结下，针对如下情况，子查询是不能展开的：

1.子查询存在 ROWNUM
2.子查询存在 CUBE,ROLLUP
3.子查询存在 UNION,UNION ALL,INTERSECT、MINUS
4.子查询存在 STARTWITH ,CONNECT BY字句

针对右外连接的情况下，oracle优化器默认也不会进行view merge操作

1 select t3.object_name

2 fromt3,t_view

3 where t3.object_name=t_view.object_name(+)

4* and t3.object_id=888

hr@ORCL> /

Execution Plan

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

Plan hash value: 1962414821

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 115 (2)| 00:00:02 |

|* 1 | HASH JOIN OUTER | | 1 | 87 | 115 (2)| 00:00:02 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW |T_VIEW | 2931 | 188K| 75 (2)| 00:00:01 |

|* 4 | HASH JOIN | | 2931 | 79137 | 75 (2)| 00:00:01 |

| 5 | TABLE ACCESS FULL | T1 | 2931 | 64482 | 13 (0)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 72498 | 353K| 61 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 -access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))

2 -filter("T3"."OBJECT_ID"=888)

4 -access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

针对上面的情况使用merge hint 结果也是一样

1 select /*+merge(t_view)*/ t3.object_name

2 fromt3,t_view

3 where t3.object_name=t_view.object_name(+)

4* and t3.object_id=888

hr@ORCL> /

Execution Plan

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

Plan hash value: 1962414821

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

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

| 0 | SELECT STATEMENT | | 1 | 87 | 115 (2)| 00:00:02 |

|* 1 | HASH JOIN OUTER | | 1 | 87 | 115 (2)| 00:00:02 |

|* 2 | TABLE ACCESS FULL | T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 3 | VIEW |T_VIEW | 2931 | 188K| 75 (2)| 00:00:01 |

|* 4 | HASH JOIN | | 2931 | 79137 | 75 (2)| 00:00:01 |

| 5 | TABLE ACCESS FULL | T1 | 2931 | 64482 | 13 (0)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 72498 | 353K| 61 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

1 -access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))

2 -filter("T3"."OBJECT_ID"=888)

4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

1 select t3.object_name

2 from t3,t_view

3 where t3.object_name=t_view.object_name

4* and t3.object_id=888

5 /

Execution Plan

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

Plan hash value: 2983785075

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

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

| 0 | SELECT STATEMENT | | 1 | 48 | 54 (2)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 48 | 54 (2)| 00:00:01 |

|* 2 | HASH JOIN | | 1 | 43 | 53 (2)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T3 | 1 | 21 | 39 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| T1 | 2931 | 64482 | 13 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | IDX_ID1_T2| 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

2 -access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")

3 - filter("T3"."OBJECT_ID"=888)

5 -access("T1"."OBJECT_ID"="T2"."OBJECT_ID")