RID Lookup with HEAP

RID Lookup is a physical Query Execution operator which operates on HEAP Table i.e A table without Clustered Index on it. It comes into picture when non clustered index does not contain all the data requested by a query, so to get the addition data it goes to heap (only in case when you don’t have clustered index on your table) .  RID Lookup always accompanied by Nested Loop Join operator.

Lets stimulate the RID lookup operator in Execution plan.

Here we are creating a table with col2 as datatime datatype which we will use it in where predicate.

CREATE TABLE RIDLookup_Demo_TB

(

    id   INT  IDENTITY (1, 1),

    col2 DATETIME        ,

    col3 NUMERIC (28, 10) NOT NULL

);

Command(s) completed successfully.

Our table is ready to get some data into it. Here we are trying to insert 5 Lkh data where date column i.e. Col2 has value 16June2014 and 5 record with col2 value 16June2014 values.

DECLARE @i AS INT = 0;

BEGIN TRANSACTION;

WHILE @i < 500000   

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140617',

               1000 * rand();

        SET @i = @i + 1;

    END

COMMIT TRANSACTION;

Now since our data are inserted into table, lets query the table to see the plan

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

Here we saw, since the table has no clustered index (Primary Key), Table Scan aka Heap Scan comes into picture, but we are concern with RID Lookup. Which is associated with Non Clustered index on table which has no Clustered index define.

it’s time to create Non Clustered Index on col2 of table.

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Now since NonClustered Index is in place, lets fire the same query to see the RID Lookup in the Execution Plan.

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

Work around solution

When I see RID Lookup, I always think, why the table has no clustered index on it. So the solution is create Clustered Index on the table.

If you have clustered index on table, the RID lookup will changed to Key Lookup, again when I saw Key Lookup in execution, I think why the non clustered index not covered by required column asked in select clause.

Blend my love with code, and you will find me happy! J

 

Post Reference: Vikram Aristocratic Elfin Share