partitioning: SQL Server query analyzes more partitions than expected

Reasoning

When comparing the column of the unpartitioned table with the partitioned table, the sql server may not know DayOfMonth It will remain, even if all are 15.

As a result, I would not know what partitions to return to satisfy this filtering by joining the two tables.

A different example to give more information can be found here.


Tests

I was able to recreate your problem, for more questions, add as much information as possible.
This could be table definition, indexes, partition scheme, …

The DDL and DML is at the bottom.

When executing the query we can obtain the same result:

enter the description of the image here

62 partitions returned.

In search predicates, try to filter what you can, these are the two filters in CalendarId they don't have the + DayOfMonth-1.

enter the description of the image here

This translates into

WHERE ...
    AND (( dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601))
...

When you run the query with these filters, you will see the same number of rows returned when accessing the table in the query plan.

Only after obtaining this data, can you and will join the AccountInfo table in the ID& # 39; s and the DayOfMonth - 1

enter the description of the image here

enter the description of the image here

And to confirm when we change the query:

SELECT
    ai.Name
    ,SUM(dc.Component )
    ,SUM(dc.Value) Value
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601  - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601  - 1)))
GROUP BY            Name

Yes delete the partitions:

a


Solution

I will assume that DayOfMonth just go up to 31 days.

If you know these limits, you can & # 39; encode them & # 39; so that the sql server knows which partitions to look for. After this, you can add additional filtering.

E.G.

WITH CTE
AS
(
SELECT
    ai.Name
    ,dc.Component 
    ,dc.Value 
    ,dc.CalendarId
    ,ai.DayOfMonth
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 )
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270701)))

)

SELECT Name,Component,SUM(Value)
FROM CTE 
WHERE ((CalendarId >= (20190601 + DayOfMonth - 1)
        AND CalendarId < 20190701)
    OR (CalendarId >= 20270601
        AND CalendarId < (20270601 + DayOfMonth - 1)))

GROUP BY        
    Name
    ,Component

The sole purpose of the cte is to let the SQL server know that it can also filter dc.CalendarId >= dc.CalendarId >= (20190601 ) OR dc.CalendarId < (20270701)).

Side note: Adding a restriction does not work to enforce this.

This query gives us the result we want, with the correct separation of the partition:

enter the description of the image here

enter the description of the image here

Only access to partitions 19 and 21.


Test data

CREATE SCHEMA WARM
GO

CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
                            Component int, 
                             Value int, 
                             CalendarId INT, 
                             AccountInfoId int
                             )


CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                              Name VARCHAR(25),
                              CorrelationId uniqueidentifier,
                              DayOfMonth int
                              );
USE (master)
GO
ALTER DATABASE (test) ADD FILEGROUP (Partitionfg)
GO
USE (test)
GO
CREATE PARTITION FUNCTION (PF_CostDateByYearMonth)(int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,
    20190101,
    20190201,
    20190301,
    20190401,
    20190501,
    20190601,
    20190701,
    20270601,
    20270701,
    20270801,
    20270901,
    20271001,
    20271101,
    20271201,
    20280101,
    20280201,
    20280301,
    20280401,
    20280501,
    20280601,
    20280701,
    20280801,
    20280901,
    20281001,
    20281101,
    20281201,
    20290101,
    20290201,
    20290301,
    20290401,
    20290501,
    20290601,
    20290701,
    20290801,
    20290901,
    20291001,
    20291101,
    20291201,
    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)


CREATE PARTITION SCHEME (PS_CostDateByYearMonth)  
AS PARTITION (PF_CostDateByYearMonth)  
ALL TO ( (Partitionfg) );  

CREATE UNIQUE CLUSTERED INDEX ptci ON  Warm.DailyCosts(CalendarId,Id) ON (PS_CostDateByYearMonth)(CalendarId) ;
USE (master)
GO
ALTER DATABASE (test) ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:DATATestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP (Partitionfg)
GO
USE (test)
GO

INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20180101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2018
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20190101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2019
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )

SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20280101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2028

INSERT INTO Warm.AccountInfo(
                              Name ,
                              CorrelationId,
                              DayOfMonth 
                              )

SELECT TOP(3000000) --3M
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
       '00000000-0000-0000-0000-000000000000',
       15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
CREATE NONCLUSTERED INDEX (IX_Component_Value_DailyCosts) ON Warm.DailyCosts
 (Component,Value) 
ON (PS_CostDateByYearMonth)(CalendarId);
GO

18.04 – Does my computer have no other partitions mounted?

I have an Ubuntu 18.04 loaded on my machine. Recently I got a little confused and made new partitions making the others a bit small. But now I have to eliminate them but not find the way. I know my root directory that I should not delete and search for all other important partitions, but I cannot obtain them, since only the root is indicated.
This is the result of lsblk /dev/sda

NAME    MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda       8:0    0 298.1G  0 disk 
├─sda1    8:1    0   6.9G  0 part 
├─sda3    8:3    0  35.6G  0 part 
├─sda5    8:5    0   3.7G  0 part 
├─sda6    8:6    0    47G  0 part 
├─sda7    8:7    0  13.7G  0 part 
├─sda8    8:8    0 103.5G  0 part /
├─sda9    8:9    0   6.8G  0 part 
└─sda10   8:10   0   5.4G  0 part 

partitioning: new folders cannot be created on old partitions

I just installed Linux on my laptop and it works fine, but there is a problem, the installation I did in the c directory so it has been formatted and installed, but the other partitions like the E directory, I have my folders but I can't create or delete any folder inside it.

partitioning: partitions for tables that only contain identifiers in PostgreSQL 9.6

I am working on adjusting queries and decided to go to Partitioning Tables. Totally 3 tables in the base query and only one table makes the query slower, tried to create a partition but that table only contains identifiers that refer to two other tables. The remaining two tables created a partition based on the date and another with category. Each time the partitioned table joins the 3-table query it is too slow since it has 20 million rows (this table will be updated every week to get new data, so it is getting bigger).
This is the sample query:

select t.id as topic_id, t.value as value, t.topic as topic, COUNT(pt1.topic_id) as count  from posts_test p --partitioned table---
 join post_locations_test plt on plt.post_id = p.id ---partitioned table---
 join post_topics pt on pt.post_id = plt.post_id -- not partitioned --
 join post_topics pt1 on pt.post_id = pt1.post_id -- not partitioned--
 JOIN topics_test t on pt1.topic_id = t.id -- partitioned---
 where p.date_posted BETWEEN ('2019-07-15'::date - interval '6 month') AND '2019-07-15'::date and plt.country_code = 'GB' and pt.topic_id = 'a451d3e2-f593-4b39-8f4b-d0a97422a344' group by t.id,pt1.topic_id  ORDER BY count DESC;

So, in the previous query, posts_test has post_id and the topic table has topic_id. The Post_topics table contains both post_id and topic_id where this table reference for other tables. Because post_topics is assigned by topic ID, each post is assigned with several topics. This is where I find it difficult to optimize the union.

Post_id is almost 8 million and topics_id is 8k in number. Either way, I can't create partitions for these numbers. I lost optimizing this table.

enter the description of the image here

Could someone please suggest what I can do to improve the union or how we can create a partition in the post_topics table, just having identifiers?

Disk utility: why is it necessary to manually clean the partitions when deleting a copy of macOS in a dual macOS installation?

In the next thread, remove macOS from an APFS container?

There is an answer from @ "David Anderson" that says if you install 2 copies of macOS on separate partitions, then try to delete one, you should perform this multi-step cleaning.

My question is why is it necessary to perform this cleaning? How do these commands know not to eliminate High Sierra main recovery or the second copy you installed? And if you delete these partitions, is there still a preboot and recovery partition? Please clarify

(Note: a mod advised me to publish this as a separate thread of questions, so I hope it complies with all the difficult rules here).

Windows 10: I tried to install Ubuntu and I am stuck with two EFI partitions. Is it safe to delete one?

Recently I tried to install Ubuntu on my Lenovo Windows laptop, a task that I have now failed for the third time (on two computers). However, after I am asked to partition my data unit (HDD, D:), as expected, a new partition was created.

However, after inspecting partitions in disk management, it seems that a second EFI partition was also created on the hard disk. At least, that's what I imagine, since I don't see why Windows 10 would need two EFI partitions.

This EFI partition is between my data and the Ubuntu partitions, which causes the merging of the partitions (or, when extending one, the other does not have valuable data) without creating a "dynamic volume" (I don't remember the exact term ) is impossible.

The questions I would like to ask now is this:

  1. Is it safe to delete this partition?
  2. If so, how?
  3. If more information is needed, is it possible to inspect the EFI partition?

To clarify:
I have two disks, 2XXGB SSD, 9XXGB HDD. Windows (seems to be) is installed on the SSD.

Partitions – How do I solve this compositional equality?

c (n) is the notation for the composition of a positive integer n, that is, a partition where the order of the terms of the sum is taken into account.

Example:

c(3) = 4 because 3 = 3, 3 = 1+2, 3 = 2+1, 3=1+1+1

Here is the sum.

I know that the geometric sum is used after the first step, but I don't get the full proof. Can anyone explain the steps to me? Thank you!

reference request: algorithm to calculate partitions of a graph in N cliques

Does anyone know of an efficient algorithm to calculate the partition of a chart in N cliques?

Notice that N is the number of cliques and not the size of them.

I have heard about the problem of the 2 cliques, but I am interested in the more general version.

Thank you!

SD card: mounting of multiple ext4 partitions on Android 7+

I'm eliminating exFat for me Micro SD card After all my images broke down, they became corrupted and the directory was blocked. It seems that I can not write to a specific directory. Chkdsk it will simply abort saying:

The disk does not have enough space to replace the faulty clusters
detected in the file  WhatsApp  .Shared of the name 1.
Corruption was found when browsing the files in the  WhatsApp  (6) directory.

so I would like to give an opportunity to Ext4 partitioning so cheap micro SD card, not only 2 (the standard app2sd Y sdcard1 design) but 3. One for applications and another 2 depending on the writing activity, I adjusted the block parameters, etc. for each one
I am able to mount the partitions and a Rooted Total Commander You can see them all
BUT other applications can not see the partitions that I created. How I understand Vold It helps other applications to be aware of this. So how do I get this formally & # 39; with a sound & # 39; hackish & # 39;

what I did :

mkdir / storage /
mount -t ext4 -or noatime, rw / dev / block / mmcblk (x) p (y) / storage /
where x and y are variables of the partition file in the proc directory.

I read about a fstab file out there that can be modified but it would be overwritten every start.

even the second partition that app2sd (or link2sd) creates ONLY can be approached as root. So prolly those applications that ask. root You can see this, but other applications can write to it.
Ext4 is linux . Sounds like Android He seems to ignore his own child & # 39; biological & # 39; ext4 and instead recognize a stepson & # 39;exfat / FAT & # 39;

I'm sure I'm missing something here. Any ideas ?
I am rooted with Magisk in a Galaxy j7 with, of course, TWRP (3.x) installed

ssis – How to process dynamic partitions in SSAS

How to process dynamic partitions in SSAS

Hi all,

I'm new to SSAS, this is my case, I have a view table, that is, dbo.table that has data from 2016 to 2018. By using the dbo.table view, I created a partition called vwTable. Now I want to get data monthly using partitions. We create partitions for each month as shown below.

vwTable_201601, vwTable_201602, etc.,

But I'm not sure how to process these partitions automatically through scheduled jobs.

Can someone help me process these dynamic partitions through a scheduled job?

Thanks in advance

Chandana