Oracle中无公共字段的两张表关联查询方案问询

当然可以搞定这个重复执行的麻烦！你完全可以把这两个查询合并成一个，一次性拿到所有符合要求的表及其大小，不用再手动逐个处理几千个会话编号。下面给你两种实用的方案：

方案1：用正则匹配模式批量匹配会话编号 #

这个方案先把所有符合条件的会话编号拼接成一个正则表达式的备选字符串，然后一次性匹配所有表名，效率比较高：

WITH session_nos AS (
    -- 把所有早于2020-11-01的会话编号拼接成用|分隔的正则模式
    SELECT LISTAGG(SESSION_NO, '|') WITHIN GROUP (ORDER BY SESSION_NO) AS session_pattern
    FROM APP_SESSION
    WHERE SESSION_BEG < TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
)
SELECT 
    us.SEGMENT_NAME, 
    ROUND(us.BYTES/1024/1024, 2) AS "SIZE in MB" -- 保留两位小数更易读
FROM USER_SEGMENTS us
CROSS JOIN session_nos sn
WHERE 
    us.SEGMENT_TYPE = 'TABLE'
    -- 用正则匹配表名是否包含任意一个会话编号
    AND REGEXP_LIKE(us.SEGMENT_NAME, sn.session_pattern)
ORDER BY "SIZE in MB" DESC;

注意事项： #

• 如果你的会话编号数量特别多（比如超过LISTAGG默认的字符串长度限制），可以改用XMLAGG来生成更长的匹配字符串，替换CTE部分：

  SELECT RTRIM(XMLAGG(XMLELEMENT(e, SESSION_NO, '|')).EXTRACT('//text()').GETCLOBVAL(), '|') AS session_pattern
  FROM APP_SESSION
  WHERE SESSION_BEG < TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  

方案2：用EXISTS子查询逐个校验表名 #

如果担心正则匹配的性能或者字符串长度问题，可以用EXISTS子查询，逻辑更直观：

SELECT 
    us.SEGMENT_NAME, 
    ROUND(us.BYTES/1024/1024, 2) AS "SIZE in MB"
FROM USER_SEGMENTS us
WHERE 
    us.SEGMENT_TYPE = 'TABLE'
    -- 检查当前表名是否包含任意一个早于指定日期的会话编号
    AND EXISTS (
        SELECT 1
        FROM APP_SESSION asess
        WHERE 
            asess.SESSION_BEG < TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            AND us.SEGMENT_NAME LIKE '%' || asess.SESSION_NO || '%'
    )
ORDER BY "SIZE in MB" DESC;

优化建议： #

• 给APP_SESSION.SESSION_BEG字段加个索引，能大幅加快子查询的过滤速度；
• 如果SESSION_NO是数字类型，Oracle会自动转换为字符串，不用额外处理。

两种方案都能帮你一次性完成查询，不用再重复执行Query-2啦！

内容的提问来源于stack exchange，提问作者Aarie