这是Microsoft还是Oracle问题? [英] Is this a Microsoft or an Oracle Problem?

问题描述

在我的原始帖子中:解决方案

如果能够从Power BI更改SQL调用，则会发现以下查询产生相同的输出.它在与Power BI一起安装了Oracle的笔记本电脑上运行不到一秒钟.相比之下，原始查询需要近30分钟的时间. Microsoft，您可能想看看您的产品如何通过DSN连接到较新的Oracle产品:

SELECT
    *
FROM
    (
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            o1.object_type table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type = 'TABLE' and
            o1.owner not in ('SYS','SYSTEM')
        UNION
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            o1.object_type table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type = 'VIEW' and
            o1.owner not in ('SYS','SYSTEM')
    ) tables
ORDER BY
    4,
    2,
    3

In my original thread here: How can I fix ORA: 01013 (user requested cancel...) when trying to link Oracle tables in MS Access? I describe an issue attempting to link Oracle tables into a Microsoft Access (office 365) database. The process timed out after entry of a UID and password.

As I researched the problem, I was able to determine that the ODBC drivers and DSN work for ADO, Toad, and Microsoft Power BI (when using a specific query against an Oracle table). I was never able to log entries in the Oracle V$SQL table from either Access or Excel to further troubleshoot the problem.

However, tonight, I was able to get Power BI to recreate the same behavior by attempting to connect through the DSN and browse the tables in Oracle. Oracle captured the SQL call and the result is this gem:

SELECT
    *
FROM
    (
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            DECODE(o1.owner, 'SYS', DECODE(o1.object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM'
            , DECODE(o1.object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', o1.object_type), o1.object_type) table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type IN ('TABLE',
                'VIEW'
            )
        UNION
        SELECT
            NULL table_qualifier,
            s.owner          table_owner,
            s.synonym_name   table_name,
            'SYNONYM' table_type, null remarks
        FROM
            all_objects    o3,
            all_synonyms   s
        WHERE
            o3.object_type IN (
                'TABLE',
                'VIEW'
            )
            AND s.table_owner = o3.owner
            AND s.table_name = o3.object_name
        UNION
        SELECT
            NULL table_qualifier,
            s1.owner          table_owner,
            s1.synonym_name   table_name,
            'SYNONYM' table_type,
            NULL remarks
        FROM
            all_synonyms s1
        WHERE
            s1.db_link IS NOT NULL
    ) tables
WHERE
    1 = 1
    AND ( table_type = 'TABLE'
          OR table_type = 'VIEW' )
ORDER BY
    4,
    2,
    3

I don't know where to start with this query. The second and third subqueries in the union statement are filtered out by the final where clause, so they are useless. The first subquery is attempting to retrieve a list of tables/schemas from all_objects. If I restrict that chunk of SQL to the first 100,000 rows and run it in SQLPLUS, the runtime is over 20 minutes in Oracle 18c (XE). I presume that is because that object is constantly updating, even as the query is running.

The details of the MS ODBC specification to which Oracle certifies are way over my head, so I don't know whether to continue pursuing tickets with Microsoft, or whether to turn my attention to Oracle as the culprit for the problem.

Any and all advice appreciated. I really need to know which party is responsible for the SQL above.

Thanks!

解决方案

If I were able to change the SQL call from Power BI, I find that the following query produces identical output. It runs in less than one second on the laptop where Oracle is installed along with Power BI. That contrasts with almost 30 minutes for the original query. Microsoft, you may want to take a look at how your products connect to the newer Oracle products via DSN:

SELECT
    *
FROM
    (
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            o1.object_type table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type = 'TABLE' and
            o1.owner not in ('SYS','SYSTEM')
        UNION
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            o1.object_type table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type = 'VIEW' and
            o1.owner not in ('SYS','SYSTEM')
    ) tables
ORDER BY
    4,
    2,
    3

这篇关于这是Microsoft还是Oracle问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！