sql tipps und tricks - doag deutsche oracle … tipps und tricks ......

SQL Tipps und Tricks

... inklusive 12.2 Features

Ulrike Schwinn

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Ulrike SchwinnE-mail: [email protected] Deutschland B.V. & Co KG

Announcement

• Oracle Database 12c Release 2 steht in Oracle Cloud zur Verfügung!

– Database Cloud Services

– Exadata Express Cloud Service

Copyright © 2016 Oracle and/or its affiliates. All rights reserved.

– Exadata Express Cloud Service (Entry Level)

https://docs.oracle.com/database/122/NEWFT/toc.htm

http://docs.oracle.com/database/122/DBLIC/Licensing-Information.htm#DBLIC-GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4

Noch kurz zu den Werkzeugen

• SQL*Plus: Release 12.2Standard, Erweiterungen in 12c R2 (History, Performance ,CSV Format)

• SQL Developer: aktuell 4.1.5 (early adopter 4.2)einfache Verbindung zu Cloud Services


einfache Verbindung zu Cloud Servicesbesonders gut geeignet für Tuning, Ausführungspläne, schnelle Objekt-Übersicht, SQL Generierung, Laden etc.

• SQLcl: aktuell 4.2.0 (November)einfache Cloud Verbindungen, Scripting, Vorteile von graphischemInterface, einfache Formatgenerierung etc

Kennen Sie http://livesql.oracle.com?


Agenda

• Applikationsanforderungen genügen ...

– Anzahl verschiedener Spalteneinträge berechnen

– In Applikationen blättern

– Wiederverwendung von Berechnungen, Spalten etc. in der gleichen Abfrage

– Umgebungsinformationen abfragen


– Umgebungsinformationen abfragen

– Case Insensitivität verwenden

– Codieren mit Fehler Handling

– JSON und die Oracle Datenbank

Anforderung „Unterschiedliche Einträge in den Spalten?“

• Spontan ... mit count distinct...

• Data Dictionary USER_TAB_COLUMNS und Spalte NUM_DISTINCT

SQL> select count(distinct(prod_id)) from sales;

COUNT(DISTINCT(PROD_ID))

------------------------

72


• Data Dictionary USER_TAB_COLUMNS und Spalte NUM_DISTINCT

SQL> select column_name, num_distinct

from user_tab_columns where table_name='SALES';

COLUMN_NAME NUM_DISTINCT

------------------------------ ------------

PROD_ID 72

CUST_ID 7059

TIME_ID 1460

APPROX_COUNT_DISTINCT

• Alle skalare Datentypen sind möglich.

SQL> select approx_count_distinct(prod_id)from sales;

APPROX_COUNT_DISTINCT(DISTINCT(PROD_ID))

----------------------------------------

72


72

Plan hash value: 2087281841

---------------------------------------------------------

| Id | Operation | Name |

---------------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT GROUP BY APPROX | |

...

Und in 12.2 ...

• Keine Code Anpassung mehr erforderlich – nur Sessionparameter einstellenSQL> alter session set APPROX_FOR_COUNT_DISTINCT=true;


• Weitere Approximate Query Processing Funktionen sind in 12.2 verfügbar

Anforderung „Top n Abfragen“

• Die 10 billigsten Produkte ... mit ROWNUM ...select prod_id, prod_list_price

from

(select *

from sh.products order by prod_list_price)

where ROWNUM <= 10;


• Und jetzt noch blättern (ab 5 die nächsten ...) - Tuning mit RESULT_CACHE

select prod_id, prod_list_price

from (select prod_id, prod_list_price, rownum AS rnum

from (select prod_id, prod_List_price

from products

order by prod_list_price)

where rownum <= 10)

where rnum >= 5;

• Neue Syntax[ OFFSET offset { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ]

• Die 10 billigsten Produkte ... mit ROWNUM ...

Row Limiting Klausel


• Die 10 billigsten Produkte ... mit ROWNUM ...

•Ab 5 ... die nächsten


from sh.products order by prod_list_price fetch first 10 rows only


from sh.products order by prod_list_price

offset 4 rows fetch next 6 rows only

Der Unterschied


Weniger Code durch Lateral Queries in 12c

• „Jede Subquery wird unabhängig ausgewertet und kann keine andereSpalte aus der FROM Klausel referenzieren. Was tun?”

SQL> select d.dname, e.ename from dept d,

(select e.ename

from emp e


from emp e

where e.deptno = d.deptno);

where e.deptno = d.deptno)

*

ERROR at line 5:

ORA-00904: "D"."DEPTNO": invalid identifier

Lateral Abfragen

• “A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.”

– Subqueries in FROM können


– Subqueries in FROM könnennun mit dem LATERAL Schlüsselwort eingeleitetwerden. Dies erlaubt das Referenzieren dieser Spalten.

– ANSI Standard

– interne Verwendung umANSI Outer Joins zu nutzen

Umgebungsinformationen

• Datenbank Versionen: DBMS_DB_VERSION(RELEASE, VER_LE_12)DBMS_UTILITY.DB_VERSION

• Aktuelle Session Informationen mit SYS_CONTEXT

• FormatSELECT sys_context('<namespace>', '<parameter>')


• Beispiele: Host, OS, IP, NLS Einstellungen, PDB Name, Service, Session Rollen, DBMS_APPLICATION_INFO usw.

• Verwendung für das Setzen von Application Context

• Liste wird ständig erweitert (siehe Handbuch)

– http://docs.oracle.com/database/122/SQLRF/SYS_CONTEXT.htm#SQLRF06117

SELECT sys_context('<namespace>', '<parameter>')

FROM dual;

Die Oracle Datenbank für “all your data”

select p.id, p.name, sl.json_doc.quantity anzahl

from


from

product p, customer c, store s, sales sl

where sl.json_value(json_doc,'$.product_id')=p.id

and

contains(p.notes,fuzzy('Lieferkosten'), 1) > 0

and

sl.json_value(json_doc,'$.CUSTNumber' returning

number) = c.cust_ids

and

s.store_id = 1234

and

sdo_within_distance(c.location,

s.location,'distance=20')='TRUE'

Alle Arten von Daten mit

Advanced Analytics, OLAP, R, PHP, .NET,

SQL ...

„All Your Data“

Volltextrecherche, Klassifizierung und Clustering, Katalogabfragen, Abfragen von Geodaten, Reguläre Ausdrücke,

Speichern, Zugreifen, Verarbeiten


Katalogabfragen, Abfragen von Geodaten, Reguläre Ausdrücke, XML Pfad, Geocoding, Abfragen im RDF Modell,

XML Generierung, Metadatenextraktion von Bildern, Thumbnails, RSS Feeds, Auslesen mit Uritypes, XQuery-Anwendungen, Objekt

relationale Zugriffe, relationale Sichten auf XML Daten ...

Und jetzt auch JSON Daten in die Datenbank

• Seit Oracle Database 12.1.0.2 integraler Bestandteil der Datenbank

• Nutzbar in allen Editionen der Datenbank

• Mit allen Standard Features der Datenbank verwendbar


Mit allen Standard Features der Datenbank verwendbar

• Kein spezieller Datentyp erforderlich

– Speicherung in VARCHAR2 oder LOB Datentypen

• Einführung neuer Funktionen und Operatoren um über die reine Speicherung hinaus JSON Pfad Ausdrücke, relationale Projektionen, Validierungen oder Indizes zu verwenden.

JSON Daten aus einem JSON Dump File mit External Table

create table json_dump_file_contents(json_document CLOB)

organization external

(type oracle_loader default directory json_dir

access parameters

(records delimited by 0x'0A'

fields (json_document CHAR(5000)))

location ('PurchaseOrders.dmp')) reject limit unlimited;


location ('PurchaseOrders.dmp')) reject limit unlimited;

SQL> select * from json_dump_file_contents;

JSON_DOCUMENT

-----------------------------------------------------------------

{"PONumber":1,"Reference":"MSULLIVA-20141102","Requestor":"Martha

Sullivan","User": "MSULLIVA", "CostCenter": "A50",

"ShippingInstructions":{"name":"Martha Sullivan ","Address":

{"street": "200 Sporting Green","city":"South San Francisco",

"state": "CA","zipCode":99236,"country":"United States of

America"},"Phone":[{"type":"Off ice","number":"979-555-6598"}]},

Zugriffe

• Neue Operatoren und Funktionen wie zum Beispiel

– IS JSON/IS NOT JSON: Testet auf wohlgeformte JSON Daten

– JSON_EXISTS: Testet in einer Bedingung auf Existenz eines speziellen Wertes

– JSON_VALUE: Selektiert einen skalaren Wert

Operatoren, Funktionen ...


– JSON_VALUE: Selektiert einen skalaren Wert

– JSON_QUERY: Selektiert ein JSON Fragment - normalerweise ein Objekt oder Feld

– JSON_TABLE: Projiziert JSON Daten in ein relationales Format über eine virtuelleTabelle, ähnlich einer relationalen Inline View.

– JSON_TEXTCONTAINS: Testet ob ein bestimmter Characterstring in den JSON Eigenschaftswerten (property value) existiert

Beispiel mit JSON_QUERYSkalaren Wert abfragen

select min(json_value (json_document, '$."PONumber"' returning number))

from json_tab;

• Oder mit Punktnotation, falls IS JSON Constraint in der Tabellendefinition verwendet wurde


wurde

select min(j.json_document."PONumber")

from json_tab j;

MIN(J.JSON_DOCUMENT."PONUMBER")

-------------------------------------------

1

Relationale ProjektionenJSON_TABLE

create or replace view json_view

as

select jt. ponumber, jt.requestor, jt.adresse, jt.special

from json_tab,

json_table (json_document, '$'

COLUMNS (ponumber number PATH '$.PONumber',


COLUMNS (ponumber number PATH '$.PONumber',

requestor VARCHAR2(32 CHAR) PATH '$.Requestor',

special VARCHAR2(10) PATH '$."Special Instructions"',

adresse VARCHAR2(400) FORMAT JSON PATH '$.ShippingInstructions.Address')) jt

where json_value(json_document,'$.PONumber' returning number) < 5000;

SQL> select ponumber from json_view

where special='Air Mail' and requestor='Karen Partners';

Indizes

• Function Based Index auf die entsprechenden JSON Pfade mit skalaren Werten

JSON_VALUE, JSON_EXISTS, JSON_TEXTCONTAINS

SQL> create index PONUMBER_IDX on json_tab

(JSON_QUERY(JSON_DOCUMENT, '$."PONumber"' returning number ));

Index created


• Spezieller Index und Abfragen mit JSON_TEXTCONTAINS

• Mehr dazu in unserem Bloghttps://apex.oracle.com/pls/apex/germancommunities/dbacommunity/tipp/5421/index.html

Index created

JSON News - Dataguide

• Dataguide erzeugen – mit oder ohne Textindex und als Grundlage für View Creation


JSON News - Dataguide


Weitere JSON News

• Neues Handbuch: Database JSON Developer's Guide

• Partitionierung von JSON Daten

• SQL/JSON Funktionen und Bedingungen in PL/SQL


SQL/JSON Funktionen und Bedingungen in PL/SQL

• SQL/JSON Funktionen können nun für GeoJSON verwendet werden

• JSON Spalten im In-Memory Column Store

• Materialized Views mit JSON Daten

• SQL/JSON Funktionen um JSON zu generieren

•

Nützliche Funktionen: String Aggregation

• Wie kann man Strings aggregieren?

– Eigene Funktion schreiben

– Ab 11g Release 2 mit analytische Funktion listagg

FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP l_text

:= l_text || ',' || cur_rec.ename; END LOOP; ….


– Ab 11g Release 2 mit analytische Funktion listagg

select deptno, LISTAGG(ename, ',')

WITHIN GROUP (ORDER BY ename) AS employees

from emp group by deptno;

DEPTNO EMPLOYEES

---------- --------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Und in 12.2 mehr Kontrolle im Fehlerfall

• listagg bietet nun Kontrolle über den Return Wert, falls maximaleLänge erreicht wird … zuvorORA-01489: Das Ergebnis der Zeichenfolgenverkettung ist zu lang

01489. 00000 - "result of string concatenation is too long"

*Cause: String concatenation result is more than the maximum size.

*Action: Make sure that the result is less than the maximum size.


nun

*Action: Make sure that the result is less than the maximum size.

SQL> select listagg(s, ';' on overflow truncate '...' with count)

within group (order by r)

from (select rownum r , 'x' s from dual connect by level <2002);

x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;

x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;x;...(16)

Und in 12.2 mehr Kontrolle im Fehlerfall

• Auch für Funktionen wieto_binary_double, to_binary_float, to_date, to_dsinterval,

to_number, to_timestamp to_timestamp_tz, to_yminterval

select to_number(hiredate default 1000 on conversion error)

from emp where rownum<2;


• Neue Funktion validate_conversion

from emp where rownum<2;

select validate_conversion('1.11.2016' as number) from dual;

select validate_conversion('1000' as number) from dual;

select validate_conversion('1000' as number) from dual;

• IGNORE_ROW_ON_DUPKEY_INDEX (tabelle (spalte))

– ignoriert Unique Key Violation und führt Row-level Rollback durch

• CHANGE_DUPKEY_ERROR_INDEX

– ORA-38911 statt ORA-001 bei Unique Key Violation

Ein paar nützliche Hints


• ENABLE_PARALLEL_DML

– Parallel DML im SQL Statement(ab 12c)

• APPEND_VALUES

– Direct-Path INSERT mit VALUES Klausel

• RESULT_CACHE

– Cachen des Resultats einer Query

IGNORE_ROW_ON_DUPKEY_INDEX

SQL> insert into emp select * from emp;

insert into emp select * from emp

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_EMP) violated

SQL> insert /*+ ignore_row_on_dupkey_index(emp(empno)) */


SQL> insert /*+ ignore_row_on_dupkey_index(emp(empno)) */

into emp select * from emp;

0 rows created.

SQL> select count(*) from emp;

COUNT(*)

----------

14

Case-Insensitive Datenbank vor 12c Release 2

• Case insensitives Verhalten ist kein Default


• Nutzung von nls_upper, nls_lower in Kombination mit nls_sortusw.

Case-Insensitive Datenbank

• Case Insensitivität by Default!


Ausführungsplan


Weitere Informationen

• Deutschsprachiger Blog: http:/tinyurl.com/dbcommunity

– Aktueller Blogeintrag SQLcl

• Database Online Documentation Library 12c Release 2 (12.2) : http://docs.oracle.com/database/122/nav/portal_booklist.htm

• Monatl.Oracle Technology Update: http://tinyurl.com/oratech-monthly


• Monatl.Oracle Technology Update: http://tinyurl.com/oratech-monthly

• Twitter: @uschwinn, @oraclebudb

Oracle (SQL) Features

PIVOT Clause Error Logging Multi Table Insert SQLcl Kommandos

External Table Load/Unload APPROX_COUNT_DISTINCT() case insensitiv

JSON Support Temporal Validity Flashback JSONIdentity Columns Row Limiting Clause SQL Result Cache

Improved Automatic Degree of Parallelism Row Pattern Matching listagg


Improved Automatic Degree of Parallelism Row Pattern Matching listagg

In-Database Archiving Regular Expressions SYS_CONTEXT Oracle Data Pump Export Views_As_Tables Invisible Columns Virtual Columns

PL/SQL Result Cache Multiple Indexes on Same Set of Columns

Analytical (Window) Functions Model Clause Lateral QueriesIGNORE_ROW_ON_DUPKEY_INDEX Hint APPEND_VALUES Hint

So viele coole Neuigkeiten – einfach ausprobieren


sql tipps und tricks - doag deutsche oracle … tipps und tricks ......

Documents