Tags:

SQL Probleme und das Oracle SQL Forum

Von Tobias Arnhold 10.22.2015
Viele meiner Kollegen (um die 90 %) nutzen bei schwierigen SQL Problemen nicht das SQL Forum, sondern versuchen sich lieber an inperformanten PL/SQL Code.

Die Gründe sind leicht erklärt: Zeitmangel!

Der kurzfristige Vorteil kann im Nachhinein aber sehr teuer / zeitaufwendig werden. Nämlich dann, wenn die schnelle Lösung mit erhöhten Datenmengen versagt und statt ursprünglich wenigen Sekunden plötzlich Minuten an Zeit verbraucht.

Hierbei möchte ich auf die goldene Regel von Tom Kyte referenzieren:
     1.) Nutze solange SQL wie es geht
     2.) Wenn SQL nicht mehr reicht, dann nehme PL/SQL
     3.) Wenn PL/SQL nicht mehr reicht, dann nehme JAVA oder irgendetwas Anderes was weiterhilft

Meine Anmerkung wäre die folgende zu Punkt 1.)
     1.)  Nutze solange SQL wie es geht und befrage bei Problemen das Oracle SQL Forum

Wie aber stelle ich eine Frage richtig?
Um auch richtige Hilfe zu erhalten, muss eine entsprechende Vorarbeit Ihrerseits geleistet werden.
Das heißt:
     1.) Beschreiben Sie das Problem und fokussieren Sie sich dabei auf das Kernproblem
     2.) Hinterlegen Sie Quelldaten und ihre gescheiterten SQL Versuche
     3.) Beschreiben Sie das Zielszenario (Ergebnis in Tabellenform)
Sie sollten mit 30 Minuten Vorbereitungszeit rechnen (das erste Mal wird etwas länger dauern).
Info: Achten Sie auch auf den Inhalt der Quelldaten.  :)

Sie können sich dabei an meinen Beispielen inspirieren lassen:
     Beispiel 1: Generate some kind of cartesian list
     Beispiel 2: wi (dd.mm.yyyy-dd.mm.yyyy)
     Beispiel 3: Check crossing time periods between rows

Der aufwendigste Teil bleibt immer wieder die Aufbereitung der Quell- und Zieldaten. Ich bin zwar kein Fan der WITH-Klausel aber bei der Hilfe im Forum wird Sie von den Entwicklern immer wieder verwendet. Nun wäre es die einfachste Vorgehensweise die Quelldaten immer mit Hilfe der WITH-Klausel aufbereitet zur Verfügung zu stellen. Denn dann könnten die Entwickler deren Hilfe ich in Anspruch nehme, viel schneller reagieren. Wobei auch so die Reaktionszeit bei durchschnittlich 10 Minuten liegt (siehe Beispiele).

Deshalb habe ich eine Funktionen gebaut die auf Basis einer Tabelle oder View die Quelldaten als WITH-Klausel aufbereitet.
create or replace function get_with_clause(p_table_name in varchar2, p_include_with_clause number default 1, p_rows in number default 10) return clob as
/* Dynamische WITH-Klausel mit SELECT FROM DUAL auf Basis einer vorhandenen Tabelle generieren
   Übergabeparameter: 
     p_table_name = Tabellename
     p_include_with_clause = 1 oder 0, wenn 1 dann mit WITH-Klausel ansonsten nur SELECT FROM DUAL
     p_rows  = Anzahl Zeilen die zurückgegeben werden sollen, wenn NULL, dann alle
*/
 l_sql1 varchar2(4000);
 l_sql2 clob;
begin 
  select 'SELECT ' || chr(13) 
    || 
       case 
        when p_include_with_clause = 1 then
         '''WITH ' || max(table_name) || ' AS ('' ||' || chr(13) 
        else 
         null
       end
    || 'REGEXP_REPLACE(DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E, ' || chr(13) 
       || 'CHR(13) || ''SELECT '' || CHR(13) || '
       || replace(listagg(
                case 
                  when data_type = 'NUMBER' 
                    then 'nvl(trim(to_char('||column_name||')),''NULL'') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'VARCHAR2' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||'||column_name||'||'''''''') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'DATE' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||to_char('||column_name||',''dd.mm.yyyy hh24:mi'')||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
                  else 
                    null
                end
                , ' '
          ) within group (order by rownum) || 'ZZ',',''|| CHR(13) || ZZ',' '' ||')
       || chr(13) 
       || '''FROM DUAL UNION ALL '''
       || chr(13) 
    || ')).EXTRACT(''//text()'').GETCLOBVAL(),1)||''XX'','' UNION ALL XX'','''')'
    ||
       case 
        when p_include_with_clause = 1 then
          '|| CHR(13) || '') ' || 'SELECT t1.* FROM ' || max(table_name) || ' t1 ORDER BY 1'''
        else 
          null
       end
    || chr(13) 
    || 'FROM ' 
    || max(table_name)
    || chr(13) 
    || case when p_rows is null then null else 'WHERE ROWNUM BETWEEN 1 AND ' || trim(to_char(p_rows)) end
  into l_sql1
  from user_tab_columns
  where table_name = p_table_name
  and data_type in ('NUMBER','VARCHAR2','DATE');
 
  execute immediate (l_sql1) into l_sql2;
 
  return l_sql2;
end get_with_clause;
Innerhalb der Funktion werden die Spalten der übergebenen Tabelle ausgelesen. Daraus generiere ich dann ein dynamisches SQL das mir die "select * from dual" - je Zeile generiert. Das Ergebnis ist ein CLOB mit einem ausführbaren SQL Code.

Unter 12c sollte es auch ohne Funktionsaufruf abbildbar sein (Grundlagen sie Blogpost von Carsten), ich konnte es aber wegen fehlen einer 12c Umgebung noch nicht abschließend testen. :(
with function get_with_clause(p_table_name in varchar2, p_include_with_clause number default 1, p_rows in number default 10) return clob as
 L_SQL1 VARCHAR2(4000);
 L_SQL2 CLOB;
BEGIN 
  SELECT 'SELECT ' || CHR(13) 
    || 
       case 
        when p_include_with_clause = 1 then
         '''WITH ' || MAX(table_name) || ' AS ('' ||' || CHR(13) 
        else 
         null
       end
    || 'REGEXP_REPLACE(DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E, ' || CHR(13) 
       || 'CHR(13) || ''SELECT '' || CHR(13) || '
       || replace(listagg(
                case 
                  when data_type = 'NUMBER' 
                    then 'nvl(trim(to_char('||column_name||')),''NULL'') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'VARCHAR2' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||'||column_name||'||'''''''') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'DATE' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||to_char('||column_name||',''dd.mm.yyyy hh24:mi'')||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
                  else 
                    null
                end
                , ' '
          ) WITHIN GROUP (ORDER BY ROWNUM) || 'ZZ',',''|| CHR(13) || ZZ',' '' ||')
       || CHR(13) 
       || '''FROM DUAL UNION ALL '''
       || CHR(13) 
    || ')).EXTRACT(''//text()'').GETCLOBVAL(),1)||''XX'','' UNION ALL XX'','''')'
    ||
       case 
        when p_include_with_clause = 1 then
          '|| CHR(13) || '') ' || 'SELECT t1.* FROM ' || MAX(table_name) || ' t1 ORDER BY 1'''
        else 
          null
       end
    || CHR(13) 
    || 'FROM ' 
    || MAX(table_name)
    || CHR(13) 
    || case when p_rows is null then null else 'WHERE ROWNUM BETWEEN 1 AND ' || trim(to_char(p_rows)) end
  INTO L_SQL1
  FROM user_tab_columns
  WHERE table_name = p_table_name
  AND DATA_TYPE IN ('NUMBER','VARCHAR2','DATE');
 
  EXECUTE IMMEDIATE (L_SQL1) INTO L_SQL2;
 
  RETURN L_SQL2;
end get_with_clause;
select get_with_clause('DEPARTMENT') as result from dual
Ergebnis Beispiel für Tabelle DEPT:
SELECT GET_WITH_CLAUSE( P_TABLE_NAME => 'DEPT', P_INCLUDE_WITH_CLAUSE => 1, P_ROWS => 100 ) as RESULT
FROM DUAL;

WITH DEPT AS (
SELECT 
10  as DEPTNO,
'ACCOUNTING'  as DNAME,
'NEW YORK'  as LOC FROM DUAL UNION ALL 
SELECT 
20  as DEPTNO,
'RESEARCH'  as DNAME,
'DALLAS'  as LOC FROM DUAL UNION ALL 
SELECT 
30  as DEPTNO,
'SALES'  as DNAME,
'CHICAGO'  as LOC FROM DUAL UNION ALL 
SELECT 
40  as DEPTNO,
'OPERATIONS'  as DNAME,
'BOSTON'  as LOC FROM DUAL
) SELECT t1.* FROM DEPT t1 ORDER BY 1

Info: Die Lösung funktioniert nur mit den Datentypen: NUMBER, VARCHAR2, DATE. Die restlichen Datentypen werden derzeit ignoriert.

Post Tags:

No Comment to " SQL Probleme und das Oracle SQL Forum "