Oracle 12c Inline View Evaluation

Question

Once upon a time in a database far away, the developer wrote a query in which he / she depended on the order in which the predicates were written.

For instance,

select x
  from a, b
 where NOT REGEXP_LIKE (a.column, '[^[:digit:]]')
   and a.char_column = b.numeric_column; 

(To clarify the plan assumes that the transformation to_numberwill be applied to a.char_column)

I think that chance is more than design, it “just works” (Oracle 11g). However, the order of the predicates is not respected when working in Oracle 12c, so this request is interrupted with an invalid number of exceptions. I know that I could try to get 12c to evaluate predicates in order using a hint ORDERED_PREDICATESas follows

select /*+ ORDERED_PREDICATES +*/ x
  from a, b
 where NOT REGEXP_LIKE (a.column, '[^[:digit:]]')
   and a.char_column = b.numeric_column

.. , to_char . , to_char . , , , . , ?

select x
  from b
  inner join (
              select only_rows_with_numeric_values as numeric_column
                from a 
               where NOT REGEXP_LIKE (a.column, '[^[:digit:]]')
             )  c
     on c.numeric_column = b.numeric_column;

Answer 1

- https://jonathanlewis.wordpress.com/2015/06/02/predicate-order-2/

, rownum doc (https://docs.oracle.com/database/121/SQLRF/queries008.htm#SQLRF52358)

select x
  from b
  inner join (
              select only_rows_with_numeric_values as numeric_column,
                rownum
                from a 
               where NOT REGEXP_LIKE (a.column, '[^[:digit:]]')
             )  c
     on c.numeric_column = b.numeric_column;

/*+ no_unnest*/