22. Oktober 2009

Neu in Oracle 11.2 und praktisch: Der IGNORE_ROW_ON_DUPKEY_INDEX Hint

English title: Oracle 11.2: The IGNORE_ROW_ON_DUPKEY_INDEX Hint

Heute will ich mich mal dem Thema Oracle11g Release 2 widmen. Im Rahmen der deutschen APEX-Community gab es ja bereits Anfang September einen ersten Einblick in die neuen Features. Dieses Blog-Posting widmet sich einem Feature, das nicht auf der Liste ist: Den neuen Optimizer-Hints zum Handling von Duplicate Key Violations bei SQL INSERT-Kommandos. Die Ausgangssituation:
Today I'll start writing about the new database release 11.2. For german readers the APEX community website contains some information about new features since 11.2 was released in september. This blog posting is about a feature which is not on that list: There are new optimizer hints which can help handling duplicate key violations on SQL insert commands. An Example:
create table test_tab (
  id   number(10) primary key,
  name varchar2(20)
)
/

insert into test_tab values (1, 'Oracle')
/

commit
/
Möchte man nun nochmals eine Zeile mit der ID 1 einfügen, so gibt es eine Fehlermeldung - das ist bekannt. Wenn man nun mehrere Zeilen auf einmal einfügen möchte (und man nicht weiss, ob diese teilweise Unique Key Violations auslösen würden), so bieten sich bislang zwei Varianten an:
  • Man baut eine PL/SQL-Schleife, geht darin durch die einzufügenden Zeilen durch (SELECT-Loop), probiert mit jeder einzelnen den INSERT und fängt etwaige Exceptions ab. Das muss allerdings codiert werden, ist langsam und fehleranfällig
  • Man nutzt das MERGE-Kommando
If you now try to insert another row with the ID value of 1 you get an error message - this is clear. If you now want to insert multiple rows into that table (and you don't know whether there will be unique key violations) there are two approaches:
  • Use PL/SQL to build a loop on the SELECTed rows, try an INSERT for each row and catch the exception. This has to be coded, is errornous and slow.
  • A better approach is the MERGE command
In Oracle 11.2 gibt es eine dritte Variante per Optimizer-Hint (IGNORE_ROW_ON_DUPKEY_INDEX): Und das geht so:
Oracle 11.2 now has a third variant: The optimizer hint IGNORE_ROW_ON_DUPKEY_INDEX.
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(test_tab(id))*/ into test_tab values (1, 'MySQL')
/

0 rows inserted.
Wie man sieht, wird zwar nichts eingefügt, es wird aber auch keine Fehlermeldung ausgelöst. Der Hint funktioniert nur bei Single Table-INSERT-Kommandos. Laut Dokumentation ist dies trotz Hint-Syntax ein sog. Mandate, wird vom Optimizer also auf jeden Fall umgesetzt. Normalerweise ist ein Hint - wie der Name schon sagt - ein Hinweis an den Optimizer, den dieser auch ignorieren darf. Ich finde das eine ganz nette Variante, da es mir die PL/SQL-Loops erspart und doch ein wenig einfacher als MERGE ist. Mehr Infos zu 11.2 in den nächsten Blog-Postings - Stay tuned ...
As you can see: There is no row being inserted but also no error message - the statement succeeds. This hint does only work for Single Table INSERT statements. The documentation states that this hint is indeed a mandate - so for the optimizer it is a mandatory command. Unlike a classic hint it cannot choose between following or not following it. IMHO this is a nice and easy alternative to a MERGE command - PL/SQL loops are still not necessary and it's much easier to use. I'll do more blog postings about 11.2 features in the near future - Stay tuned ...

12. Oktober 2009

LIKE-Abfragen und Indizes: Geht das ..:?

English title: LIKE queries and indexes ...

Dass man mit dem LIKE-Operator eine Wildcard-Suche durchführen kann, ist hinreichend bekannt. Interessant wird es, wenn man sich fragt, wie das denn mit der Indizierung aussieht. Dazu ein Beispiel anhand der Tabelle EMP:
Everyone knows that the LIKE operator allows wildcard searches within SQL. The interesting thing is when it's about indexes. We'll start with an example based on the EMP table.
SQL> create index idx_ename on emp (ename);

Index created.

SQL> select * from emp where ename like 'K%';

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7839 KING       PRESIDENT       17.11.81  5000           10
Hierfür kann ein Index genutzt werden, wie der Ausführungsplan zeigt
This query can be executed using an index - as the execution plan indicates.
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ENAME |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME" LIKE 'K%')
       filter("ENAME" LIKE 'K%')
Das ist klar, denn die Wildcard steht am Ende. Da der Anfang des Suchstrings feststeht, kann der Index genutzt werden. Probieren wir es mal andersherum ...
That's abvious since the beginning of the search string is a literal - the wildcard is at the end. So the optimizer chooses an index range scan to speed up query execution. The next example is the other way around: The wildcard is now at the beginning of the search string.
SQL> select * from emp where ename like '%G';

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7839 KING       PRESIDENT       17.11.81  5000           10

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor());

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" LIKE '%G')
Naja und auch das ist klar; da der Anfang des Suchstrings nicht bekannt ist, kann die Datenbank den Index gar nicht nutzen. Ein Full-Table-Scan ist das einzige, was bleibt ...
And that's also obvious: When the search string starts with a wildcard the optimizer just cannot use the index - where should it enter the index tree ...? So a full table scan is the only option.
Wirklich das einzige? Es gäbe noch die Moglichkeit, mit einem function-based Index zu arbeiten. Dazu brauchen wir zunächst eine Funktion, die uns den String "umdreht".
Really? Function Based indexes might be another approach. For this we firstly need a function which "reverses" a string.
create or replace function string_reverse (
  p_string in varchar2
) return varchar2 deterministic is
  v_revstring varchar2(32767);
begin
  for i in reverse 1..length(p_string) loop
    v_revstring := v_revstring || substr(p_string, i, 1);
  end loop;
  return v_revstring;
end;
/
Wichtig ist das Schlüsselwort deterministic. Damit deklarieren wir explizit, dass diese Funktion bei gleichen Eingabeparametern das gleiche Ergebnis zurückliefert. Das ist ein wichtig für den Optimizer. Als nächstes kommt nun ein funktionsbasierter Index auf die Spalte ENAME im Zusammenspiel mit dieser Funktion.
The keyword deterministic is important here. This declared explicitly that the function returns the equal values for equal input parameters. This is important for the optimizer. The next step is to create the function based index on the ENAME column using this new function.
SQL> create index idx_ename_rev on emp (string_reverse(ename));

Index created.
Nun probieren wir das aus. Die obige Abfrage kann den Index nicht nutzen, man kann sie aber nun sinngemäß wie folgt umschreiben ... demnach ist ...
Now it's about testing this setup. The above query still cannot use the index but we could now rewrite the query - so the above query ...
SQL> select * from emp where ename like '%G';
... das gleiche wie ...
... is the same as this one ...
SQL> select * from emp where string_reverse(ename) like string_reverse('%G');
Letzteres kann allerdings nun einen Index nutzen, wie der Ausführungsplan zeigt:
And the latter one can use the new function based index - as the execution plan shows.
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |  2089 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |  2089 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ENAME_REV |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SCOTT"."STRING_REVERSE"("ENAME") LIKE "STRING_REVERSE"('%G'))
       filter("SCOTT"."STRING_REVERSE"("ENAME") LIKE "STRING_REVERSE"('%G'))
Man sieht also, dass man auch LIKE-Abfragen mit Wildcards am Anfang recht einfach beschleunigen kann. Was allerdings nicht mehr geht, wäre eine Wildcard am Anfang und am Ende (LIKE '%IN%'). Wenn Ihr sowas in großen Datenmengen braucht, wäre eher ein Volltextindex angedacht - dazu findet Ihr im Blog oracle-text-de.blogspot.com mehr Informationen.
So LIKE queries can also use an index - with function based indexes. The next step would then be a LIKE query having wildcards at the beginning as well as at the end (e.g. LIKE '%IN%'. But this case cannot be solved with this technology: If you need index support for such queries on larger data sets you might consider Oracle TEXT - the blog oracle-text-de.blogspot.com (in german) contains more information about this technology.

1. Oktober 2009

Oracle 11g Release 2 kennenlernen: Launch-Event in München, Stuttgart, Köln und Hamburg

Oracle11g Release 2 ist bekanntlich seit einem Monat für Linux-Plattformen verfügbar. Wenn Ihr genauer wissen möchtet, was das Release mit sich bringt und im Oktober einen Tag Zeit habt, dann ist Oracle11g Release 2 Launch-Veranstaltung (mehr Information) vielleicht genau das Richtige: Die Teilnahme ist kostenlos! Wenn Ihr also Zeit habt, meldet euch gleich an. Vielleicht sieht man sich ja dort: In München, Stuttgart und Köln stelle ich die neuen Features für Entwickler vor ...

Beliebte Postings