Re: Functions and Relations
Date: Tue, 21 Nov 2006 22:05:16 +0100 (CET)
Message-ID: <Xns9882A3C047B0Easdgba_at_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,[_at_1],0)), WHERE:([test].[dbo].[T].[y]=([test].[dbo].[T].[x]-[_at_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 - 22:05:16 CET