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

Hi Mark,

I am stating that

"where <indexed_numeric_column1> + <constant1> = <constant2>"

can be efficiently reduced to

"where <indexed_numeric_column1> = <constant3>"

where <constant3> = <constant2> - <constant1>

The index on indexed_numeric_column1 could then explored by the CBO as an execution path candidate.

I am stating too that the algebraic reduction is incredibly simple maths.

The specific case in my initial example code demonstrated the point well and I remain surprised given the coverage of the CBO that this type of simple optimisation candidate is not explored by some component of the CBO (...or better put, does not appear to be explored by some component of the CBO).

In a more general form, and while I think about it, it would be pleasing if the CBO explored the other obvious possibilities too. For example

"where <indexed_numeric_column1> + <constant1> = <constant2>"

could be rewritten as

"where <virtual_calculated_indexed_column1> = <constant4>"

where <constant4> is whatever it needs to be to satisfy the calculation offset in the virtual/calculated and importantly indexed column.

In pseudo code

create table foo(x number, x4 as (x+4))
insert into foo select stuff from hugeTable create index idxFoo on foo(x4)

so

select * from foo where x+10=100

that would result in a full table scan could be rewritten as

select * from foo where x4=114

and idxFoo considered for use in a higher priority execution path.

The C in CBO is Cost, the B is Based, and the O is Optimiser. The CBO just doesn't seem to live up to its name for these trivial operations. I don't think it is too much to ask that they implement something a bit better than the status quo.

> On the other hand, you’re on point regarding why would you hand such drivel to the CBO!

A vastly more complex query than that shown in my original post comes through from an ERP. It's an Oracle house so I'll let you guess the vendor & product ;-)

Mike
http://www.strychnine.co.uk <http://www.strychnine.co.uk/>

> On 17 Jul 2016, at 05:04, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> I think he is suggesting that the canonical form
>
> where <indexed_numeric_column> + < expression1 > = < expression2>
>

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