Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table.

When I run the following:

SELECT  *
FROM    dbo.INVENTORY_BREAK
WHERE   IR_ID IN ( 100, 101, 102 )

I get the following execution plan:

enter image description here

It states Table Scan (HEAP). Not quite sure what it means in the context of a partitioned table. I also don't see that it uses any kind of index. And yet, it must, because the query comes back fairly fast (e.g. the table has 6 million rows).

So, my questions are:

  1. What does Table Scan (HEAP) mean for a partitioned table?
  2. Does it indeed use an index, perhaps behind the scenes?
  3. Is there anything I need to do to improve efficiency?
share|improve this question
    
As IR_ID is the partitioning column it can just look at the three partitions of interest. Do any of the partitions contain more than one IR_ID value? –  Martin Smith Jul 10 '12 at 22:17
    
@MartinSmith No, IR_ID is unique in partition. –  AngryHacker Jul 10 '12 at 22:29
    
No point in having the NCI on IR_ID then AFAIK. It can already tell which partitions contain a particular value from the partitioning metadata and within each partition all rows in the index will have the same value. –  Martin Smith Jul 10 '12 at 22:32
    
Blogged on this topic: sqlblog.com/blogs/paul_white/archive/2011/02/17/… –  Paul White Sep 12 '12 at 5:40

1 Answer 1

up vote 2 down vote accepted

The query did do partition elimination.

If you look at the tooltip, the Actual Partition Count says 3, which means it touched 3 partitions. (The Seek Predicate section also confirm this.)

The reason why it appears as a table scan is because it's a table scan with partition elimination. I'm not sure how else to explain that.

No indexes were used for this. Table partitioning is implemented deeper down in the database internal structures than an index, so partition elimination is very efficient by nature (given a suitable predicate, which you provided).

That said, the predicate you've given is suitable only for partition elimination, and (very likely) not much else because it's not very selective. If all the rows from those partitions are required, this is already close to an optimal solution -- creating a useful clustered index is usually recommended.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.