|
|
Posted by Erland Sommarskog on 11/17/05 01:16
joshsackett (joshsackett@gmail.com) 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@sommarskog.se
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
Navigation:
[Reply to this message]
|