Re: Functions and Relations

From: NENASHI, Tegiri <tnmail42_at_gmail.com>
Date: Tue, 21 Nov 2006 22:05:16 +0100 (CET)
Message-ID: <Xns9882A3C047B0Easdgba@194.177.96.26>

"Aloha Kakuikanu" <aloha.kakuikanu_at_yahoo.com> wrote in news:1164136641.739367.248440_at_j44g2000cwa.googlegroups.com:

> Aloha Kakuikanu wrote:

>> Now returning to the main discussion. The query in question:
>>
>> select x,y from T
>> where x=y and y=f(x)
>>
>> ....

>
> More realistic example:
>
> create table T (
> x integer,
> y integer,
> );
>
> create index Ti on T(x,y);
>
> insert into t
> select i/1000, mod(i,1000)
> from Integers where i < 100000
>
> select * from T
> where x = 1 and y = x+1
>
> Is your SQL database of choice able to find the access path where `x=1`
> is evaluated first, then it is joined with `y = x+1` and finally a
> tuple from T is fetched by index range scan?

MS SQL Server is able to find the access path where x=1 is evaluated first. It seeks to the index location and then utilizes the function condition and reads the values from the index. I changed a little your SQL to read one row but it is the same exection plan:

select * from T
where x = 1 and y = x-1 -- '+' -> '-'

Plan:

|--Index Seek(OBJECT:([test].[dbo].[T].[Ti]), SEEK:([test].[dbo].[T].[x]=CONVERT_IMPLICIT(int,[@1],0)), WHERE:([test].[dbo].[T].[y]=([test].[dbo].[T].[x]-[@2])) ORDERED FORWARD)   

<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831">

<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation>

>
>
Received on Tue Nov 21 2006 - 15:05:16 CST