Tag Info

New answers tagged

1

Oracle said it wasn't a known issue, and basically asked us to re-run with tracing on. Unfortunately on a large production database, and given large downtime required to rollback last time, this wasn't really an option. We had suspicion that it is related to this bug fixed in 11.2.0.4.2 Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ...


3

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there ...


3

I can reproduce the bad plan. I found three workarounds: OPTION (RECOMPILE) INNER LOOP JOIN hints A nasty, crazy rewrite: . SELECT y.* FROM (VALUES (@CustomerPartitionKey)) x(CustomerPartitionKey) CROSS APPLY ( SELECT --TOP 300 CI.ContactId, I.Ordinal, I.Identifier FROM #identifiers I INNER JOIN ...


3

Be aware of the implications in regions that have the nasty habit of changing their clocks twice a year and Check this Link


2

A few details are still not clear, but let's see, what we can do now. First, having about 100M rows and 231 partitions sounds not that good. The resulting tables will be too small, in turn their number too high - I cannot tell the threshold, but at some point the query planning migt get too expensive. I think it is quite possible that yearly partitions ...


0

This seems to be known problem with to_days and between - if there were some invalid dates in the table, to_days return NULL on them and stores them in the first partition. And when evaluating BETWEEN, mysql can sometimes decide that some dates in that range are invalid and so it thinks it has to check first partition to find them. Such dates giving NULL may ...


0

I cannot fully answer this question but I can give you some tips on what to look around for. First thing I would do is check the dates inside each partition as follows SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q1) GROUP BY date_x; SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q2) GROUP BY date_x; SELECT date_x,COUNT(1) ...


1

In addition to Rolando's suggested schema and my improvement on RENAME, you may want two more things: Purge "old" data via DROP PARTITION. This is about the only real use for moving to partitioning. Add a new partition using ALTER TABLE REORGANIZE PARTITION p99999999999 INTO PARTITION p2016_01_jan VALUES LESS THAN ('2016-02-01'), PARTITION p99999999999 ...


1

Here are the Steps to Partition Your Table by Month Create a Temp Table, Partitioned and Indexed on LogDate CREATE TABLE paramlog_ems_new ( SiteIndex smallint(5) unsigned NOT NULL, RegionIndex smallint(5) unsigned NOT NULL, OrganizationIndex smallint(5) unsigned NOT NULL, DeviceSlaveId smallint(5) unsigned NOT NULL, UserIndex smallint(5) unsigned ...


2

Assuming we are talking about 1:1 relationships among all tables. Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate ...


0

A select on a single table should always be faster. As soon as you have found your vehicle you already have all the details. However you lose the efficiency of normalization. For example if 1 car had many models with different options. Is this a reference db of all cars? Or a list of second hand vehicles? Would there be many examples of the same ...



Top 50 recent answers are included