使用IN子句中的子查询，Oracle性能降低

I have query like this:

我有这样的查询:

SELECT
  xmlelement("objects",
    xmlagg(
        xmlelement("object",
          xmlelement("accountId", ACCOUNTS.accountId),
          xmlelement("address", ACCOUNTS.ADDRESS)
      )
    )
    INTO obj_info_xml
    FROM 
      ACCOUNTS
    WHERE account_code IN (SELECT EXTRACTVALUE(VALUE(accountCodes), '/accountCode/text()') as accountCode
                 FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//accountCodes/accountCode'))) accountCodes);

When I hardcode values inside IN clause then query executes fast, but when I use subquery to select from xml then I can't get results because it executes very slow. Do you have any suggestions?

当我在IN子句中硬编码值时,查询执行速度很快,但是当我使用子查询从xml中选择时,我无法获得结果,因为它执行速度很慢。你有什么建议吗?

1 个解决方案

#1

Assuming that x is a PL/SQL variable containing the list of your account codes, something like this ought to speed things up a little bit:

假设x是包含您的帐户代码列表的PL / SQL变量,这样的事情应该加快一点:

select xmlelement("objects",
                  xmlagg(xmlelement("object",
                                    xmlelement("accountId", accounts.accountid),
                                    xmlelement("address", accounts.address))
                         )
                 ) account_xml
into   obj_info_xml
from   accounts a
       inner join xmltable('//accountCodes/accountCode'
                           passing x
                           columns account_code varchar2(30) path '.') xdata  -- amend datatype as appropriate
         on a.account_code = xdata.account_code;

N.B. untested, due to lack of sample data.

注:未经测试,由于缺乏样本数据。

Ok, what does the following give you?

好的,以下是什么给你的?

select xmlelement("objects",
                  xmlagg(xmlelement("object",
                                    xmlelement("accountId", accounts.accountid),
                                    xmlelement("address", accounts.address))
                         )
                 ) account_xml
into   obj_info_xml
from   accounts a
where  a.account_code in (select /*+ dynamic_sampling(xdata 10) */ 
                                 account_code
                          from   xmltable('//accountCodes/accountCode'
                                          passing x
                                          columns account_code varchar2(30) path '.') xdata); -- amend datatype as appropriate

Another suggestion would be to replace /*+ dynamic_sampling(xdata 10) */ with /* cardinality(xdata <roughly expected number of rows>) */ (overwriting with the relevant number, of course).

另一个建议是将/ * + dynamic_sampling(xdata 10)* /替换为/ * cardinality(xdata <大致预期的行数> )* /(当然用相关数字覆盖)。

Also, can you edit your question to provide the execution plans for the query with and without the hardcoded variables, please?

另外,你可以编辑你的问题,为有或没有硬编码变量的查询提供执行计划吗?

#1