Here is the second episode of Oracle in 3 Minutes. I titled it A Ticking Bomb, but you can also call it A Sleeper Bug.
Possibly related:
- A Complimentary Oracle OpenWorld Session
- Oracle in 3 Minutes: Multi-Versioning
- Oracle OpenWorld 2006 - Watch Tom Kyte (video)
- blogs.oracle.com: It Worked!
- Now I know
Tagged 3minoracle | Post a Comment | Trackback URI
Hope everything is working out...
Solution 3:
Reconsider your data model if you are really going to compare two columns which obviously have the same content and meaning but are of different data type…
December 10th, 2007, at 12:11 pm #Patrick
@Patrick: Sure, if you can change the data model, that will be a very good solution. But, sometimes reality hurts and you’re stuck with a model that you have no control over. I sometimes do find myself in such a situation, unfortunately, especially when I deal with FlexFields (varchar2 columns) in Oracle EBS.
December 10th, 2007, at 12:21 pm #Yeah, reality sometimes is so far way from the “ideal” world
December 10th, 2007, at 12:25 pm #When trying to filter out rows, be aware that there is no guarantee about what order Oracle executes the various conditions. So you might well find that the invalid number error returns down the road when the query plan changes.
Even if you nest the subquery, i.e.
SELECT t1.col1, t2.col2 FROM (SELECT t.col1 FROM table1 t WHERE filter_out_non_numbers) t1, table2 t2 WHERE t1.col1 = t2.col1there is no guarantee that the inner subquery is logically materialized before the join (and the implicit conversion) happens.
December 10th, 2007, at 6:59 pm #@Justin: Good points.
One more thing, if the to_char is used in the WHERE clause, that will break the use of indexes which may impact performance. So, a function based index may be a good option here.
December 10th, 2007, at 9:29 pm #[…] also has the second episode of his Oracle in 3 Minutes webcast, called A Ticking Bomb, or A Sleeper […]
December 14th, 2007, at 10:00 am #