execution plan

Question

Post reply

execution plan

niranjankumar_k

Ten Centuries

Points: 1075
More actions
May 24, 2008 at 7:42 am

#182771

Created non clustered index on this column (key) of 2 table a1, a2.. made a join with index column only .. and select non index column date in select list .. but execution plan shows table scan ....
same query i choose index column in select list only no other changes in join .. however it shows index scan .. how it is possible
---->>>>>TABLE SCAN
SELECT STG2.DT , STG1.DT
FROM A1 STG1 (NOLOCK)
JOIN A2 STG2 (NOLOCK)
ON STG1.KEY = STG2.KEY
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([STG1].)=([STG2].), RESIDUAL:([STG2].=[STG1].))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG1].))
| |--Table Scan(OBJECT:([QIW].[dbo].[A1] AS [STG1]))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG2].))
|--Table Scan(OBJECT:([QIW].[dbo].[A2] AS [STG2]))
---->>>>> INDEX SCAN
SELECT STG1.KEY , STG2.KEY
FROM A1 STG1 (NOLOCK)
JOIN A2 STG2 (NOLOCK)
ON STG1.KEY = STG2.KEY
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([STG2].)=([STG1].), RESIDUAL:([STG2].=[STG1].))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG2].))
| |--Index Scan(OBJECT:([QIW].[dbo].[A2].[NC_INDEX1_A2] AS [STG2]))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([STG1].))
|--Index Scan(OBJECT:([QIW].[dbo].[A1].[NC_Index1_A1] AS [STG1]))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Please provide DDL and sample data.

For details, please read http://www.aspfaq.com/etiquette.asp?id=5006

SQL = Scarcely Qualifies as a Language

niranjankumar_k Ten Centuries Points: 1075 More actions · Answer 2

this both table has 1020 columns ...

there is no relation ship .. constraint .. only this 2 column is having non cluster index .. nothing else ..

and 4.5 lak data having ..

if i include the index column in select i can see index scan .. but this is not possible .. in another place i have choosen 350 columns ...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table and index definitions please.

How many rows do the two tables have? How many does this query return?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass