locked
Select from Partitioned Table RRS feed

  • Question

  • If I run a select query against a partitioned table using where clause, does it search only in the related partition or in the entire table? For example, if I have a Student table with studentId from 1 to 10 and partitioned as 1 to 5 in first and 6 to 10 in second partition then if I run

    select * from student where studentId between 7 and 9

    then does it search in second partition only or in entire table?

    Wednesday, February 7, 2018 8:52 AM

Answers

  • ... for the actual execution plan, the scan or seek, you should see an "Actual Partition Count" information in the tool-tip.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Thursday, February 8, 2018 8:51 AM
    Wednesday, February 7, 2018 9:19 AM
  • Seems there is no suitable index on column "roll", so database engine performs a full scan over the complete table. Create a partitioned index.

    Note: On such a small table its likely to have full scan even an index exists, because the index/query is not selective enough.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Hannah Yu Thursday, February 8, 2018 6:43 AM
    • Marked as answer by Curendra Thursday, February 8, 2018 8:51 AM
    Wednesday, February 7, 2018 9:52 AM
    Answerer
  • Hi Curendra,

    >> Is it searching on all four partitions? I really could no figure out.

    >> Now is it searching in 2 partitions only?

    Just as Olaf mentioned, the engine needs to check every row since there’s no index on your partition key and after you created an index on that field it could check just the index.

    Besides, you can examine an actual execution plan to see partition elimination. For detailed steps, please refer to Did My Query Eliminate Table Partitions in SQL Server?.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Curendra Thursday, February 8, 2018 8:50 AM
    Thursday, February 8, 2018 7:05 AM
  • Actually, an index is not required for partition elimination. This example clearly show that:

    DROP TABLE IF EXISTS RegionScores
    
    IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'ps_regions')	DROP PARTITION SCHEME ps_regions
    
    IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pf_region_code')	DROP PARTITION FUNCTION pf_region_code
    GO
    
    CREATE PARTITION FUNCTION pf_region_code (int)  AS RANGE LEFT FOR VALUES (100, 200, 300)
    
    CREATE PARTITION SCHEME ps_regions AS PARTITION pf_region_code ALL TO ([PRIMARY])
    
    CREATE TABLE RegionScores(  region_code int , score_date date NOT NULL, score smallint NOT NULL, filler char(500) default 'hey')
    ON ps_regions (region_code)
    
    --One row each in first three partitions
    INSERT INTO RegionScores(region_code, score_date , score)
    VALUES 
     (20, '20160301',50),
     (120, '20160302',51),
     (220, '20160303',52)
    
    
    --500 000 rows in last partition
    INSERT INTO RegionScores(region_code, score_date , score)
    SELECT TOP(500000) 400 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20160301', 50 FROM sys.columns s1 CROSS JOIN sys.columns s2
    
    --33373 pages
    --    1 page each for partition 1, 2 and 3
    --33370 pages for partition 4
    
    
    SET STATISTICS IO ON
    
    --Only 1 page accesses for below
    SELECT * FROM RegionScores WHERE region_code BETWEEN 2 AND 50


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Friday, February 9, 2018 5:13 AM
    Thursday, February 8, 2018 11:01 AM

All replies

  • In optimum the database engine searches only in the second Partition; that's what the feature is good for. Check the execution plan, there you should see that only the second partition is touched.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 7, 2018 9:06 AM
    Answerer
  • ... for the actual execution plan, the scan or seek, you should see an "Actual Partition Count" information in the tool-tip.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Thursday, February 8, 2018 8:51 AM
    Wednesday, February 7, 2018 9:19 AM
  • Hi Olaf,

    I have demo table school which is partitioned into four partitions based on column roll. 

    I ran the following query

    SELECT * FROM dbo.school S WHERE S.roll BETWEEN 12 AND 15.

    roll 12 to 15 fall in 3 and 4 paritions.

    The execution plan is as follows.

    Is it searching on all four partitions? I really could no figure out.

    Wednesday, February 7, 2018 9:30 AM
  • Seems there is no suitable index on column "roll", so database engine performs a full scan over the complete table. Create a partitioned index.

    Note: On such a small table its likely to have full scan even an index exists, because the index/query is not selective enough.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Hannah Yu Thursday, February 8, 2018 6:43 AM
    • Marked as answer by Curendra Thursday, February 8, 2018 8:51 AM
    Wednesday, February 7, 2018 9:52 AM
    Answerer
  • Hi Olaf ,

    Refer the following image in previous context.

    Now is it searching in 2 partitions only?

    Wednesday, February 7, 2018 10:25 AM
  • I believe there was some change between 2008 and 2008 R2 (or whenever the change was) where the partition elimination could be pushed down to the storage engine. Meaning you wouldn't see it in the execution plan. You can conclude it from a trace by looking the amount of I/O. This is all from memory from many years ago, so I have to be vague here. But just as an FYI...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Hannah Yu Thursday, February 8, 2018 6:46 AM
    Wednesday, February 7, 2018 10:58 AM
  • Hi Curendra,

    >> Is it searching on all four partitions? I really could no figure out.

    >> Now is it searching in 2 partitions only?

    Just as Olaf mentioned, the engine needs to check every row since there’s no index on your partition key and after you created an index on that field it could check just the index.

    Besides, you can examine an actual execution plan to see partition elimination. For detailed steps, please refer to Did My Query Eliminate Table Partitions in SQL Server?.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Curendra Thursday, February 8, 2018 8:50 AM
    Thursday, February 8, 2018 7:05 AM
  • Actually, an index is not required for partition elimination. This example clearly show that:

    DROP TABLE IF EXISTS RegionScores
    
    IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'ps_regions')	DROP PARTITION SCHEME ps_regions
    
    IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pf_region_code')	DROP PARTITION FUNCTION pf_region_code
    GO
    
    CREATE PARTITION FUNCTION pf_region_code (int)  AS RANGE LEFT FOR VALUES (100, 200, 300)
    
    CREATE PARTITION SCHEME ps_regions AS PARTITION pf_region_code ALL TO ([PRIMARY])
    
    CREATE TABLE RegionScores(  region_code int , score_date date NOT NULL, score smallint NOT NULL, filler char(500) default 'hey')
    ON ps_regions (region_code)
    
    --One row each in first three partitions
    INSERT INTO RegionScores(region_code, score_date , score)
    VALUES 
     (20, '20160301',50),
     (120, '20160302',51),
     (220, '20160303',52)
    
    
    --500 000 rows in last partition
    INSERT INTO RegionScores(region_code, score_date , score)
    SELECT TOP(500000) 400 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20160301', 50 FROM sys.columns s1 CROSS JOIN sys.columns s2
    
    --33373 pages
    --    1 page each for partition 1, 2 and 3
    --33370 pages for partition 4
    
    
    SET STATISTICS IO ON
    
    --Only 1 page accesses for below
    SELECT * FROM RegionScores WHERE region_code BETWEEN 2 AND 50


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Curendra Friday, February 9, 2018 5:13 AM
    Thursday, February 8, 2018 11:01 AM