JPPD : Join Predictive Pushdown

CREATE OR REPLACE TYPE SALES_CUST_TYPEAS OBJECT
(prod_cnt NUMBER(5),
 channel_cnt NUMBER(2),
 tot_amt NUMBER(15,2));
/

SELECT /*+ GATHER_PLAN_STATISTICS */
       s.cust_id, s.cust_first_name, s.cust_last_name,
       s.sales_cust.prod_cnt,  -- the Alias was used here
       s.sales_cust.channel_cnt,
       s.sales_cust.tot_amt
  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */
               c.cust_id, c.cust_first_name, c.cust_last_name,
               (SELECT sales_cust_type -- the type name should be used as it is
                          (COUNT (DISTINCT s.prod_id),
                           COUNT (DISTINCT s.channel_id),
                           SUM (s.amount_sold)
                          )
                  FROM sales s
                 WHERE s.cust_id = c.cust_id
) AS sales_cust -- this Alias is being used on the main query block.
          FROM customers c
         WHERE c.cust_year_of_birth= 1987
           AND ROWNUM   ;

The SQL above shows the similar effect when JPPD is being used. So it is same as a lateral view has been created.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));