How do you prevent Oracle Cost Based Optimization?

Question

Consider the following scenario. I have table (a stupid_table) in a schema that I have no control over. This is a third party, beyond. No offense. I can query it, but not add indexes or new tables or change the design.

Each column in stupid_tableis VARCHAR2(50 BYTE), there are many columns, but I need only two of them: row_typeand magic_number. magic_numberpopulated with a string representation of an integer, but only where row_typethe value is set 'DATA', I only need magic numbers that are greater than zero.

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND TO_NUMBER(magic_number) > 0;

This leads to an Oracle error with an invalid number, because the Cost Optimizer (CBO) selects the estimate TO_NUMBERbefore checking row_typeand there is a whole group of rows with a different one row_typeand a different use for the field magic_number.

OK, how about filtering the rows first and then doing the comparison?

SELECT TO_NUMBER(t.magic_number)
FROM (
    SELECT magic_number
    FROM stupid_table
    WHERE row_type = 'DATA'
) t
AND TO_NUMBER(t.magic_number) > 0;

Now CBO seems to find out that the request is quite simple and ignores the trick I used, giving an identical query plan to the original.

Finally, in desperation, I resort to dirty hacks: using the /*+RULE*/query hint to force Oracle to use the old rule-based optimizer. It works like a dream, but it’s not necessary, not to mention using the Oracle function, which is no longer supported.

Is there a better way to do this?

Answer 1

TO_NUMBER? , . - :

WHERE t.magic_number != '0'

, , , , .

Answer 2

, , , ..

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN number
IS 
BEGIN
  RETURN to_number( p_str );
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND MY_TO_NUMBER(magic_number) > 0;

, , RBO, , CBO . , , , CBO - MAGIC_NUMBER ROW_TYPE.

Answer 3

CASE

select to_number(magic_number) 
from stupid_table
where row_type = 'DATA'
and case when row_type = 'DATA' then to_number(magic_number) else 0 end > 0

, , , DATA, . , .

, no_merge , , .

SELECT --+ no_merge(t)
  TO_NUMBER(t.magic_number)
FROM (
    SELECT magic_number
    FROM mike_temp_stupid_table
    WHERE row_type = 'DATA'
) t
where TO_NUMBER(t.magic_number) > 0;

Answer 4

- ordered_predicates, WHERE.

: Oracle ORDERED_PREDICATES

SELECT /*+ ORDERED_PREDICATES */ TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND TO_NUMBER(magic_number) > 0;

, . , , , TO_NUMBER - .

Answer 5

stupid_table, row_type 'DATA'?

Answer 6

rownum, . ( , , , , .) , - "" , .

SELECT TO_NUMBER(t.magic_number)
FROM (
    --Bad data, use rownum for type safety
    SELECT magic_number, rownum
    FROM stupid_table
    WHERE row_type = 'DATA'
) t
AND TO_NUMBER(t.magic_number) > 0;

Answer 7

with .

WITH
has_numerics_only AS
(
    SELECT magic_number
    FROM stupid_table
    WHERE row_type = 'DATA'
)
SELECT TO_NUMBER(t.magic_number)
FROM has_numerics_only
WHERE TO_NUMBER(t.magic_number) > 0;

"DATA".

Answer 8

:

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND REGEXP_LIKE(magic_number, '^\d{1,}$');

, HAVING .

SELECT TO_NUMBER(magic_number)
FROM (
SELECT magic_number
FROM stupid_table
WHERE row_type = 'DATA'
GROUP BY magic_number
HAVING REGEXP_LIKE(magic_number, '^\d{1,}$')) ilv;

Otherwise, materializing the view or using the PL / SQL cursor may be the only way.