检测Oracle表中所有行都具有相同值的列

Question

每天，请求变得越来越奇怪。

我被要求编写一个查询来检测表中哪些列在所有行中都包含相同的值。我说：“这需要通过程序完成，这样我们就可以在表的一次遍历中完成而不是N次遍历。”但是我的建议被否决了。

长话短说，我有一个非常简单的查询示例来说明问题。它对测试集进行了4次遍历。我正在寻找SQL巫术的想法，不涉及在每个列上添加索引，也不需编写程序，更不需要花费完整的人生时间来运行。

叹气，它需要能够在任何表上工作。

感谢您提前的建议。

WITH TEST_CASE AS
(
    SELECT 'X' A, 5 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 3 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 7 B, 'TUE' C, NULL D FROM DUAL 
),
KOUNTS AS 
(
    SELECT SQRT(COUNT(*)) S, 'Column A' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P, TEST_CASE Q
    WHERE P.A = Q.A OR (P.A IS NULL AND Q.A IS NULL)

    UNION ALL

    SELECT SQRT(COUNT(*)) S, 'Column B' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P, TEST_CASE Q
    WHERE P.B = Q.B OR (P.B IS NULL AND Q.B IS NULL)

    UNION ALL

    SELECT SQRT(COUNT(*)) S, 'Column C' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P, TEST_CASE Q
    WHERE P.C = Q.C OR (P.C IS NULL AND Q.C IS NULL)

    UNION ALL

    SELECT SQRT(COUNT(*)) S, 'Column D' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P, TEST_CASE Q
    WHERE P.D = Q.D OR (P.D IS NULL AND Q.D IS NULL)
)
SELECT COLUMNS_WITH_SINGLE_VALUES
FROM KOUNTS
WHERE S = (SELECT COUNT(*) FROM TEST_CASE)

- EvilTeach

---

您的结果是否有一定的灵活性？例如，您是否可以拥有一个包含4列的1行结果，这些列具有colA_indicator和colB_indicator的Y或N值？ - Justin Cave

是的。Justin，格式没有限制。 - EvilTeach

Answer 1

你的意思是类似于这样吗？

WITH 
TEST_CASE AS
(
    SELECT 'X' A, 5 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 3 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 7 B, 'TUE' C, NULL D FROM DUAL 
)
select case when min(A) = max(A) THEN 'A'
            when min(B) = max(B) THEN 'B'
            when min(C) = max(C) THEN 'C'
            when min(D) = max(D) THEN 'D'
            else 'No one'
       end 
from TEST_CASE

编辑这个有效:

WITH 
TEST_CASE AS
(
    SELECT 'X' A, 5 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 3 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 7 B, 'TUE' C, NULL D FROM DUAL 
)
select case when min(nvl(A,0)) = max(nvl(A,0)) THEN 'A ' end ||
       case when min(nvl(B,0)) = max(nvl(B,0)) THEN 'B ' end ||
       case when min(nvl(C,0)) = max(nvl(C,0)) THEN 'C ' end ||
       case when min(nvl(D,0)) = max(nvl(D,0)) THEN 'D ' end c

from TEST_CASE

额外福利：我还添加了检查空值的代码，因此现在的结果是：A和D

这里有给您的SQLFiddle演示。

- mucio

---

这就是为什么我喜欢这个网站的原因，它有新鲜的视角和侧向思维。 - EvilTeach

考虑包含“0”或NULL的列的情况。 - EvilTeach

如果你想要一个技巧的话，可以使用一些非常奇怪的值，比如-38932.343。 - mucio

是的，没错。我现在正在使用一个真实的表格和一个0/NULL列进行测试。 - EvilTeach

是的。这个可以运行，并且速度足够快。谢谢muncio。我会在大约2天左右标记为已接受。 - EvilTeach

让我们在聊天中继续这个讨论：http://chat.stackoverflow.com/rooms/40334/discussion-between-mucio-and-evilteach - mucio

Answer 2

优化器统计信息可以轻松识别具有多个不同值的列。在收集统计信息后，针对数据字典的简单查询将几乎立即返回结果。

仅当您使用 ESTIMATE_PERCENT = 100 时，结果才会在 10g 上准确。如果您使用 ESTIMATE_PERCENT = 100 或 AUTO_SAMPLE_SIZE，则在 11g+ 上结果将准确无误。

代码

create table test_case(a varchar2(1), b number, c varchar2(3),d number,e number);

--I added a new test case, E.  E has null and not-null values.
--This is a useful test because null and not-null values are counted separately.
insert into test_case
SELECT 'X' A, 5 B, 'FRI' C, NULL D, NULL E FROM DUAL UNION ALL
SELECT 'X' A, 3 B, 'FRI' C, NULL D, NULL E FROM DUAL UNION ALL
SELECT 'X' A, 7 B, 'TUE' C, NULL D, 1    E FROM DUAL;

--Gather stats with default settings, which uses AUTO_SAMPLE_SIZE.
--One advantage of this method is that you can quickly get information for many
--tables at one time.
begin
    dbms_stats.gather_schema_stats(user);
end;
/

--All columns with more than one distinct value.
--Note that nulls and not-nulls are counted differently.
--Not-nulls are counted distinctly, nulls are counted total.
select owner, table_name, column_name
from dba_tab_columns
where owner = user
    and num_distinct + least(num_nulls, 1) <= 1
order by column_name;

OWNER     TABLE_NAME   COLUMN_NAME
-------   ----------   -----------
JHELLER   TEST_CASE    A          
JHELLER   TEST_CASE    D          

性能

在11g中，这种方法可能与mucio的SQL语句一样快。 选项如cascade => false可以通过不分析索引来提高性能。

但是这种方法的好处在于它还产生有用的统计信息。 如果系统已经定期收集统计信息，则可能已经完成了大量工作。

AUTO_SAMPLE_SIZE算法的详细信息

AUTO_SAMPLE_SIZE在11g中完全改变了。 它不使用采样来估计不同值（NDV）的数量。 相反，它扫描整个表并使用基于哈希的去重算法。 这种算法不需要大量的内存或临时表空间。 读取整个表比对其进行排序要快得多。 Oracle Optimizer博客在此处对该算法进行了很好的描述。 要获取更多详细信息，请参见Amit Podder的此演示文稿。（如果您想验证我下一节中的细节，您将需要浏览该PDF。）

可能出现错误结果的情况

尽管新算法不使用简单的采样算法，但它仍然不能100％正确地计算不同值的数量。 很容易找到估计的不同值数量与实际数量不同的情况。 但是，如果不同值的数量明显不准确，那么它们如何在此解决方案中得到信任？

潜在的不准确性来自两个来源-哈希冲突和概要拆分。 概要拆分是不准确性的主要来源，但不适用于此处。 它仅在有13864个不同值时发生。 它永远不会将所有值排除在外，最终估计肯定比1大很多。

唯一真正的问题是出现2个具有哈希冲突的不同值的机会有多大。 使用64位哈希，机会可能低至18,446,744,073,709,551,616中的1次。 不幸的是，我不知道他们的哈希算法的细节，也不知道真正的概率。 我无法从一些简单的测试和以前的现实测试中产生任何冲突。（我的其中一个测试是使用大值，因为某些统计操作仅使用数据的前N个字节。）

现在还要考虑的是，这只会在表中的所有不同值都发生冲突时发生。 有仅两个值并碰巧发生冲突的表的机会可能远小于赢得彩票并同时遭受陨石袭击的机会。

- Jon Heller

---

我也想到了同样的答案，但是我对它的准确性不够自信。因为任何插入/更新/删除等操作都会影响统计数据的准确性。所以最好在查询之前收集统计数据。感谢你的解释，让我更有信心了。 - Srini V

Answer 3

如果您可以接受结果在单行上显示，那么这个操作应该只扫描一次；

WITH TEST_CASE AS
(
    SELECT 'X' A, 5 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 3 B, 'FRI' C, NULL D FROM DUAL UNION ALL
    SELECT 'X' A, 7 B, 'TUE' C, NULL D FROM DUAL
)
SELECT 
  CASE WHEN COUNT(DISTINCT A) + 
            COUNT(DISTINCT CASE WHEN A IS NULL THEN 1 END) = 1
       THEN 1 ELSE 0 END SAME_A,
  CASE WHEN COUNT(DISTINCT B) + 
            COUNT(DISTINCT CASE WHEN B IS NULL THEN 1 END) = 1
       THEN 1 ELSE 0 END SAME_B,
  CASE WHEN COUNT(DISTINCT C) + 
            COUNT(DISTINCT CASE WHEN C IS NULL THEN 1 END) = 1
       THEN 1 ELSE 0 END SAME_C,
  CASE WHEN COUNT(DISTINCT D) + 
            COUNT(DISTINCT CASE WHEN D IS NULL THEN 1 END) = 1
       THEN 1 ELSE 0 END SAME_D
FROM TEST_CASE

一个 SQLfiddle 用于测试。

- Joachim Isaksson

Answer 4

这将在单次扫描中完成。

WITH 
    TEST_CASE AS
    (
        SELECT 'X' A, 5 B, 'FRI' C, NULL D FROM DUAL UNION ALL
        SELECT 'X' A, 3 B, 'FRI' C, NULL D FROM DUAL UNION ALL
        SELECT 'X' A, 7 B, 'TUE' C, NULL D FROM DUAL 
    )
    select decode(count(distinct nvl(A,0)),1,'SINGLE','MULTP') COL_A,
           decode(count(distinct nvl(B,0)),1,'SINGLE','MULTP') COL_B,
           decode(count(distinct nvl(C,0)),1,'SINGLE','MULTP') COL_C,
           decode(count(distinct nvl(D,0)),1,'SINGLE','MULTP') COL_D
    from TEST_CASE

- rahul