Kategorie: Work

Auch nett…

wer weiss wofuer man es mal gebrauchen kann.

Gegeben sei folgende Tabelle:

SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;

DEPTNO ENAME
------ ----------
    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD

14 rows selected.

Wir moechten jetzt die Employees zu einem department aber in je einer Zeile haben. Man nehme: wm.concat aus wmsys

SELECT deptno,wmsys.wm_concat(ename) as concatenated FROM dept group by deptno;

    DEPTNO CONCATENATED
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
...

Ist doch schick, oder ?

[Update 2017-04-25]: Habe das heute mal wieder gebraucht. wm_concat ist wohl nicht mehr vorhanden. Stattdessen gibt es was offizielles. Funktioniert fast genau so: listagg. Hier ein adaptiertes Beispiel:

SELECT deptno,wmsys.listagg(ename,', ') within group (order by ename) as concatenated FROM dept group by deptno;

Man kann sogar den Delimiter mit angeben (hier ein ,) und sortieren…

Schon mal versucht einen Aggregations-SQL nach anderen Werten als den Format-Strings die mit to_date, to_char benutzt werden duerfen zu bauen ?

Ist garnicht so simpel. Ein

select count(*),attribut,to_char(creationdate,'DD.MM.YYYY')
 from tabelle
 group by attribut,trunc(creationdate,'DD.MM.YYYY')

ist noch fuer jedermann verstaendlich. Auch wenn man nach Stunden gruppieren will, geht das noch schmerzfrei (‚HH‘). Was aber wenn man auf 15Minuten Ebene Aggregieren soll ? Ohne Kunstgriff geht es nicht. Hier der to_date String:

 to_date(to_char(sysdate,'DD.MM.YYYY') ||
         ' ' ||
         trunc(to_number(to_char(sysdate,'SSSSS'))/900)*900,
         'DD.MM.YYYY SSSSS')

Was passiert hier ?

Zunächst wird die aktuelle Zeit (im Select ist es natuerlich das zu gruppierende Feld) in Sekunden nach Mitternacht umgewandelt. Nun dividieren wir das Ergebniss durch 900 (15 Minuten = 900 Sekunden) und schnibbeln die Nachkommastellen ab. Anschliessend das ganze wieder mit 900 Multiplizieren, und wir haben jede Erdenkliche Uhrzeit auf die letzte Viertelstunde (ab-)gerundet. (Schlaue Menschen koennten jetzt sagen, Mensch Joerch, nimm doch DIV, MOD gibts unter Oracle schliesslich auch -> DIV gibts leider NICHT). So, jetzt packen wir noch das Datum wieder davor (ist ja durch die Umwandlung in Sek. nach Mitternacht verloren gegangen) und konvertieren das ganze wieder in ein Datum. Fertig ist der Viertelstundenaggregator 🙂 Bezogen auf das obige Beispiel lautet unserer (zugegebenermassen unlesbarerer Spruch) wie folgt:

select count(*),attribut,
 to_date(to_char(creationdate,'DD.MM.YYYY') ||
         ' ' ||
         trunc(to_number(to_char(creationdate,'SSSSS'))/900)*900,
         'DD.MM.YYYY SSSSS')
 from tabelle
 group by attribut,
 to_date(to_char(creationdate,'DD.MM.YYYY') ||
         ' ' ||
         trunc(to_number(to_char(creationdate,'SSSSS'))/900)*900,
         'DD.MM.YYYY SSSSS')

LOBs unter Oracle sind, wie schon an einigen Stellen im Blog hier erwaehnt, wirklich eine suboptimale Storage-Loesung unter Oracle. Vor allem, wenn eine solche Tabelle auch noch alle x Monate bereinigt werden soll. Nach dem entsprechenden DELETE belegt das LOBSEGMENT naemlich immer noch gut Platz auf dem Tablespace. Was bei „normalen“ Tabellen ueber ein „ALTER TABLE ENABLE ROW MOVEMENT“ mit anschliessendem „ALTER TABLE SHRINK SPACE“ geht, ist bei LOBSEGMENTEN nicht ganz so simpel. Und weil ich die beiden Sprueche immer wieder verwechsele, schreibe ich Sie jetzt hier auf:

Gegeben sei ein Table „DATASTORE“ mit einem LOB der „FILEDATA“ heisst.

Nur Shrinken (Nicht Highwatermark zuruecksetzen, also auch nicht Table „locken“):

SQL> alter table datastore modify lob (filedata) (shrink space compact);

Im Anschluss Highwatermark zuruecksetzen / Bzw. direkt vollstaendig shrinken (VORSICHT: Table wird gelockt):

SQL>alter table datastore modify lob (filedata) (shrink space);

PS: Das ganze laesst sich noch mit einem CASCADE erweitern, was dann bedeutet dass auch unterliegende Objekte mit geshrinkt werden (Indizes, etc.)