Re: Indexes being improperly used when selecting data through a view
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 16 Nov 2005 23:16:39 +0000 (UTC)
joshsackett (joshsackett@xxxxxxxxx) writes:
> I am having a problem with indexes on specific tables. For some reason
> a query that runs against a view is not selecting the correct index on
> a table. I run the same query against the table directly and it looks
> fine. Can anyone give me some insight? Thanks.
>
> PRODUCTION1:
> CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2
> CHAR(10))
> CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)
> CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)
You have:
> While in PRODUCTION1:
> SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
> --> Clustered index seek PRODUCTION1..IDX_MyTest2
> --> Results returned
But that is not possible. Either you would have an Index Seek +
Bookmark Lookup oon IDX_MyTest2 or a Clustered Index Scan on IDX_MyTest1.
> REPORTDB:
> CREATE VIEW MyTest1 AS
> SELECT ID, COLUMN1, COLUMN2 FROM PRODUCTION1..MyTest1
> UNION ALL
> SELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTest1
>...
> While in REPORTDB:
> SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
> --> Index seek PRODUCTION1..IDX_MyTest2
> --> Bookmark lookup PRODUCTION1..IDX_MyTest1
> --> Index seek ARCHIVE1..IDX_MyTest2
> --> Bookmark lookup ARCHIVE1..IDX_MyTest1
> --> Concatenate data and results returned
What happens if you actually have the UNION ALL, and not the UNION as
you correct later? With UNION SQL Server will have to do an operation
to sort out duplicates - that may affect the choice of plan.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Indexes being improperly used when selecting data through a view
- From: joshsackett
- Indexes being improperly used when selecting data through a view
- Prev by Date: Re: There is a question when i work on Linkedserver~Pls kindly help me:)
- Next by Date: Optimizing Stored Procedure with Datetime parameter
- Previous by thread: Re: Indexes being improperly used when selecting data through a view
- Next by thread: Merge Replication - Adding a new table
- Index(es):
Relevant Pages
|
