CODEWITHSUNDEEP
oracle-databasedynamic-sqlxmltable
MAndrews
MAndrews

Reputation: 69

Oracle - Dynamic SQL / XMLTable Poor Performance

I have a series 'dynamic' SQL queries contained within a column (DYNAMIC_SQL), the 'dynamic' query itself is fairly basic;

e.g. SELECT ROWID AS RECORD_ID, COLUMN_1 AS STR_VALUE, '-Undefined-' AS STR_IDENTIFIER_VALUE, 'DS_1319' AS DS_TABLE_NAME FROM DS_1319

I'm then using a combination of XMLTABLE and GETXML to extract and display the results.

However, even though the query runs and functions as intended, I've noticed that the performance is very poor (NB: DS_1319 has a row count of; 13,939).

The query I'm using is as follows:

SELECT
  T1.*,
  T2.RECORD_ID,
  T2.STR_VALUE,
  T2.STR_IDENTIFIER_VALUE
FROM
  CP_RDN_IN_DYNAMIC_SQL_TMP T1,
  XMLTABLE('/ROWSET /ROW' 
           PASSING XMLTYPE(DBMS_XMLGEN.GETXML(T1.DYNAMIC_SQL)) 
           COLUMNS RECORD_ID VARCHAR2(255) PATH 'RECORD_ID',
                   STR_VALUE VARCHAR2(255) PATH 'STR_VALUE',
                   STR_IDENTIFIER_VALUE VARCHAR2(255) PATH 'STR_IDENTIFIER_VALUE',
                   DS_TABLE_NAME VARCHAR2(255) PATH 'DS_TABLE_NAME') T2
WHERE
  T1.DS_TABLE_NAME = T2.DS_TABLE_NAME

NB: the column DYNAMIC_SQL is defined as follows;

'SELECT ROWID AS RECORD_ID, '||
        T1.FIELD_NAME||' AS STR_VALUE, '||
        T1.IDENTIFIER_FIELD_NAME||' AS STR_IDENTIFIER_VALUE, '''||
        T1.DS_TABLE_NAME||''' AS DS_TABLE_NAME 
 FROM'||T1.DS_TABLE_NAME AS DYNAMIC_SQL

Below is a small extract of the data;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAA</RECORD_ID>
  <STR_VALUE>ORACLE</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAB</RECORD_ID>
  <STR_VALUE>Oracle</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAC</RECORD_ID>
  <STR_VALUE>Oracle 9i</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAD</RECORD_ID>
  <STR_VALUE>Oracle 11g</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAE</RECORD_ID>
  <STR_VALUE>Oracle CRM</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAF</RECORD_ID>
  <STR_VALUE>oracle 10g</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAG</RECORD_ID>
  <STR_VALUE>ORACLE, XE</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAH</RECORD_ID>
  <STR_VALUE>XE</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAI</RECORD_ID>
  <STR_VALUE>MS Windows</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAJ</RECORD_ID>
  <STR_VALUE>Microsoft Windows Vista</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAK</RECORD_ID>
  <STR_VALUE>Vista</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAL</RECORD_ID>
  <STR_VALUE>MSOFT Win Vista</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAM</RECORD_ID>
  <STR_VALUE>Microsoft Office</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAN</RECORD_ID>
  <STR_VALUE>MS SQL Server</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAO</RECORD_ID>
  <STR_VALUE>VISTA MS WINDOWS</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAP</RECORD_ID>
  <STR_VALUE>Windows vista</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAQ</RECORD_ID>
  <STR_VALUE>Microsoft Windows 2000</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
 <ROW>
  <RECORD_ID>AAAGU2AABAAAShJAAR</RECORD_ID>
  <STR_VALUE>Macromedia</STR_VALUE>
  <STR_IDENTIFIER_VALUE>-Undefined-</STR_IDENTIFIER_VALUE>
  <DS_TABLE_NAME>DS_1319</DS_TABLE_NAME>
 </ROW>
</ROWSET>

I'm fairly certain there is a more efficient approach to achieve the desired results, so if there are any big brains out there with some advice / suggestions, it would be very much appreciated (I'm open to approach; Pure SQL, PL/SQL, etc).

Many thanks in advance.

Upvotes: 0

Views: 742

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59486

I don't see any reason why you transform the result into XML, just make dynamic SELECT directly.

   cur SYS_REFCURSOR;
BEGIN
DYNAMIC_SQL := 'SELECT ROWID AS RECORD_ID, '||
        T1.FIELD_NAME||' AS STR_VALUE, '||
        T1.IDENTIFIER_FIELD_NAME||' AS STR_IDENTIFIER_VALUE, '''||
        T1.DS_TABLE_NAME||''' AS DS_TABLE_NAME 
 FROM'||T1.DS_TABLE_NAME;

OPEN CURSOR cur FOR DYNAMIC_SQL;
FETCH ...

Upvotes: 1

Related Questions