And generally, you then start to find all sorts of boundary cases you need
to take care of:
SQL> create table t (x int primary key, y int);
Table created.
SQL> insert into t values (1,null);
1 row created.
SQL>
SQL> select *
2 from t
3 where x+y = 1/0;
no rows selected
So this "works"....but hey...let's be smart and shift that "y" to the other
side, so we can use the index on "x"
SQL>
SQL> select *
2 from t
3 where x = 1/0 - y;
where x = 1/0 - y
*
ERROR at line 3:
ORA-01476: divisor is equal to zero
On Sun, Jul 17, 2016 at 12:04 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
I think he is suggesting that the canonical form
where <indexed_numeric_column> + < expression1 > = < expression2>
or at least
where <indexed_numeric_column> + <constant1> = <constant2>
would be automatically reduced to
where <indexed_numeric_column> = <expression2> - <expression1>
or
where <indexed_numeric_column> = <result constant2-constant1>
probably before the query is handed to the CBO for analysis.
Maybe that is a legitimate enhancement request. Don’t they already discard
+0 and ||NULL at least some of the time?
On the other hand, you’re on point regarding why would you hand such
drivel to the CBO!
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still
*Sent:* Saturday, July 16, 2016 3:55 PM
*To:* woodwardinformatics@xxxxxxxxxxxxxxxx
*Cc:* Oracle-L Freelists
*Subject:* Re: Grumble - is this really the best they can do?
On Wed, Jun 22, 2016 at 5:53 AM, Michael D O'Shea/Woodward Informatics Ltd
<woodwardinformatics@xxxxxxxxxxxxxxxx> wrote:
where largeTab_id+1 = 13536978456
Obviously this is what you are referring to.
What is not obvious is why you care.
Is this a canned app written in a sub-optimal manner?
Sub-optimally generated code?
Something else?
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
