在oracle select语句中使用Union all with loop

Question

我在一个oracle数据库中工作，有20个表具有相同的结构，但按年划分。所以，它从ft_expenses_2002开始，一直到ft_expenses_2021(我写这篇文章的那一年)。在做一些数学之前，我需要把所有这些表的列放在一起，我的第一个方法是使用UNIAN all语句。它工作了，但我想知道是否有可能做一些更优雅的事情，比如使用FOR循环。这样不仅可以使查询更加优雅，而且可以避免将来的维护，因为每年都会有一个带有"_new_year"的新表。后缀将被创建。

Answer 1

为所有的表创建一个视图:

CREATE VIEW ft_expenses AS
SELECT * FROM ft_expenses_2002
UNION ALL SELECT * FROM ft_expenses_2003
UNION ALL SELECT * FROM ft_expenses_2004
UNION ALL SELECT * FROM ft_expenses_2005
UNION ALL SELECT * FROM ft_expenses_2006
UNION ALL SELECT * FROM ft_expenses_2007
UNION ALL SELECT * FROM ft_expenses_2008
UNION ALL SELECT * FROM ft_expenses_2009
UNION ALL SELECT * FROM ft_expenses_2010
UNION ALL SELECT * FROM ft_expenses_2011
-- ...
UNION ALL SELECT * FROM ft_expenses_2021

然后使用视图执行查询。

明年当您添加2022表时，然后重新创建视图，并将额外的表添加到视图中。

---

或者，从原始表创建一个表，以便所有内容都在一个表中，您可以直接查询:

CREATE TABLE ft_expenses (year, col1, col2, col3) AS
SELECT 2002, col1, col2, col3 FROM ft_expenses_2002
UNION ALL SELECT 2003, col1, col2, col3 FROM ft_expenses_2003
UNION ALL SELECT 2004, col1, col2, col3 FROM ft_expenses_2004
UNION ALL SELECT 2005, col1, col2, col3 FROM ft_expenses_2005
UNION ALL SELECT 2006, col1, col2, col3 FROM ft_expenses_2006
UNION ALL SELECT 2007, col1, col2, col3 FROM ft_expenses_2007
UNION ALL SELECT 2008, col1, col2, col3 FROM ft_expenses_2008
-- ...
UNION ALL SELECT 2021, col1, col2, col3 FROM ft_expenses_2021

然后删除单个表(确保首先备份了所有内容)并创建视图，如果您仍然需要以原始名称访问它们:

CREATE VIEW ft_expenses_2002 (col1, col2, col3) AS
SELECT col1, col2, col3 FROM ft_expenses WHERE year = 2002;
CREATE VIEW ft_expenses_2003 (col1, col2, col3) AS
SELECT col1, col2, col3 FROM ft_expenses WHERE year = 2003;
-- ...
CREATE VIEW ft_expenses_2021 (col1, col2, col3) AS
SELECT col1, col2, col3 FROM ft_expenses WHERE year = 2021;

Answer 2

我在这里找到了一个非常好的和简短的方法来解决我的问题，它是:

SELECT
GROUP_CONCAT(
CONCAT(
'SELECT * FROM `',
TABLE_NAME,
'`') SEPARATOR ' UNION ALL ')
FROM
`INFORMATION_SCHEMA`.`TABLES` 
WHERE
`TABLE_NAME` LIKE 'ft_expenses_%'
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

Answer 3

这取决于你对"优雅"的定义。

当然可以使用动态SQL来查找名称符合特定模式的每个表。我的猜测是，最简单的事情是创建一个执行union all的视图，针对该视图编写处理代码，然后有一些动态SQL，可以根据存在的表重建视图。然后，您可以在创建新表时运行该过程(如果您可以挂钩到该进程)，或者基于DDL触发器或根据新表突然出现的可能性将其安排在每年/月/天的清晨运行。

create or replace procedure build_view
as
l_sql_stmt varchar2(32000);
type typ_table_names is table of varchar2(256);
l_tables typ_table_names;
begin
l_sql_stmt := 'create or replace view my_view as ';
select table_name
bulk collect into l_tables
from user_tables
where table_name like 'ft_expenses%';
for i in 1 .. l_tables.count
loop
l_sql_stmt := l_sql_stmt || 
' select * from ' || l_tables(i);
if( i != 1_tables.count )
then
l_sql_stmt := l_sql_stmt ||
' union all ';
end if;
end loop;
dbms_output.put_line( l_sql_stmt );
execute immediate l_sql_stmt;
end;

Answer 4

表的物理设置是Oracle 6(1980年代后期)的最新技术。

有两种升级的可能性。DIY联合all视图在其他答案中提出，或者简单地遵循Oracle自上述版本以来实现的开发。

注意，我不推荐要遵循的建议，将所有数据放在一个表中-为什么?在覆盖所有数据的查询中，您不会看到任何差异，但是在一年或几年的查询中，您会发现性能显著下降。

自从发布6以来，Oracle在这个主题上做了什么?

在Oracle 7(1990)中，引入了一个分区视图，这与UNION ALL视图的提议类似。

Oracle 8partitioning这个概念在以后的每个版本中被引入和改进。

所以如果你想利用当前Oracle的特性，分区应该应用:

• 管理可接受大小的数据
• 提供了访问
的灵活性

在Oracle 19c

中如何迁移

我假设您的表包含一个列trans_dt，其中DATE的年份与表year相同。

从最老的表开始，将其更改为分区表

alter table ft_expenses_2002 modify
partition by range(trans_dt) interval(NUMTOYMINTERVAL(1,'YEAR'))
( partition p_init values less than (DATE'2002-01-01')
) online

重命名去掉年份的表

rename ft_expenses_2002 to ft_expenses;

现在中的表对进行了分区，并包含两个分区，初始的分区和2002年的分区。

select PARTITION_NAME from user_tab_partitions where table_name = 'FT_EXPENSES' order by PARTITION_POSITION;
PARTITION_NAME                                                                                                                  
---------------- 
P_INIT
SYS_P2340

接下来的每一年执行以下步骤

Add a new partition

alter table ft_expenses 
exchange partition for( DATE'2003-01-01' ) with table ft_expenses_2003

请注意，您使用for语法来定位分区，因此不需要知道分区名称。

此外，最新版本可以在exchange语句中创建分区，因此不再需要lock table。

最后指出

您可以在重组中包含索引。

一如既往地在启动前备份所有表。

仔细测试以检查可能的限制。