full table scan on partitioned table

Question

Post reply

full table scan on partitioned table

shen-dest

SSCrazy

Points: 2498
More actions
April 21, 2011 at 8:57 am

#238114

hi
we have a 120G table and partitioned table into 30 partitions on based on a char(20) char field. and have created a non-clustered index on partition key column.
a query like select * from table_name where column='20110101' scans all table. what could by the reason.
Regards,
MShenel

Viewing 10 posts - 1 through 9 (of 9 total)

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

dajonx SSCertifiable Points: 6950 More actions · Answer 1

Do you have an index that will satisfy the query? Do you need to select all of the columns?

My Blog!

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 2

shen-dest (4/21/2011)
a query like select * from table_name where column='20110101' scans all table.

In the Query Exec plan, is there a difference between the estimated row count and the actual (when looking at an actual plan?)

When was the last time the statistics were updated? Try UPDATE STATISTICS table_name WITH FULLSCAN and then try your query again.

Are you sure you are not using a function on the left of the equals sign? ... where UPPER(column)='abc'?

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Is "column" the only dimension of the partitioning function? Could you post full DDL for the table along with all indexes, partitioning function and scheme?

Also the actual execution plan for the query.

Removing the partitioning element for the moment, a non-clustered index seek + key lookup is surprisingly expensive and likely won't be used if it isn't very selective.

LutzM SSC Guru Points: 107049 More actions · Answer 4

The description is kind of vague...

Is the column used to partition the table identical to the column used in your WHERE clause?

Based on what I see I'd guess the partitions are based on a date range but the date is stored as character. If so, I strongly recommend to use DATE or DATETIME instead of CHAR().

But, if the column used in the WHERE clause is not the one used to partition the table then you'll end up with a table scan of all partitions since it is unknown in what partition the data exist meeting your criteria.

So, please provide more details: column name and logic to define the range of a partition as well as column name used in the WHERE clause.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

select * from table_name where column='20110101'

mention columns which you have required in the selection and mention index scripts also

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

shen-dest SSCrazy Points: 2498 More actions · Answer 6

yes we have an index on that column, and selecting few columns also does full scan.

Regards,
MShenel

shen-dest SSCrazy Points: 2498 More actions · Answer 7

DBCC SHOW_STATISTICS indicates that statistics are up to date (showing few hours ago), and number of rows returning from query matches rows shown in execution plan. a week ago i started updating statistics with sample 100 percent clause but it took so long that i had to cancel after 6 hours.

Regards,
MShenel

shen-dest SSCrazy Points: 2498 More actions · Answer 8

here is the table definition , partition funtion and partition schema used to partition the table.

CREATE PARTITION FUNCTION [part_fn](char(20)) AS RANGE

RIGHT FOR VALUES (N'20110325 ', N'20110326 ', ...,N'20110502 ')

CREATE PARTITION SCHEME [part_schema] AS PARTITION [part_fn] TO ([SECONDARY],

[SECONDARY], [SECONDARY], [SECONDARY], [SECONDARY], [SECONDARY], [SECONDARY],...)

GO

CREATE TABLE [dbo].[table_name](

[swwState] [char](2) NOT NULL,

[swwdate] [char](20) NOT NULL,

[swwhour] [char](10) NOT NULL,

[swwbday] [char](25) NOT NULL,

...

[swwOrgRRN] [char](12) NULL,

[swwtotal] [money] NULL

)

on part_schema(swwdate)

GO

table has 98 columns

idx_01nonclustered located on part_schemaswwNo

idx_02nonclustered located on part_schemaswwRetrRequest, swwTrcAudit, swworderno

idx_03nonclustered located on part_schemaswwdate

idx_04nonclustered located on part_schemaswwTermsource, swwTermId, swwhour

idx_05nonclustered located on part_schemaswwdate, swwTermId, swwhour

idx_06nonclustered located on part_schemaswwbday, swwDomain

idx_07nonclustered located on part_schemaswwinsertdate

Regards,
MShenel

LutzM SSC Guru Points: 107049 More actions · Answer 9

As I mentioned earlier it looks like you try to partition by date.

So, let me ask again: why do you have a CHAR(20) column to store an 8 charachter date format???

Why don't you use the DATE data type?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]