Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed

On Wed, 12 Nov 2008, Vladimir Sitnikov wrote:
> And rewrite query as follows:
> select greatest(
>    (select max(date) from bilkaib where datecol<=date'2008-11-01' and cr='00'),
>    (select max(date) from bilkaib where datecol<=date'2008-11-01' and db='00'))

Oops, yes, I missed the "OR" in the query. This rewrite is good - my
suggested index would not have helped.

> I suggest you to create two more indices:
>
> create index date_with_zero_cr on bilkaib(date) where cr='00';
> create index date_with_zero_db on bilkaib(date) where db='00';

Alternatively if you create an index on (cr, bilkaib) and one on (db,
bilkaib) then you will be able to use other values in the query too.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                 -- Kyle Hearn