执行排除特定分区名称的 SQL 查询

Question

假设表my_table分为 1000 个分区，如下例所示：

P1, P2, P3, ... , P997, P998, P999, P1000

分区按日期组织，主要是每天一个分区。 例如：

P0    < 01/01/2000 => Contains around 472M records
P1    = 01/01/2000 => Contains around  15k records
P2    = 02/01/2000 => Contains around  15k records
P3    = 03/01/2000 => Contains around  15k records
...   = ../../.... => Contains around  ... records
P997  = 07/04/2000 => Contains around  15k records
P998  = 08/04/2000 => Contains around  15k records
P999  = 09/04/2000 => Contains around  15k records
P1000 = 10/04/2000 => Contains around  15k records

请注意，P0<01/01/2000，而不是=

现状：

在不知道日期的情况下查找特定记录时，我正在做 a：
SELECT * FROM my_schema.my_table WHERE ... ;
但这需要太多时间，因为它确实包括P0(30s).

不可能的解决方案：

所以最好的主意是执行一个SQL查询，例如：
SELECT * FROM my_schema.my_table FROM PARTITION(P42) WHERE ... ;
但我们永远不知道哪个分区是记录。我们也不知道与分区关联的日期。当然，我们不会遍历所有分区 1 x 1

糟糕的解决方案：

我可以聪明地做 5 x 5：
SELECT * FROM my_schema.my_table FROM PARTITION(P40,P41,P42,P43,P44) WHERE ... ;
但是与上述相同的问题，我不会遍历所有分区，即使是 5 x 5

不太糟糕的解决方案：

我也不会运行 do(不包括列表中的P0)：
SELECT * FROM my_schema.my_table FROM PARTITION(P1,P2,...,P99,P100) WHERE ... ;
查询太长，我必须为每个请求计算分区名称列表，因为它不能总是以P1开头或以P100结束(每天都有一些分区被删除，一些分区被创建)

聪明的解决方案(但它存在吗？

我该怎么做这样的事情？
SELECT * FROM my_schema.my_table NOT IN PARTITION(P0) WHERE ... ;
or
SELECT * FROM my_schema.my_table PARTITION(*,-P0) WHERE ... ;
or
SELECT * FROM my_schema.my_table LESS PARTITION(P0) WHERE ... ;
or
SELECT * FROM my_schema.my_table EXCLUDE PARTITION(P0) WHERE ... ;

有什么办法可以做到这一点吗？

我的意思是选择所有分区期望其中一个或部分的方法？ 注意：我事先不知道销售日期的价值。在表格中，我们有类似

CREATE TABLE my_table 
(
recordID NUMBER(16) NOT NULL,     --not primary
dateOfSale DATE NOT NULL,         --unknown
....
<other fields>
)

在回答之前，请阅读以下内容：

1. 索引用法：是的，它已经优化了，但请记住，我们不知道分区日期
2. 不，我们不会删除 P0 中的记录，我们需要保留它们至少几年(根据每个国家/地区的法律，3 年、5 年，有时是 10 年)
3. 我们可以将 P0 "拆分"为多个分区，但这并不能解决全局 SELECT 的问题
4. 我们无法将该数据移动到新表中，我们需要将它们保留在此表中，因为我们有多个服务和软件在其中执行选择。我们必须编辑大量代码，以便为每个服务和后端的第二个表添加查询。
5. 我们不能做一个akaWHERE date > 2019子句并为日期字段编制索引，原因有很多，在这里解释需要太多时间。

Answer 1

下面的查询，即 UNION ALL 中的两个查询，但我只想要 1 行，将立即停止找到一行。如果我们在第一部分有一行，我们不需要进入 UNION ALL 的第二部分。

SQL> select * from
2  ( select x
3    from   t1
4    where  x = :b1
5    union all
6    select x
7    from   t2
8    where  x = :b1
9  )
10  where rownum = 1
11  /

有关这方面的简单证明，请参阅 https://connor-mcdonald.com/golden-oldies/first-match-written-15-10-2007/。

我假设您是在假设大多数情况下您感兴趣的记录在最近的较小分区中的情况下工作的。 在没有任何其他信息可以在正确的分区中磨练的情况下，您可以这样做

select * from
( select ...
from   tab
where  trans_dt >= DATE'2000-01-01'
and    record_id = :my_record
union all
select x
from   tab
where  trans_dt < DATE'2000-01-01'
and    record_id = :my_record
)
where rownum = 1

如果我们失败并且在其他任何地方找不到它，它只会扫描大分区。

但是您的问题似乎确实在尖叫着要求索引以避免所有这些工作

Answer 2

让我们简化分区表，如下所示

CREATE TABLE tab
(  trans_dt       DATE
)
PARTITION BY RANGE (trans_dt)
( PARTITION p0 VALUES LESS THAN (DATE'2000-01-01')
, PARTITION p1 VALUES LESS THAN (DATE'2000-01-02')
, PARTITION p2 VALUES LESS THAN (DATE'2000-01-03')
, PARTITION p3 VALUES LESS THAN (DATE'2000-01-04')
);

如果要跳过查询中的大型分区P0，只需(因为这是第一个分区)将分区键约束为trans_dt >= DATE'2000-01-01'

您将需要两个谓词和or来跳过中间的分区

查询

select * from tab
where trans_dt >= DATE'2000-01-01';

检查执行计划，您会看到Pstart = 2中的预期行为(即修剪第一个分区)。

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR  |      |     1 |     9 |     2   (0)| 00:00:01 |     2 |     4 |
|   2 |   TABLE ACCESS STORAGE FULL| TAB  |     1 |     9 |     2   (0)| 00:00:01 |     2 |     4 |
---------------------------------------------------------------------------------------------------

请记住，如果在不限制分区键的情况下扫描分区表，则必须调用所有分区。

如果知道，大部分查询结果都在最近和小分区，只需在第一次查询中扫描 tme

select * from tab
where trans_dt >= DATE'2000-01-01' and <your filter>

并且仅当您无法获得行扫描大分区时

select * from tab
where trans_dt < DATE'2000-01-01' and <your filter>

如果假设查询主要引用最近的数据，则平均响应时间会好得多。

Answer 3

尽管没有排除特定分区的语法，但您可以构建一个管道表函数，该函数动态构建使用除一个分区之外的每个分区的查询。

表函数构建如下所示的查询。该函数使用数据字典视图USER_TAB_PARTITIONS获取分区名称以生成 SQL，使用动态 SQL 执行查询，然后将结果传送回调用方。

select * from my_table partition (P1) union all
select * from my_table partition (P2) union all
...
select * from my_table partition (P1000);

示例架构

CREATE TABLE my_table 
(
recordID NUMBER(16) NOT NULL,     --not primary
dateOfSale DATE NOT NULL,         --unknown
a NUMBER
)
partition by range (dateOfSale)
(
partition p0 values less than (date '2000-01-01'),
partition p1 values less than (date '2000-01-02'),
partition p2 values less than (date '2000-01-03')
);
insert into my_table
select 1,date '1999-12-31',1 from dual union all
select 2,date '2000-01-01',1 from dual union all
select 3,date '2000-01-02',1 from dual;
commit;

封装和功能

create or replace package my_table_pkg is
type my_table_nt is table of my_table%rowtype;
function get_everything_but_p0 return my_table_nt pipelined;
end;
/
create or replace package body my_table_pkg is
function get_everything_but_p0 return my_table_nt pipelined is
v_sql clob;
v_results my_table_nt;
v_cursor sys_refcursor;
begin
--Build SQL that referneces all partitions.
for partitions in
(
select partition_name
from user_tab_partitions
where table_name = 'MY_TABLE'
and partition_name <> 'P0'
) loop
v_sql := v_sql || chr(10) || 'union all select * from my_table ' ||
'partition (' || partitions.partition_name || ')';
end loop;
v_sql := substr(v_sql, 12);
--Print the query for debugging:
dbms_output.put_line(v_sql);
--Gather the results in batches and pipe them out.
open v_cursor for v_sql;
loop
fetch v_cursor bulk collect into v_results limit 100;
exit when v_results.count = 0;
for i in 1 .. v_results.count loop
pipe row (v_results(i));
end loop;
end loop;
close v_cursor;
end;
end;
/

该软件包使用 12c 的功能来定义封装规范中的类型。如果在 11g 或更低版本中构建此内容，则需要改为创建 SQL 类型。此包仅适用于一个表，但如有必要，可以通过创建适用于任何表的函数(使用 Oracle 数据盒或 18c 的多态表函数)。

示例查询

SQL> select * from table(my_table_pkg.get_everything_but_p0);
RECORDID DATEOFSAL          A
---------- --------- ----------
2 01-JAN-00          1
3 02-JAN-00          1

性能

此功能的性能应该与您正在寻找的智能解决方案一样好。会有开销，因为行是通过PL/SQL传递的。但最重要的是，该函数构建了一个 SQL 语句，该语句用于分区修剪掉大的 P0 分区。

此函数的一个可能问题是优化器内部没有可见性，并且无法创建良好的行基数估计值。如果将该函数用作另一个大型 SQL 语句的一部分，请注意优化程序将盲目猜测该函数返回 8168 行。这种错误的基数猜测可能会导致糟糕的执行计划。