"itamar" <itamar@xor.co.il> wrote in message
news:16f8ed5a.0311180727.5c796968@posting.google.com...
> select * from table2
> where Date2 = '10-12-2003 12:00:00'
>
> Using the query analyzer "Display Estimated Execution Plan" shows a
> table scan will occur (and running the query takes a long time).
>
> The problem is - there is only 1 index on the table, a primary index
> on Date2 field.
>
> To make things more interesting, the following 'identical' query runs
> in a second using the primary index (as indicated in its "Estimated
> Execution Plan").
>
> select * from table2
> where Date2 = '10-11-2003 12:00:00'
>
>
> The table:
> Create table table2 (
> date2 smalldatetime null,
> value1 char(8) collate SQL_Latin1_General_CP1_CI_AS null,
> value2 int null,
> value3 bigint null,
> value4 float null
> ) on primary
>
> The index:
> Create index ix_table2_date2 on dbo.table2(date2) with fillfactor=90
> on primary
>
> Product Version 9.00.384 (SP1)
> Server Collation: SQL_Latin1_General_CP1_CI_AS

"Niall Litchfield" <n-litchfield@audit-commission.gov.uk> wrote in message
news:3fbb7c91$0$247$ed9e5944@reading.news.pipex.net...
> It isn't identical, the date is different. Take a look at the distribution
> of your data.

>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> "itamar" <itamar@xor.co.il> wrote in message
> news:16f8ed5a.0311180727.5c796968@posting.google.com...
> > select * from table2
> > where Date2 = '10-12-2003 12:00:00'
> >
> > Using the query analyzer "Display Estimated Execution Plan" shows a
> > table scan will occur (and running the query takes a long time).
> >
> > The problem is - there is only 1 index on the table, a primary index
> > on Date2 field.
> >
> > To make things more interesting, the following 'identical' query runs
> > in a second using the primary index (as indicated in its "Estimated
> > Execution Plan").
> >
> > select * from table2
> > where Date2 = '10-11-2003 12:00:00'
> >
> >
> > The table:
> > Create table table2 (
> > date2 smalldatetime null,
> > value1 char(8) collate SQL_Latin1_General_CP1_CI_AS null,
> > value2 int null,
> > value3 bigint null,
> > value4 float null
> > ) on primary
> >
> > The index:
> > Create index ix_table2_date2 on dbo.table2(date2) with fillfactor=90
> > on primary
> >
> > Product Version 9.00.384 (SP1)
> > Server Collation: SQL_Latin1_General_CP1_CI_AS
>
>