Geek Answers Handbook

Where is the suggestion affecting the mix

Dumb question. Oracle 10g.

Is it possible for the where clause to affect the connection?

I have a request in the form:

select * from
(select product, product_name from products p
join product_serial ps on product.id = ps.id
join product_data pd on pd.product_value = to_number(p.product_value)) product_result
where product_name like '%prototype%';

Obviously, this is a contrived example. There is no real need to show the structure of the table, like all imaginable. Unfortunately, I cannot show the actual table structure or query. In this case, p.product_value is a VARCHAR2 field, which on some lines has an identifier stored inside it, and not text. (Yes, poor design - but something I inherited and cannot change)

The problem is the connection. If I omit the where clause, the query works and the rows are returned. However, if I add a where clause, I get an "invalid number" error in the connection condition pd.product_value = to_number (p.product_value).

, " " , , p.product_value. , , ? external where, where ? , , where , , , .

?

+5
4

: .

: , , . , .

, , , , , where, , , , .

​​ , to_number, , .

+1

.

( ) , , .

, , , "" ; , .

WHERE , ORACLE , ( , ..).

, "" , .


. , , , NULL, .

EXPLAIN PLAN, , .

+2

, to_number(p.product_value) product_name.

, join where, to_number.

, product_name like '%prototype%' join :

select * from
(select product, product_name from products p
join product_serial ps on product.id = ps.id
join product_data pd on product_name like '%prototype%' AND
     pd.product_value = to_number(p.product_value));
0

( ) Subquery Madness.

, Oracle . , ( ) product_name . . , Oracle , product_value to_number, . , , to_number, product_value, . , , N , N + 1, N + 1 to_number .

Besides fixing the data model, you could potentially throw some hints into the query to force Oracle to evaluate the predicate, which ensures that all non-numeric data is filtered before the predicate is applied to_number. But overall, it’s a little difficult to completely hint at a query in a way that will make the optimizer always evaluate things in the “right” order.

0
source

More articles:


All Articles