My table have 7 million records and I do split table in 14 part according to ID, each partition include 5 million record and size of partition is 40G. I want to run a query to get count in one partition but it scan all partitions and time of Query become very large.
我的表有700万条记录,我根据ID分成14个部分,每个分区包含500万条记录,分区大小为40G。我想运行一个查询来获取一个分区的计数,但它扫描所有分区,查询的时间变得非常大。
SELECT COUNT(*)
FROM Item
WHERE IsComplated = 0
AND ID Between 1 AND 5000000
How can I run my query on one partition only without scan other partition?
如何在不扫描其他分区的情况下在一个分区上运行查询?
1 个解决方案
#1
3
Refer http://msdn.microsoft.com/en-us/library/ms188071.aspx
B. Getting the number of rows in each nonempty partition of a partitioned table or index The following example returns the number of rows in each partition of table TransactionHistory that contains data. The TransactionHistory table uses partition function TransactionRangePF1 and is partitioned on the TransactionDate column. To execute this example, you must first run the PartitionAW.sql script against the AdventureWorks2012 sample database. For more information, see PartitioningScript.
B.获取分区表或索引的每个非空分区中的行数以下示例返回包含数据的表TransactionHistory的每个分区中的行数。 TransactionHistory表使用分区函数TransactionRangePF1,并在TransactionDate列上进行分区。要执行此示例,必须首先针对AdventureWorks2012示例数据库运行PartitionAW.sql脚本。有关更多信息,请参阅PartitioningScript。
USE AdventureWorks2012;
GO
SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition,
COUNT(*) AS [COUNT] FROM Production.TransactionHistory
GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)
ORDER BY Partition ;
GO
C. Returning all rows from one partition of a partitioned table or index The following example returns all rows that are in partition 5 of the table TransactionHistory. Note Note To execute this example, you must first run the PartitionAW.sql script against the AdventureWorks2012 sample database. For more information, see PartitioningScript.
C.从分区表或索引的一个分区返回所有行以下示例返回表TransactionHistory的分区5中的所有行。注意注意要执行此示例,必须首先针对AdventureWorks2012示例数据库运行PartitionAW.sql脚本。有关更多信息,请参阅PartitioningScript。
SELECT * FROM Production.TransactionHistory
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;
#1
3
Refer http://msdn.microsoft.com/en-us/library/ms188071.aspx
B. Getting the number of rows in each nonempty partition of a partitioned table or index The following example returns the number of rows in each partition of table TransactionHistory that contains data. The TransactionHistory table uses partition function TransactionRangePF1 and is partitioned on the TransactionDate column. To execute this example, you must first run the PartitionAW.sql script against the AdventureWorks2012 sample database. For more information, see PartitioningScript.
B.获取分区表或索引的每个非空分区中的行数以下示例返回包含数据的表TransactionHistory的每个分区中的行数。 TransactionHistory表使用分区函数TransactionRangePF1,并在TransactionDate列上进行分区。要执行此示例,必须首先针对AdventureWorks2012示例数据库运行PartitionAW.sql脚本。有关更多信息,请参阅PartitioningScript。
USE AdventureWorks2012;
GO
SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition,
COUNT(*) AS [COUNT] FROM Production.TransactionHistory
GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)
ORDER BY Partition ;
GO
C. Returning all rows from one partition of a partitioned table or index The following example returns all rows that are in partition 5 of the table TransactionHistory. Note Note To execute this example, you must first run the PartitionAW.sql script against the AdventureWorks2012 sample database. For more information, see PartitioningScript.
C.从分区表或索引的一个分区返回所有行以下示例返回表TransactionHistory的分区5中的所有行。注意注意要执行此示例,必须首先针对AdventureWorks2012示例数据库运行PartitionAW.sql脚本。有关更多信息,请参阅PartitioningScript。
SELECT * FROM Production.TransactionHistory
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;