问题[partitioning](dba)

我正在压缩分区表。

在分区表之前，我尝试使用以下步骤使用普通表：

DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => 'USER',
    orig_table => 'ORIGINAL',
    int_table => 'INTERIM'
);




BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('USER','ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/



BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
      uname => 'USER',
      orig_table => 'ORIGINAL',
      int_table => 'INTERIM',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/



DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('USER', 'ORIGINAL', 'INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/


DECLARE
   error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => 'USER',
    orig_table => 'ORIGINAL',
    int_table => 'INTERIM',
    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers => TRUE,
    copy_constraints => TRUE,
    copy_privileges => TRUE,
    ignore_errors => FALSE,
    num_errors => 0
);
END;
/

ALTER TABLE ORIGINAL MOVE ROW STORE COMPRESS ADVANCED;

然而，对于分区表来说，这是不可能的。

有人能告诉我在Oracle 19c中压缩分区表的步骤吗？

我正在尝试使用以下查询将表分区切换为临时表：

create partition function [PFCT_T](datetime2) as range right for values ()
create partition scheme [PSCH_T] as partition [PFCT_T] all to ([primary])
    
create table T(A1 datetime2 not null, A2 int not null) on [PSCH_T](A1)
create clustered columnstore index [CIX_T] on dbo.T
    
insert into T(A1, A2) values ('2024-01-01 00:00:00', 1)
    
select top 0 * into #T from T
create clustered columnstore index [CIX_#T] on #T
    
alter table T switch partition 1 to #T
--Results in:
--Msg 608, Level 16, State 1, Line 15
--No catalog entry found for partition ID 5620492390548766720 in database 23. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.

是否无法将分区切换到临时表（它适用于普通表）或者我错过了什么？

我刚刚开始分区，并且发现了很多关于如何设置和管理滑动窗口分区的好博客（我选择关注的博客位于SQL Shack和MSSQLTips）。这两个 how-to 的说明都非常清楚，但是它们都没有讨论在添加新分区、文件组和文件时如何管理添加到数据库的空数据文件。

我对将空的数据库文件留在周围感到疑虑（对我来说，这只是马虎），所以我想到建立固定数量的数据文件进行分区（12 个，每个日历月一个）并重新使用这些文件。例如，数据库文件不是名为“MyData_2023_02”，而是名为“MyData_Month02”或类似名称。但是，我不确定如何为新分区“回收”数据库文件（同样，我对分区很陌生）。

有人可以指出我正确的方向吗：

• 交换和合并分区后清理空数据库文件
• 如何为新分区回收数据库文件

我宁愿从一开始就学习正确/适当/更好的方法来处理分区，而不是以后必须忘记并重新学习。

编辑：根据 Dan Guzman 的解决方案，我尝试为我的分区使用单个文件和单个文件组。没那么顺利：

ALTER DATABASE MY_DW
ADD FILEGROUP FG_PriceFiles_Sliding_Window

CREATE PARTITION FUNCTION [PF_PriceFiles_MonthlyPartition] (date)
AS RANGE RIGHT FOR VALUES 
('2019-07-01', '2019-08-01', '2019-09-01', '2019-10-01', '2019-11-01', '2019-12-01')
GO

CREATE PARTITION SCHEME PS_PriceFiles_MonthlyPartition
AS PARTITION PF_PriceFiles_MonthlyPartition
TO ('FG_PriceFiles_Sliding_Window', 'Primary')

Msg 7707, Level 16, State 1, Line 9
The associated partition function 'PF_PriceFiles_MonthlyPartition' generates more partitions than there are file groups mentioned in the scheme 'PS_PriceFiles_MonthlyPartition'.

我在 PostgreSQL 14 中有一个简单的分区表，如下所示：

create table entity
(
    dataset_id integer not null references dataset (id),
    ...
) partition by list (dataset_id);

create table entity_1
(
    like entity including constraints including defaults,
    constraint entity_1_pkey primary key (entity_id),
);

alter table entity attach partition entity_1 for values in (1);

创建一个分区的原因是先“分离”一个分区，然后在单独的语句中附加它是为了避免在父表上获得排他锁entity（分割。这是Laurenz Albe在https://stackoverflow.com/a/67016755/1536933中建议的解决方案，它非常适合创建分区。

不幸的是，我在删除分区时遇到了同样的问题：只要客户端正在从任何分区读取数据，我就无法删除任何其他分区：不仅被drop table entity_1阻塞，甚至alter table entity detach partition entity_1 concurrently被阻塞！我看不到pg_locks“同时分离”语句的任何相关锁定（没有granted=false），但 pgAdmin 仪表板显示Wait event: Lock: virtualxid和Blocking PIDs: (pid of the reading process)

从其他分区读取数据时如何删除分区？当然这应该是可能的？

在超大规模服务层中运行的 Azure SQL DB 中，是否支持表级分区？它似乎没有在已知限制页面中被调用。

尝试通过 SSMS 在 Azure SQL 超大规模数据库中创建文件组（这是创建分区方案的第一步）时，它引发了错误：

此版本的 SQL Server 不支持关键字或语句选项“文件组”。

所以，要么做分区是不可行的，要么必须有其他的方法来创建它们。

• 有人可以澄清是否支持分区吗？
• 如果是这样，如何为表创建它们？
• 如何处理分区切换/新分区创建？

我有一个verbatim由整数列 dataset_key 分区的表，它也是复合主键的一部分：

\d+ verbatim_default
                     Partitioned table "public.verbatim_default"
   Column    |   Type   | Collation | Nullable |  Default                                                               
-------------+----------+-----------+----------+-----------
 id          | integer  |           | not null | 
 dataset_key | integer  |           | not null | 
Partition key: LIST (dataset_key)
Indexes:
    "verbatim_pkey" PRIMARY KEY, btree (dataset_key, id)
Partitions: verbatim_2049 FOR VALUES IN (2049),
            verbatim_2064 FOR VALUES IN (2064),
            verbatim_2066 FOR VALUES IN (2066),
            verbatim_3 FOR VALUES IN (3),
            verbatim_default DEFAULT, PARTITIONED

有一个 verbatim_default 分区可以捕获任何分区中未明确提及的所有数据集键。这个默认分区本身又被 HASH 分区，总共包含大约 1 亿条记录。

当我使用单个 dataset_key 附加一个新表时，它需要很长时间，因为显然需要扫描 verbatim_default 表。我的目的是提供一个避免扫描默认分区的检查约束。如果我使用这样的简单检查约束dataset_key < 10000可以正常工作并且附加是即时的。

但是，如果我使用更复杂的约束来进行一些计算，则不使用检查，而是扫描整个表。不起作用的检查示例和附加语句示例：

ALTER TABLE verbatim_default ADD CONSTRAINT vb_check1 CHECK (dataset_key <= 10000);
ALTER TABLE verbatim_default ADD CONSTRAINT vb_check2 CHECK (dataset_key <= 1000 OR dataset_key+2500<10000);
ALTER TABLE verbatim_default ADD CONSTRAINT vb_check3 CHECK (dataset_key+2500<10000);
ALTER TABLE verbatim_default ADD CONSTRAINT vb_check4 CHECK (dataset_key%100 <> 0);

-- this is instant as it can use check1
ALTER TABLE verbatim ATTACH PARTITION md_verbatim FOR VALUES IN (10800);

-- this scans verbatim_default even though check2, 3 and 4 apply
ALTER TABLE verbatim ATTACH PARTITION md_verbatim FOR VALUES IN (8000);

这是预期的吗？我正在使用 PostgreSQL 13。这可能在 PG14 中有所不同吗？

版本： Oracle 12cR2 EE

该表包含以下列类型： NUMBER、CLOB、NVARCHAR2、TIMESTAMP、VARCHAR2

表大小： 95GB

表格行数： 20M

表分区？不

表上的索引：表上只有一个主键作为索引。

依赖关系： “每一行之前（插入）”只有一个触发器作为对表的依赖项。

约束：有 3 个约束（其中 1 个是主键，其他是检查）。

我们要存档此表。但是，我无法决定如何为这么大的表进行归档。使用 CTAS 将某个日期之前的记录移动到新表中，然后在现有表中删除这些导入的记录，这将是一场噩梦。

我认为最好的方法是让表间隔按月分区（如果你有更好的解决方案，我愿意接受任何想法）。

几个术前和序列问题：

1. 有没有办法预先找出在对这种大小的表进行分区期间会生成多少存档？
2. 我们有机会提前计算过程的持续时间吗？
3. 停止将在此过程中加载的其他数据库作业是否是一个不错的选择？
4. 是否需要对操作或其他依赖项或触发器的主键索引进行更改？

对于后处理：

1. 删除大分区时是否会导致数据库负载过重？

2. 例如，是否可以设置这样的订单；我们可以在主表上只保留最近 3 个月或最近 6 个月的数据，并每月将较旧的分区移动到归档表中吗？迁移时只使用导出导入方式是否正确？

3. 迁移的分区将从迁移过程中删除。这样做会不会在归档生成的基础上出现问题？

谢谢你。

此致。

我有一个相当大的（~10M 行）表，最近开始放慢速度。作为解决方案，我们决定将该表划分为 1,000 个表，按客户端 ID 的哈希进行分区。这样，我们通常每个表只有几个客户端。我们在生产中与现有表一起创建了这个分区，并填充了所有数据，并使它们与触发器函数保持同步。对分区表手动运行查询显示出巨大的希望，大多数人看到 10-100 倍以上的加速。认为一切都很好，我们在生产环境中交换了两个表名，它立即使我们的数据库崩溃。

1. 可释放内存立即从 6 GB 降至 0
2. 交换使用量从 0 GB 跃升至 3 GB
3. 数据库变慢了，以至于我们不得不完全关闭网络应用程序，因此没有与数据库的连接并恢复表名交换。

我已经完成了使用该表的查询，并且所有查询都有一个明确的WHERE client_id = <client_id>，因此他们应该只使用他们的分区而不是父表。他们都没有选择跨多个分区。

什么可能使用这么多内存？Postgres 分区有那么多内存开销吗？根据我的阅读，Postgres 应该能够处理数万个分区，所以我的 1,000 个应该不是问题。

这一切都在具有 4 个 vCPU 和 16 GB RAM 的 RDS M5.xlarge 实例上。

更新： 我们将实例大小升级为具有 32 GB RAM 的实例，并将分区数量从 1,000 个缩减到仅 250 个，然后再次尝试。这次我们看到内存立即下降，但在趋于平稳之前只有大约 3-4 GB，这表明内存随着分区数量的增加而扩展。同样，在交换表名之前，我们已经通过触发器对分区执行了大量的 INSERT/UPDATE/DELETE 命令，并且没有看到明显的影响。只有当表名被交换以便 SELECT 开始命中它时，我们才看到内存的巨大下降。这似乎与说明内存命中对于 UPDATE 和 DELETE 命令特别糟糕的文档不一致。

是否可以发出查询来判断 PARTITION 列是日期还是时间戳。

例如，这应该返回时间戳

CREATE TABLE ts (
num_val NUMBER(6),
ts_val TIMESTAMP)
  PARTITION BY RANGE (ts_val) 
  INTERVAL ( NUMTODSINTERVAL (1, 'DAY') ) ( 
    PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2021-01-01 00:00:00.000000')
  );

这应该返回日期

CREATE TABLE dts (
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);

我在主文件组上有一个现有表，我想对其进行分区。分区键在年份，这是一个计算列。我想以这样的方式对表进行分区，以便最终每年的数据都在它自己的文件组上。我首先想拆分 2 年，所以稍后我可以测试更多关于如何使用拆分命令拆分其他数据的内容。现在，我可以创建分区函数和方案，并且我还看到某年的数据在正确的分区中，但是我无法在正确的文件组中获取物理数据。似乎数据仍驻留在该主文件组中。我尝试重建索引，但这仍然没有将数据移动到正确的文件组中。最后，该表将具有聚集列存储索引，但我也尝试使用聚集行存储索引。我这样做的原因是因为 SQL Server 似乎不允许列存储索引拆分和合并非空分区（我尝试了一些拆分和合并但结果相同的东西），所以我认为这至少可以工作。如果您有任何建议或意见，请在此填写。顺便说一句，我正在使用 SQL Server 2019。

现在对于代码，我使用 Stackoverflow2013 数据库：

use StackOverflow2013;
go

-- Create file groups for partitions
alter database [StackOverflow2013]
add filegroup StackOverflow2013_2008;

ALTER DATABASE [StackOverflow2013]
    ADD FILE 
    (
    NAME = [StackOverflow2013_2008],
    FILENAME = 'E:\DATA\StackOverflow2013_2008.ndf',
        SIZE = 1024 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 512 MB
    ) TO FILEGROUP [StackOverflow2013_2008]
    
alter database [StackOverflow2013]
add filegroup StackOverflow2013_2009;

ALTER DATABASE [StackOverflow2013]
    ADD FILE 
    (
    NAME = [StackOverflow2013_2009],
    FILENAME = 'E:\DATA\StackOverflow2013_2009.ndf',
        SIZE = 1024 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 512 MB
    ) TO FILEGROUP [StackOverflow2013_2009]


-- Drop the current default index, we want to build one later on the partition key
ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [PK_Comments_Id] WITH ( ONLINE = OFF )

-- Add partition key column
alter table [StackOverflow2013].[dbo].[Comments]
add [year] as (datepart(year, CreationDate));

go
-- Add partition function based on year 
-- For now we only want 2008 and 2009, other years will be migrated later to test with split function
create partition function fun_Comments(int)
as range left for values (2008, 2009);

-- Add partition scheme
create partition scheme scheme_Comments
as partition fun_Comments
to (StackOverflow2013_2008, StackOverflow2013_2009, [Primary]);

-- Check the partition numbers and who's next
SELECT DestinationId = DestinationDataSpaces.destination_id
    ,FilegroupName = Filegroups.name
    ,PartitionHighBoundaryValue = PartitionRangeValues.value
    ,IsNextUsed = CASE 
        WHEN DestinationDataSpaces.destination_id > 1
            AND LAG(PartitionRangeValues.value, 1) OVER (
                ORDER BY DestinationDataSpaces.destination_id ASC
                ) IS NULL
            THEN 1
        ELSE 0
        END
FROM sys.partition_schemes AS PartitionSchemes
INNER JOIN sys.destination_data_spaces AS DestinationDataSpaces ON PartitionSchemes.data_space_id = DestinationDataSpaces.partition_scheme_id
INNER JOIN sys.filegroups AS Filegroups ON DestinationDataSpaces.data_space_id = Filegroups.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS PartitionRangeValues ON PartitionSchemes.function_id = PartitionRangeValues.function_id
    AND DestinationDataSpaces.destination_id = PartitionRangeValues.boundary_id
WHERE PartitionSchemes.name = N'scheme_Comments'
ORDER BY DestinationId ASC;

检查分区 1 的行

SELECT * FROM Comments 
WHERE $PARTITION.fun_Comments(year) = 1; 

检查分区 2 的行

SELECT * FROM Comments 
WHERE $PARTITION.fun_Comments(year) = 2; 

检查文件大小

（非常大的查询）

-- 创建新的聚集索引以正确分布数据

create clustered index [CCIX_Comments] ON [dbo].[Comments] (year)

再次检查文件大小

所以在我看来，所有数据实际上仍在主文件组中，因为新文件组是 emtpy。该表为 7 GB，因此我至少希望其中有一些数据。

所以基本上我的问题是，在这种情况下，如何正确地在文件组中的文件上重新分配数据？