Re: Grumble - is this really the best they can do?

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jared Still
> *Sent:* Saturday, July 16, 2016 3:55 PM
> *To:* woodwardinformatics_at_strychnine.co.uk
> *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_at_strychnine.co.uk> 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
>

-- 
Connor McDonald
===========================
blog:   connormcdonald.wordpress.com
twitter: _at_connor_mc_d

"If you are not living on the edge, you are taking up too much room."
- Jayne Howard

*Fine print: Views expressed here are my own and not necessarily that of my
employer*

--
http://www.freelists.org/webpage/oracle-l