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, Dec 12 '16 at 11:54
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, Dec 12 '16 at 11:57
Now, that is an idea! I have to admit I didnt consider a virtual column! Thanks for the suggestion! — 0909EM, Dec 12 '16 at 12:59
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, Dec 12 '16 at 13:58
@krokodilko - They are the same! I have edited to reflect that. — 0909EM, Dec 12 '16 at 14:15

Evgeniy K. · Accepted Answer · 2016-12-12T10:45:44.210

1

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*/

edited Dec 12 '16 at 10:45

answered Dec 12 '16 at 10:02

Evgeniy K.

1,137
7
11

Thanks for the link to Jonathan Lewis! In my googling I did see rownum mentioned but couldn't see it in the Oracle docs, thanks for the link. – 0909EM Dec 12 '16 at 13:03

Oracle 12c Inline View Evaluation

1 Answers1