17 | 10 | 2017
Latest Articles
Popular Articles

SQL Tuning

Query Transformations : Predicate Pushing

User Rating:  / 0
PoorBest 

Predicate Pushing ( into views )



Predicate pushing did not happen by default in Oracle 8i. In 8i the hidden parameter "_push_join_predicate" defaults to false whereas it defaults to true in 9i, 10G and 11G.

Predicate pushing is part from query transformations. Query transformation was based on heuristic rules in 9i. From 10G R1 however by default query transformations are cost based since "_optimizer_cost_based_transformation" defaults to linear. ( there are bugs related to cost based query transformation causing huge performance degradation )

In the below example I try to show what predicate pushing is and what the impact at explain plan level is.

Assume we setup the sample schema


If we run the below SQL we notice that -- at least in our testcase --- Predicate pushing did happens automatically, however we can hint it with the PUSH_PRED hint




select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk from t_order_or group by su_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    gxxa8g94dzk7n, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.maxamount from t_supplier_su, (
select max(or_totalamount) maxamount,su_fk from t_order_or group by
su_fk ) inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_name is not null

Plan hash value: 3140464201

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

| Id  | Operation                | Name          | Rows    | Bytes | Cost (%CPU)| Time    |

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

|   0 | SELECT STATEMENT         |               |         |       |   690K(100)|          |

|   1 |  NESTED LOOPS OUTER      |               |     100 | 21800 |   690K  (1)| 02:18:01 |

|*  2 |   TABLE ACCESS FULL      | T_SUPPLIER_SU |     100 | 20300 |     3   (0)| 00:00:01 |

|   3 |   VIEW PUSHED PREDICATE  |               |       1 |    15 |  6900   (1)| 00:01:23 |

|   4 |    SORT GROUP BY         |               |       1 |     9 |  6900   (1)| 00:01:23 |

|*  5 |     TABLE ACCESS FULL    | T_ORDER_OR    |   10101 | 90909 |  6900   (1)| 00:01:23 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1        / T_SUPPLIER_SU@SEL$1
3 - SEL$639F1A6F / INNER_VIEW@SEL$1
4 - SEL$639F1A6F
5 - SEL$639F1A6F / T_ORDER_OR@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
5 - filter("SU_FK"="T_SUPPLIER_SU"."SU_PK")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400],
"INNER_VIEW"."MAXAMOUNT"[NUMBER,22]
2 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400]
3 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22]
4 - (#keys=1) "SU_FK"[NUMBER,22], MAX("OR_TOTALAMOUNT")[22]
5 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22]


71 rows selected.




If we run the below SQL we notice that we can avoid predicate pushing (at any cost) with the NO_PUSH_PRED hint.


select /*+ GATHER_PLAN_STATISTICS NO_PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;



SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    0gbc9khzuznzk, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su, ( select max(or_totalamount) maxamount,su_fk,cr_fk
currency from t_order_or group by su_fk,cr_fk ) inner_view where
t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is
not null

Plan hash value: 83112093

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

| Id  | Operation             | Name          | Rows  | Bytes | Cost  (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |               |       |       |  6954  (100)|          |

|*  1 |  HASH JOIN RIGHT OUTER|               |   281 | 66597 |  6954    (2)| 00:01:24 |

|   2 |   VIEW                |               |   281 |  9554 |  6950    (2)| 00:01:24 |

|   3 |    HASH GROUP BY      |               |   281 |  3653 |  6950    (2)| 00:01:24 |

|   4 |     TABLE ACCESS FULL | T_ORDER_OR    |  1000K|    12M|  6905    (1)| 00:01:23 |

|*  5 |   TABLE ACCESS FULL   | T_SUPPLIER_SU |   100 | 20300 |     3    (0)| 00:00:01 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$2 / INNER_VIEW@SEL$1
3 - SEL$2
4 - SEL$2 / T_ORDER_OR@SEL$2
5 - SEL$1 / T_SUPPLIER_SU@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_HASH(@"SEL$1" "INNER_VIEW"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$2" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK")
5 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."CURRENCY"[VARCHAR2,12],

"SU_COMMENT"[VARCHAR2,400], "T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400]
2 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."SU_FK"[NUMBER,22],
"INNER_VIEW"."CURRENCY"[VARCHAR2,12]
3 - "SU_FK"[NUMBER,22], "CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]
4 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
5 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22],
"T_SUPPLIER_SU"."SU_NAME"[VARCHAR2,400], "SU_COMMENT"[VARCHAR2,400]


71 rows selected.



Predicate pushing becomes more interesting when the inner view cannot be merged because there is an outer join. If the filtering predicate is selectif predicate pushing can then "push this filter down" into the inner view.
 



select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk= 1;


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    bw56zj2jw7vng, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PUSH_PRED(inner_view) */
su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su, ( select max(or_totalamount) maxamount,su_fk,cr_fk
currency from t_order_or group by su_fk,cr_fk ) inner_view where
t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk= 1

Plan hash value: 484793169

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

| Id  | Operation                      | Name          |   Rows  |   Bytes |   Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT               |               |         |         |    6902 (100)|          |

|   1 |  NESTED LOOPS OUTER            |               |       3 |     678 |    6902   (1)| 00:01:23 |

|   2 |   TABLE ACCESS BY INDEX ROWID  | T_SUPPLIER_SU |       1 |     203 |       1   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN           | SU_PK         |       1 |         |       0   (0)|          |

|   4 |   VIEW PUSHED PREDICATE        |               |       3 |      69 |    6901   (1)| 00:01:23 |

|   5 |    SORT GROUP BY               |               |       3 |      39 |    6901   (1)| 00:01:23 |

|*  6 |     FILTER                     |               |         |         |              |          |

|*  7 |      TABLE ACCESS FULL         | T_ORDER_OR    |   11636 |     147K|    6900   (1)| 00:01:23 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1        / T_SUPPLIER_SU@SEL$1
3 - SEL$1        / T_SUPPLIER_SU@SEL$1
4 - SEL$639F1A6F / INNER_VIEW@SEL$1
5 - SEL$639F1A6F
7 - SEL$639F1A6F / T_ORDER_OR@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" ("T_SUPPLIER_SU"."SU_PK"))
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T_SUPPLIER_SU"."SU_PK"=1)
6 - filter(1="T_SUPPLIER_SU"."SU_PK")
7 - filter("SU_FK"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400], "INNER_VIEW"."MAXAMOUNT"[NUMBER,22],
"INNER_VIEW"."CURRENCY"[VARCHAR2,12]
2 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400]
3 - "T_SUPPLIER_SU".ROWID[ROWID,10], "T_SUPPLIER_SU"."SU_PK"[NUMBER,22]
4 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."CURRENCY"[VARCHAR2,12]
5 - (#keys=2) "SU_FK"[NUMBER,22], "CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]
6 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
7 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]


77 rows selected.




Another example of interesting predicate pushing is the example below. For the supplier 100 there are no orders (read no records in the inner view).
We need however an entry in the result set for supplier 100, thus we need an outer join.
The filtering predicate results in the usage of the index OR_IDX1, we only need (selectif) records for 1 supplier. ( supplier 1 )



select /*+ GATHER_PLAN_STATISTICS NO_MERGE(inner_view) PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_pk in (1,100);


SU_PK
----------
SU_NAME
--------------------------------------------------------------------------------
SU_COMMENT
--------------------------------------------------------------------------------
CURRENCY      MAXAMOUNT
------------ ----------
1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
EUR           99985.21

1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
GBP        99967.1

1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
USD           99993.94

1
ZAQMGJLUHAZPEVKNGPXEWNYVKUPXMDENWSGCWAFRBRJMNABHOLVHESPKVFKTMQIESGRBVPFIHHRKOZCZ
FKEQVUXRYRVJQNVUYBOJ
QGPVSNRSRSJBQPKIKFVRXMKMDBCFIFTEGRVYXWJNMHSPGFSATRLVOYVTISBBSLXYJEAOMTWWNNGDSCMW
SOBWEOYOKXAQHHMWMSDS
YEN           99972.73

100
We love SQL tuning
We love SQL tuning




SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    997w5gw1rvgdu, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS NO_MERGE(inner_view)
PUSH_PRED(inner_view) */ su_pk,su_name,su_comment,inner_view.currency,in
ner_view.maxamount from t_supplier_su, ( select max(or_totalamount)
maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk )
inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_pk in (1,100)

Plan hash value: 331405424

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

| Id  | Operation                         | Name          | Rows    | Bytes | Cost (%CPU)|  Time    |

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

|   0 | SELECT STATEMENT                  |               |         |       |    299(100)|          |

|   1 |  NESTED LOOPS OUTER               |               |       2 |   452 |    299  (1)| 00:00:04 |

|   2 |   INLIST ITERATOR                 |               |         |       |            |          |

|   3 |    TABLE ACCESS BY INDEX ROWID    | T_SUPPLIER_SU |       2 |   406 |      3  (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN             | SU_PK         |       2 |       |      1  (0)| 00:00:01 |

|   5 |   VIEW PUSHED PREDICATE           |               |       1 |    23 |    148  (1)| 00:00:02 |

|   6 |    SORT GROUP BY                  |               |       6 |    78 |    148  (1)| 00:00:02 |

|*  7 |     FILTER                        |               |         |       |            |          |

|   8 |      TABLE ACCESS BY INDEX ROWID  | T_ORDER_OR    |     151 |  1963 |    147  (0)| 00:00:02 |

|*  9 |       INDEX RANGE SCAN            | OR_IDX1       |     151 |       |     22  (0)| 00:00:01 |

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


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
3 - SEL$1        / T_SUPPLIER_SU@SEL$1
4 - SEL$1        / T_SUPPLIER_SU@SEL$1
5 - SEL$639F1A6F / INNER_VIEW@SEL$1
6 - SEL$639F1A6F
8 - SEL$639F1A6F / T_ORDER_OR@SEL$2
9 - SEL$639F1A6F / T_ORDER_OR@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" ("T_SUPPLIER_SU"."SU_PK"))
NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")
LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")
USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")
INDEX_RS_ASC(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2" ("T_ORDER_OR"."SU_FK"))
USE_HASH_AGGREGATION(@"SEL$639F1A6F")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access(("T_SUPPLIER_SU"."SU_PK"=1 OR "T_SUPPLIER_SU"."SU_PK"=100))
7 - filter((1="T_SUPPLIER_SU"."SU_PK" OR 100="T_SUPPLIER_SU"."SU_PK"))
9 - access("SU_FK"="T_SUPPLIER_SU"."SU_PK")
filter(("SU_FK"=1 OR "SU_FK"=100))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400], "INNER_VIEW"."MAXAMOUNT"[NUMBER,22],
"INNER_VIEW"."CURRENCY"[VARCHAR2,12]
2 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400]
3 - "T_SUPPLIER_SU"."SU_PK"[NUMBER,22], "SU_NAME"[VARCHAR2,400],
"SU_COMMENT"[VARCHAR2,400]
4 - "T_SUPPLIER_SU".ROWID[ROWID,10], "T_SUPPLIER_SU"."SU_PK"[NUMBER,22]
5 - "INNER_VIEW"."MAXAMOUNT"[NUMBER,22], "INNER_VIEW"."CURRENCY"[VARCHAR2,12]
6 - (#keys=2) "SU_FK"[NUMBER,22], "CR_FK"[VARCHAR2,12], MAX("OR_TOTALAMOUNT")[22]

7 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
8 - "SU_FK"[NUMBER,22], "OR_TOTALAMOUNT"[NUMBER,22], "CR_FK"[VARCHAR2,12]
9 - "T_ORDER_OR".ROWID[ROWID,10], "SU_FK"[NUMBER,22]


85 rows selected.



Remember we can avoid that any query transformation ahppens with the NO_QUERY_TRANSFORMATION hint.


SQL> select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */ su_pk,su_name,su_comment,inner_view.currency,
2  inner_view.maxamount from t_supplier_su, ( select max(or_totalamount)
maxamount,su_fk,cr_fk currency from t_order_or group by su_fk,cr_fk )
inner_view where t_supplier_su.su_pk = inner_view.su_fk(+) and
t_supplier_su.su_pk in (1,100) ;