Re: [Firebird-test] FB 2.0 Alpha 3 - Doubts in optmizer improvements

[Alexandre B. S. <ib...@th...>]

Steve Landrum wrote:

>
> STARTING WITH and LIKE queries are not treated the same in the optimizer.
>
> Try this query it would be interesting to see what the plan looks like.
>
> Select
>  *
> from
>  TituloReceber TR join
>  TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) 
> join
>  Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
> TR.EmpresaCredorDevedorID) join
>  MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
> where
>  ECD.Nome STARTING 'Alpha Corantes'
>
>
Hi Steve !

AFAIK the operator LIKE without a wildcard on the first letter has an 
special treatement (Please correct me if I am wrong here), my query with 
LIKE will internally look something like:

Select
 *
from
 TituloReceber TR join
 TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) join
 Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
TR.EmpresaCredorDevedorID) join
 MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
where
 ECD.Nome STARTING 'Alpha Corantes' and
 ECD.Nome like 'Alpha Corantes%'

thats the reason the above query can use an index.

But to answer to your question. Then plan with STARTING WITH is exactly 
the one using LIKE
PLAN JOIN (TRB NATURAL, TR INDEX (PK_TITULORECEBER), ME INDEX 
(PK_MULTIEMPRESA), ECD INDEX (PK_EMPRESA_CREDORDEVEDOR))

see you !

-- 

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.9.2/55 - Release Date: 21/07/2005