Oracle 12c Inline View Evaluation

Question

A long time ago in a database far, far away a developer wrote a query in which he/she was reliant on the order in which predicates were written.

For example,

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

(explain plan suggests a to_number conversion will be applied to a.char_column)

I think by chance more than design this "just works" (Oracle 11g). However, the order of the predicates isn't adhered to when running in Oracle 12c, so this query breaks with an invalid number exception. I'm aware that I could try to force 12c to evaluate the predicates in order by using the ORDERED_PREDICATES hint as follows

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

.. Or cast one of the values using to_char for the comparison. The downside is that to_char could operate on say a million rows. I think the following inline view is probably a better solution. Am I guaranteed that the inline view will be evaluated first?

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;

You say there could be millions of rows on the b table (The downside is that to_char could operate on say a million rows); how many rows are in the a table, since that will be having the to_number function applied to it because of the implicit conversion taking place? If there is a similar (or higher) number of rows in the a table, then you're probably going to see no negative impact on the performance because of the to_char. — Boneist
Alternatively, you could create a virtual column on table a that only contains the numeric values (and maybe index that column) and then your query can be simplified to select x from a inner join b on a.new_numeric_virtual_column = b.numeric_column; — Boneist
Now, that is an idea! I have to admit I didnt consider a virtual column! Thanks for the suggestion! — 0909EM
In the first query you are using and a.char_column = b.numeric_column; while in the second query: and a.column = b.numeric_column. Are a.column and a.char_column same ? Or different ? — krokodilko
@krokodilko - They are the same! I have edited to reflect that. — 0909EM

Evgeniy K. Evgeniy K. · Accepted Answer · 2016-12-12T10:02:09

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

You should rewrite your last query to next using rownum according to 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;

to suppress query unnesting or simply using hint /*+ no_unnest*/

Oracle 12c Inline View Evaluation

1 Answers