Oracle层级构建、根节点统计问题及PowerBI报表求助

需求说明 #

• 在Oracle中构建层级结构（对应原query1）
• 统计每个根节点及其下属子节点总数（对应原query2）
• 基于查询结果在PowerBI中制作报表

原查询语句 #

query1：构建层级结构 #

SELECT 
    PARENT AS "PARENT",
    CHILD AS "CHILD",
    LEVEL AS "LEVEL",
    PARENT_CLASS AS "PARENT_CLASS",
    CHILD_CLASS AS "CHILD_CLASS",
    CREATED_ON_RELCI AS "CREATED_ON_RELCI",
    UPDATED_ON_RELCI AS "UPDATED_ON_RELCI"
FROM (   
    SELECT DISTINCT
        CI1.NAME AS PARENT,
        CI2.NAME AS CHILD,
        CI1.SYS_CLASS_NAME AS PARENT_CLASS,
        CI2.SYS_CLASS_NAME AS CHILD_CLASS,
        RELCI.SYS_CREATED_ON AS CREATED_ON_RELCI,
        RELCI.SYS_UPDATED_ON AS UPDATED_ON_RELCI
    FROM  SN_REPORTING.REP_CMDB_REL_CI RELCI  
    LEFT JOIN SN_REPORTING.REP_CMDB_CI_SERVICE CIS
        ON RELCI.PARENT=CIS.SYS_ID
    LEFT JOIN SN_REPORTING.REP_CMDB_CI CI1
        ON RELCI.PARENT=CI1.SYS_ID
    LEFT JOIN SN_REPORTING.REP_CMDB_CI CI2
        ON RELCI.CHILD=CI2.SYS_ID
    WHERE RELCI.SYS_UPDATED_ON >= TO_DATE('01-01-18', 'MM-DD-YYYY')
)A
CONNECT BY NOCYCLE A.PARENT = PRIOR A.CHILD

query2：统计根节点及子节点总数 #

select ( count(*)-1 ) Child_Node_Count,
       root
from ( select connect_by_root(parent) root
       from  SN_REPORTING.SERVICE_LIFECYCLE_WITHOUT_HIERARCHY_FILTER_VW
       connect by nocycle parent = prior child
     )
group by root
/

遇到的问题 #

• 单独运行query1可正常获取层级结果，但写入存储过程时因"HIERARCHY"字段报错；
• 移除该字段后编写存储过程统计根节点及子节点数，查询长时间运行后抛出表空间错误；
• 数据导入PowerBI时持续加载无法完成，且因表空间错误无法查看数据库总数据量。

问题解决与查询合并方案 #

1. 存储过程"HIERARCHY"字段报错处理 #

原query1中并无HIERARCHY字段，报错大概率是存储过程定义时的字段/变量名冲突，或是代码拷贝时误引入了不存在的字段。解决步骤：

• 检查存储过程的参数、返回类型定义，确保没有使用与现有对象冲突的名称；
• 严格对齐query1的字段列表，不要额外添加未定义的字段。

2. 表空间错误与查询性能优化 #

表空间错误通常是临时表空间不足或结果集过大导致。以下是合并query1和query2的优化版查询，同时解决性能问题：

合并后的查询语句

该查询返回层级节点详细信息，同时附带对应根节点的子节点总数：

WITH hierarchy_data AS (
    SELECT 
        PARENT,
        CHILD,
        LEVEL AS "LEVEL",
        PARENT_CLASS,
        CHILD_CLASS,
        CREATED_ON_RELCI,
        UPDATED_ON_RELCI,
        CONNECT_BY_ROOT PARENT AS ROOT_NODE
    FROM (   
        SELECT DISTINCT
            CI1.NAME AS PARENT,
            CI2.NAME AS CHILD,
            CI1.SYS_CLASS_NAME AS PARENT_CLASS,
            CI2.SYS_CLASS_NAME AS CHILD_CLASS,
            RELCI.SYS_CREATED_ON AS CREATED_ON_RELCI,
            RELCI.SYS_UPDATED_ON AS UPDATED_ON_RELCI
        FROM  SN_REPORTING.REP_CMDB_REL_CI RELCI  
        LEFT JOIN SN_REPORTING.REP_CMDB_CI_SERVICE CIS
            ON RELCI.PARENT=CIS.SYS_ID
        LEFT JOIN SN_REPORTING.REP_CMDB_CI CI1
            ON RELCI.PARENT=CI1.SYS_ID
        LEFT JOIN SN_REPORTING.REP_CMDB_CI CI2
            ON RELCI.CHILD=CI2.SYS_ID
        WHERE RELCI.SYS_UPDATED_ON >= TO_DATE('01-01-18', 'MM-DD-YYYY')
    )A
    CONNECT BY NOCYCLE A.PARENT = PRIOR A.CHILD
),
root_child_counts AS (
    SELECT 
        ROOT_NODE,
        COUNT(*) - 1 AS CHILD_NODE_COUNT  -- 减1排除根节点自身
    FROM hierarchy_data
    GROUP BY ROOT_NODE
)
SELECT 
    hd.PARENT,
    hd.CHILD,
    hd."LEVEL",
    hd.PARENT_CLASS,
    hd.CHILD_CLASS,
    hd.CREATED_ON_RELCI,
    hd.UPDATED_ON_RELCI,
    hd.ROOT_NODE,
    rcc.CHILD_NODE_COUNT
FROM hierarchy_data hd
JOIN root_child_counts rcc
    ON hd.ROOT_NODE = rcc.ROOT_NODE;

优化建议

• 给SN_REPORTING.REP_CMDB_REL_CI.SYS_UPDATED_ON创建索引，加速数据过滤；
• 联系DBA检查并扩展临时表空间，避免查询过程中空间耗尽；
• 存储过程中添加分页逻辑（如WHERE ROWNUM <= 1000或FETCH NEXT 1000 ROWS ONLY），减少单批次返回数据量；
• 确认SERVICE_LIFECYCLE_WITHOUT_HIERARCHY_FILTER_VW与query1数据源一致性，避免重复扫描不同表。

3. PowerBI数据导入问题解决 #

• 先验证合并后的查询在Oracle中能快速返回结果，若查询耗时过长，PowerBI会持续加载；
• 优先使用PowerBI的DirectQuery模式，直接连接数据库实时查询，无需导入全部数据；
• 若必须使用导入模式，在Oracle中创建物化视图预计算层级和统计结果，PowerBI读取物化视图可大幅提升加载速度；
• 检查数据库与PowerBI机器的网络连接，确保低延迟、高稳定性。

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