Is there a way to force Oracle to evaluate the Filter after the Join Access?

user_8081980 0
Viewed 365

I want to filter on the result of a join, using a cast. The problem is that part of the original field cannot be cast to an Integer. It would not be a problem if the filter was applied after the join. This is why I wonder if there's a way (perhaps an Optimizer Hint or something) to push the filter evaluation after the join operation.

This is a query that I built for the example. I would expect it to work, but fails with an 'ORA-01722 invalid number' :

WITH "literal" AS (
    SELECT 1 AS "literal_id", 'abc' AS "literal"
    FROM "DUAL"
    UNION
    SELECT 2 AS "literal_id", '7' AS "literal"
    FROM "DUAL"
),
     "scalar" AS (
         SELECT 3 AS "scalar_id", 2 AS "literal_id"
         FROM "DUAL"
         CONNECT BY ROWNUM <= 10000
     )
SELECT *
FROM "scalar"
         JOIN "literal" USING ("literal_id")
WHERE TO_NUMBER("literal") > 6;

The ORA-01722 is thrown because it's applied on the "literal" CTE, hence crashes because 'abc' is obviously not a number. We can see this in the execution plan :

Query execution plan

To reduce the possibilities around the cause of my problem, I executed that query:

CREATE TABLE "literal" AS (
    SELECT 1 AS "literal_id", 'abc' AS "literal"
    FROM "DUAL"
    UNION
    SELECT 2 AS "literal_id", '7' AS "literal"
    FROM "DUAL"
);
CREATE TABLE "scalar" AS (
    SELECT 3 AS "scalar_id", 2 AS "literal_id"
    FROM "DUAL"
    CONNECT BY ROWNUM <= 10000
);
CREATE TABLE "joined" AS (
    SELECT *
    FROM "scalar"
             JOIN "literal" USING ("literal_id")
);
SELECT *
FROM "joined"
WHERE TO_NUMBER("literal") > 6;

Which works perfectly fine.

So, is there a way to rewrite this query (I still need this to be a single query though) so it will not try to convert the 'abc' ?

For reference, I tried this on Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 as well as Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Thanks a lot.

oracle18c oracle11g oracle sql
2 Answers

The old school trick is to structure the query such that the predicate can't be pushed into the join. If you put the join into an inline view and add a rownum, that'll prevent the optimizer from evaluating the predicate until the join is complete

WITH "literal" AS (
    SELECT 1 AS "literal_id", 'abc' AS "literal"
    FROM "DUAL"
    UNION
    SELECT 2 AS "literal_id", '7' AS "literal"
    FROM "DUAL"
),
     "scalar" AS (
         SELECT 3 AS "scalar_id", 2 AS "literal_id"
         FROM "DUAL"
         CONNECT BY ROWNUM <= 10000
     )
select *
  from (
SELECT "scalar_id",  "literal_id", "literal", rownum
FROM "scalar"
         JOIN "literal" USING ("literal_id")
)
WHERE TO_NUMBER("literal") > 6;

If you're on 12.2 or later, you have the ability to take advantage of enhancements to the to_number function to return a NULL if there is a conversion error.

WITH "literal" AS (
    SELECT 1 AS "literal_id", 'abc' AS "literal"
    FROM "DUAL"
    UNION
    SELECT 2 AS "literal_id", '7' AS "literal"
    FROM "DUAL"
),
     "scalar" AS (
         SELECT 3 AS "scalar_id", 2 AS "literal_id"
         FROM "DUAL"
         CONNECT BY ROWNUM <= 10000
     )
SELECT "scalar_id",  "literal_id", "literal"
FROM "scalar"
         JOIN "literal" USING ("literal_id")
WHERE to_number("literal" default null on conversion error) > 6;
Justin_Cave_103970

For the where, use a conditional conversion. For example:

SELECT *
FROM "scalar" s JOIN
     "literal" l
      USING ("literal_id")
WHERE (CASE WHEN REGEXP_LIKE(l.literal, '[^[0-9]+$')
            THEN TO_NUMBER(l.literal)
       END) > 6;

As for your question, I don't think so. Oracle has a pretty sophisticated optimizer, so it will rearrange operations to optimize performance. You could use a CTE and a compiler hint to materialize the CTE, but that seems like overkill.

user_11440350
Related