How do you identify what where clause will be filtered first in Oracle and how to control it-CodePudding

I have a problem where the fix is to exchange what gets filtered first, but I'm not sure if that is even possible and not knowledgeable enough how it works.

To give an example:

Here is a table

When you filter this using the ff query:

select * from pcparts where Parts = 'Monitor' and id = 255322 and Brand = 'Asus'

by logic this will be correct as the Asus component with a character in its ID will be filtered and will prevent an ORA-01722 error.

But to my experience this is inconsistent.

I tried using the same filtering in two different DB connections, the first one didn't get the error (as expected) but other one got an ORA-01722 error.

Checking the explain plan the difference in the two DB's is the ff:

I was thinking if its possible to make sure that the Parts got filtered first before the ID but I'm unable to find anything when i was searching, is this even possible, if not, what is a fix for this issue without relying on using TO_CHAR

CodePudding user response：

"Fix" is rather simple: take control over what you're doing.

It is evident that ID column's datatype is VARCHAR2. Therefore, don't make Oracle guess, instruct it what to do.

No : select * from pcparts where Parts = 'Monitor' and id =  255322  and Brand = 'Asus'
Yes: select * from pcparts where Parts = 'Monitor' and id = '255322' and Brand = 'Asus'  
                                                            --------
                                 VARCHAR2 column's value enclosed into single quotes

CodePudding user response：

I assume you want to (sort of) fix a buggy program without changing the source code.

According to your image, you are using "Filter Predicates", this normally means Oracle isn't using index (though I don't know what displays execution plans this way).

If you have an index on PARTS, Oracle will probably use this index.

create index myindex on mytable (parts);

If Oracle thinks this index is inefficient, it may still use full table scan. You may try to 'fake' Oracle into thinking this an efficient index by lying about the number of distinct values (the more distinct values, the more efficient)

exec dbms_stats.set_index_stats(ownname => 'myname', indname => 'myindex', numdist => 100000000)

Note: This WILL impact performance of other querys using this table