New answers tagged

1

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination. I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This ...


2

The eager spool has to do with the size of data you're deleting. In this case, you're updating a large quantity of rows on a table with several indexes. You're also updating a field that's covered by a lot of nonclustered indexes, and SQL Server has to update all of those indexes as well. To avoid this operation, update less rows (as a percentage of the ...


3

The use case of "purge after 30 days" is an excellent use of PARTITIONing. (It is one of only 4 use cases that I know of.) PRIMARY KEY(id, date) ... PARTITION BY RANGE (TO_DAYS(date)) (...) Then every day do ALTER TABLE .. DROP PARTITION ... ALTER TABLE .. REORGANIZE PARTITION future INTO PARTITION ..., -- preparing for tomorrow ...


0

By relying on ResultSet's ability to load limited data in memory we are able to come up with a solution in Java using Dynamic Comparator. Solution is to take first record from each resultSet and sort it in java and return first element from sorted data. Detailed Solution: First we built a program which can give us a dymanic Comparator based on the criteria ...


2

@wBob got it in one. It's all about those NULLs. The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation. This allows the SWITCH to run. CREATE TABLE [dbo].[SourceTable]( [Col1] [bigint] NULL, [Col2] [int] NULL, [Col3] [int] NULL, ...


0

Put the tables in a different tablespace. Then you can put the datafiles under a different location/group/naming scheme to the rest of the data, and configure your backups to ignore these files. If you need to recover the database without this data, you should drop the tablespace with the database in mounted mode: startup mount; drop tablespace ...


0

For "2) How to I take a partition out of service?" This is probably a terrible idea but... Could you: Bring the database offline Delete the physical files in the FileGroup Bring database online Alter partition to exclude the FileGroup Rebuild Indexes


0

You need to split each partition into two partitions? Something like this (please verify the syntax): ALTER TABLE table1 REORGANIZE PARTITION p2015half1 INTO partition `p0` values less than ('2015-10-01'), partition `p0` values less than ('2016-01-01') ; That is, take any number of partitions (1 in this case), copy the data into any number of ...


3

It sounds like your architecture has gone past partitioning and is sharded. If you have system software or an application framework in place which can handle sharding that is where this requirement should be implemented. In order to get the correct sort sequence you will have to have the results from all involved shards in the same place at the same time. ...



Top 50 recent answers are included