Tag Info

New answers tagged

2

Migrate piece-wise. Rename your table to tblsapdispatch_old. Create a new table called tblsapdispatch_new. This new table has the partitioning you want. Create a view called tblsapdispatch which unions the two together. This way the application is agnostic to the change. Move data from _old to _new in batches. The batch size will be found by testing. Move ...


1

from the technical article Statistics Used by the Query Optimizer in Microsoft SQL Server 2008statistics are out of date when: The table size has gone from 0 to >0 rows. The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than ...


0

We found the issue. It is a bug in SQL Server. When we set READ_WRITE the command is not transfeered properly into the mirror DB. When script start change partitions on the mirror server an error occured. After that the syncronization is ruined and the DB on the mirror is locked (in suspended state). We fix the issue by updating SQL Server to the latest ...


0

Your expression is too complex. There are many severe limitations of what can go into BY RANGE. Use simply UNIX_TIMESTAMP(time). Even if it would work, FLOOR does not add anything to the expression. BTW, the trailing hh:mm:ss is optional in this situation. These are identical: UNIX_TIMESTAMP('2015-08-01 00:00:00') UNIX_TIMESTAMP('2015-08-01') Now, on ...


2

Actually, I think this is the opposite of a bug. the 2008 document states "Source and target tables must share the same filegroup" but it looks as though they only started paying attention to their rules in 2014. This article (made for 2014 / 2016 CTE) states "The corresponding indexes, or index partitions, must also reside in the same filegroup" TechNet


5

Not sure if it's a bug, per se but it's definitely an interesting occurrence. Online partition rebuilds are new in SQL Server 2014 so there may be some internals to sort through with this. Here's my best explanation for you. Incremental statistics absolutely require that all partitions be sampled at the same rate so that when the engine merges the stats ...


1

MySQL doesn't reduce/reclaim the size of previously deleted data. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space. Source: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html To verify MySQL replication integrity, you may want ...


1

If you are still inserting data for 2014, then you risk problems with this method, because rows inserted between steps 2 and 5 are going to end up getting dropped rather than moved. If you are not still inserting data for 2014, then I think you should change step 5 to "rewrite the trigger to throw an error upon insertion of 2014 data" and move it up to be ...


3

All of this is unrelated to inheritance and partitioning. It's about indexing and query plans in general. The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even more readily use an index for wider rows. Possible reasons for the unexpected sequential scan include: You have substantially fewer rows in your tables for the ...


4

Your existing index on DATE is obviously useless for the query. The first obvious step for your query: SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2'; is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like: CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b); ...


4

"date" Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like: CREATE TABLE test ( id serial PRIMARY KEY , ts timestamp NOT NULL -- also adding NOT NULL ...


0

Right okay, after managing to get a little more time to look at this I have found the issue. Obvious really but it always is in hindsight. So, partition elimination occurs when you specify a literal as the filter on the partitioning column. Eg. WHERE partition_column = 20150812. Cool. However, what if you might need to filter on more than one date. Maybe ...


0

Always have an explicit PRIMARY KEY for any InnoDB table. If practical, use a "natural" key -- a column, or combination of columns, that is unique. Otherwise, add an AUTO_INCREMENT. Learn about "composite indexes" -- this is using more than one column in an index. It is often a good optimization. It is not the same as having an index on each column. It ...



Top 50 recent answers are included