比Oracle Database Union更快的选择

本文介绍了比Oracle Database Union更快的选择的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表，该表具有多个存储文本值的列.例如:

I have a table that has multiple columns which store a text value. For example:

ID    FATHER_NAME    MOTHER_NAME
--------------------------------
1     Henry          Sarah
2     Martin         Rebecca
3     Martin         Nancy

我想获取表中的所有名称.我知道我可以做一个工会来做到这一点:

I want to get all of the names in the table. I know I can do a union to do this:

(SELECT FATHER_NAME FROM MY_TABLE)
UNION
(SELECT MOTHER_NAME FROM MY_TABLE)

但是，在我的实际表中，我需要合并15列，而查询显然要花一点时间(大约12秒).而且我仍然需要对这些名称进行联接，等等.进行工会还有其他选择吗?

However, in my real table there are 15 columns I need to union and the query is obviously taking awhile (approximately 12 seconds). And I still need to do joins on these names, etc. Is there any other alternative to doing unions?

仅供参考:我正在使用Oracle.

FYI: I am using Oracle.

推荐答案

如果使用的是Oracle 11g，则可以使用UNPIVOT函数:

If you are using Oracle 11g, you can use the UNPIVOT function:

select id, value, col
from yourtable
unpivot
(
  value for col in (FATHER_NAME, MOTHER_NAME) -- other columns will go here
) u;

请参见带有演示的SQL小提琴

或者您可以使用UNION ALL代替UNION，不同之处在于您将不会获得DISTINCT值:

Or you can use UNION ALL instead of UNION the difference is you will not get DISTINCT values:

select id, FATHER_NAME value, 'FATHER_NAME' col
from yourtable
union all
select id, MOTHER_NAME value, 'MOTHER_NAME' col
from yourtable

请参见带有演示的SQL小提琴

UNION可能会由于尝试获取DISTINCT值而变慢.

The UNION might be slower due to it attempting to get the DISTINCT values.

这篇关于比Oracle Database Union更快的选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！