Sql-server – Select data with the latest table partition that actually holds data

partitioningsql-server-2016

Version: SQL Server 2016

So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. What I need is a way to select the latest partition within the table that actually contains data. Does anyone have any ideas.

There's a new partition created daily, but data doesn't always get inserted within the day. There's thousands of partitions that span many years so simply scrolling through each partition isn't ideal at all.

This is how I'm currently selecting the most recent partition (or should I say the max partition):

USE DBName
DECLARE @PartitionValue int
set @PartitionValue = (select max(boundary_id) from sys.partition_range_values)
SELECT * FROM schema.tablename
WHERE $PARTITION.partition_function_name(expression) = @PartitionValue

Best Answer

Try something like this (replace schema.tablename with your object name). It worked for me in my limited testing.

DECLARE @PartitionValue INT

SET @PartitionValue = (
        SELECT TOP 1 partition_number
        FROM sys.dm_db_partition_stats
        WHERE object_id = object_id('schema.table_name')
            AND row_count > 0
        ORDER BY partition_number DESC
        )

SELECT count(*)
FROM schema.table_name
WHERE $PARTITION.pf_schema_table_name_data(expression) = @PartitionValue

Related Solutions

Mysql – Does it makes sense to partition on a foreign key

FOREIGN KEYS are not supported in PARTITIONed tables. (With luck this might change in 5.8.)

If you don't have over a million rows, don't bother to PARTITION.

If you are not depending on FOREIGN KEY CONSTRAINTS, don't use them. INDEXes will suffice.

If you would like to discuss these further, please provide SHOW CREATE TABLE (even if not finalized) and the main SELECTs.

Based on what you said, I would guess that you need a compound index:

INDEX(OtherID, NegativeTimestamp)

Edit

I see a redundant index on MarketID. Also UNIQUE(ID) is redundant since the PRIMARY KEY is a UNIQUE key.

If the two Close columns have the same information, but one is negated, then the negative on can probably be removed. Then use DESC in the ORDER BY.

The use of @variables in the WHERE may be poorly optimized; let's see EXPLAIN SELECT ....

I would replace all of the indexes with just 2:

PRIMARY KEY (`MarketID`, Close_Datetime, ID),
INDEX(ID),  -- This is sufficient for AUTO_INCREMENT (except for protecting from deliberate dup)
-- nonexistent field?  KEY `OpenTimestamp` (`Open_Datetime`),
-- unneeded?   KEY `CloseTimestamp` (`Close_Datetime`),
-- unneeded?   KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`),
-- covered     KEY `MarketIDIndex` (`MarketID`),
-- replaced    KEY (`MarketID`,`Close_NegativeTimestamp`),

Also, I would shrink most of the fields to save space and improve cacheability. BIGINT is not really needed? MEDIUMINT would suffice for MarketID? decimal(30,15) takes 14 bytes, mostly zeros. DOUBLE (8 bytes) might work?

(I am assuming this is Stock Market data?)

If you choose to get rid of the FK and go with PARTITION, then I would need to see the other important SELECTs.

But, for that matter, do you need ID? Can't (MarketID, CloseDate) be the unique PRIMARY KEY? Note: It would be a 3-byte DATE, not a 5 or 8-byte DATETIME.

Postgresql – Dealing with out-of-range values on a PARTITION BY RANGE

Is there a way to get the min/max values I could store within the existing partitions?

And you also ask in a comment:

Do you know of a way to get the lower and upper bounds of the range by query?

I wouldn't know of any dedicated system catalog information function for this particular purpose. But:

Range partitioning is based on inheritance internally:

Individual partitions are linked to the partitioned table with inheritance behind-the-scenes;
Inheritance trees are stored in pg_inherits:

one entry for each direct child table
Partition bounds are stored in pg_class.relpartbound in internal format (pg_node_tree).
The system catalog information functions pg_get_expr(pg_node_tree, relation_oid) can:

decompile internal form of an expression

We can build a query from this set of clues. Based on the example for range partitioning in the manual:

SELECT i.inhrelid::regclass
     , partition_bound
     , split_part(partition_bound, '''', 2) AS lower_bound
     , split_part(partition_bound, '''', 4) AS upper_bound
FROM   pg_inherits i
JOIN   pg_class    c ON c.oid = i.inhrelid
     , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
WHERE  inhparent = 'measurement'::regclass;

inhrelid             | partition_bound                                  | lower_bound | upper_bound
:------------------- | :----------------------------------------------- | :---------- | :----------
measurement_y2006m02 | FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') | 2006-02-01  | 2006-03-01 
measurement_y2006m03 | FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') | 2006-03-01  | 2006-04-01

db<>fiddle here

Limitations:

Extracting lower and upper bound from the string based on single quotes is cheap & dirty. There is probably a cleaner way to extract the value from relpartbound directly.
Only including the first level of inheritance. You have to walk the graph in pg_inherits recursively to cover sub-partitioning.
This builds on several implementation details for declarative partitioning, which is a new feature of Postgres 10. While I do not expect this query to break due to changes in one of the next major versions, there is a chance it might.