22. August 2012

Anwendungsentwicklung und SQL "IN" Abfragen ...

Application Development and SQL "IN" Queries
Bei der Entwicklung von Anwendungen, sei es mit APEX, Java, PHP oder anderen Umgebungen, verwendet man (natürlich) immer wieder SQL IN-Abfragen wie folgt ...
select * from emp
where empno in (7839, 7844, 7900)

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7844 TURNER     SALESMAN        7698 08.09.81       1500          0         30
      7900 JAMES      CLERK           7698 03.12.81        950                    30
In Java würde das so aussehen.
     :
     PreparedStatement pstmt =
       con.prepareStatement("select * from emp where empno in (?,?,?)");
     pstmt.setInt(1, 7839);
     pstmt.setInt(2, 7844);
     pstmt.setInt(3, 7900);
     ResultSet rs = pstmt.executeQuery();
     :
Für die SQL-Abfrage im Beispiel kann man nun genau drei Werte übergeben. Möchte man weniger oder mehr Werte übergeben, so muss man die SQL-Abfrage ändern - man braucht also dynamisches SQL. Das erhöht aber den Aufwand, denn das SQL muss nun programmatisch generiert werden; in PL/SQL muss man mit EXECUTE IMMEDIATE oder OPEN ... FOR arbeiten. Außerdem muss man, sobald man mit dynamischem SQL arbeitet, auf SQL Injection-Lücken achtgeben. "Statisches SQL" wäre schon einfacher. besser.
Es gibt jedoch Ansätze, auch solche Fälle mit statischem SQL abzudecken - und zwei dieser Ansätze möchte ich nun vorstellen. Der erste Ansatz ist sehr einfach und vor allem geeignet, wenn beim Lookup kein Indexzugriff erforderlich ist. Die Idee ist, die Liste als "separierten String" zu übergeben, also bspw. ":7849:7839:7900:" (wichtig ist es, die Trennzeichen auch zu Beginn und zum Ende zu setzen). Nun kann man das Problem mit einem einfachen SQL LIKE lösen:
     select * from emp
     where ':7839:7844:7900:' like '%:' || empno || ':%'
Am Java-Beispiel wird deutlich, wie sich der Code vereinfacht.
     :
     PreparedStatement pstmt =
       con.prepareStatement("select * from emp where ? like '%:' || empno || ':%'");
     pstmt.setString(1, ":7839:7844:7900:");
     ResultSet rs = pstmt.executeQuery();
     :
Der Nachteil ist, dass für die Spalte EMPNO nun kein Index mehr verwendet werden kann; diese Lösung ist also nur für kleine Datenmengen geeignet oder für solche Abfragen, bei denen auf besagter Tabellenspalte kein Indexzugriff mehr nötig ist. Ein Beispiel dafür könnte ein räumlicher Index (Geodaten) sein; wenn dieser schon zum Einsatz kommt, ist ein zweiter, "normaler" Index oft kontraproduktiv.
Möchte man dem Optimizer die Möglichkeit belassen, einen Index nutzen zu können, muss man anders vorgehen. Eine Variante wäre ein "VARRAY", welches in SQL mit der Funktion TABLE() genutzt werden kann. Zuerst braucht es eine Type-Definition für das Array.
create type t_numarray as table of number
/

Type created.
In einer SQL-Abfrage lässt sich das dann wie folgt nutzen.
select * from emp
where empno in (
  select e.column_value from table(t_numarray(7839,7844,7900)) e
)
In PL/SQL-Funktionen kann man damit schon sehr elegant arbeiten; mit Java, PHP, .NET oder anderen Umgebungen ist die Konstruktion bzw. Übergabe des Arrays dagegen sehr umständlich. Da kann eine PL/SQL-Funktion zum Zerlegen helfen: Eine solche hatte ich vor einer Zeitlang auf diesem Blog veröffentlicht. Nun kann anstelle des Array wieder eine Zeichenkette übergeben werden; die Funktion STRING_TOKENIZER.ALL_TOKENS wandelt sie in ein Array um.
select * from emp
where empno in (
  select e.token_text from table(string_tokenizer.all_tokens('7839,7844,7900',',')) e
)
/
Auch von "extern" lässt sich dieses Konstrukt elegant nutzen.
     :
     PreparedStatement pstmt =
       con.prepareStatement(
           "select * from emp where empno in ( "+
           "  select e.token_text from table(string_tokenizer.all_tokens(?, ',')) e " +
           ")";
     pstmt.setString(1, "7839,7844,7900");
     ResultSet rs = pstmt.executeQuery();
     :
In der Praxis hatte ich dann aber schon erlebt, dass dieses Konstrukt zu "suboptimalen" Ausführungsplänen - und damit zu schlechter Performance führen kann.
SQL> select * from emp
  2  where empno in (
  3    select e.token_text from table(string_tokenizer.all_tokens('7839,7844,7900',',')) e
  4  )
  5  /

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2046375553

---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |  8168 |    16M|    34   (6)| 00:00:01 |
|*  1 |  HASH JOIN                           |            |  8168 |    16M|    34   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | EMP        |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   VIEW                               | VW_NSO_1   |  8168 |    15M|    30   (4)| 00:00:01 |
|   4 |    HASH UNIQUE                       |            |  8168 | 16336 |    30   (4)| 00:00:01 |
|   5 |     COLLECTION ITERATOR PICKLER FETCH| ALL_TOKENS |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Die Spalte Rows bringt es an den Tag: Der Optimizer geht davon aus, dass 8.168 Tokens zurückgeliefert werden. Das ist aber nicht richtig - und es kann schon sein, dass diese Annahme zu einer falschen Strategie beim Ausführen der Abfrage führt. Nun kann man für das Array nicht, wie für eine Tabelle, Statistiken sammeln; wie können den Optimizer aber dazu bringen, das übergebene Array etwas näher zu betrachten ...
select * from emp
where empno in (
  select /*+ dynamic_sampling (e 2) */ e.token_text from table(string_tokenizer.all_tokens('7839,7844,7900', ',')) e
)
/
Am Ausführungsplan kann man nun erkennen, dass der Optimizer nun bessere Informationen hat ...
---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |     3 |  6120 |    16  (13)| 00:00:01 |
|*  1 |  HASH JOIN                           |            |     3 |  6120 |    16  (13)| 00:00:01 |
|   2 |   VIEW                               | VW_NSO_1   |     3 |  6006 |    12   (9)| 00:00:01 |
|   3 |    HASH UNIQUE                       |            |     3 |     6 |    12   (9)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| ALL_TOKENS |     3 |     6 |    11   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL                  | EMP        |    14 |   532 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Das geht bei der kleinen Tabelle alles sehr schnell. Probieren wir es mal mit einer großen ... die Tabelle BIGEMP hat 6,8 Mio Zeilen und, zusätzlich, eine eindeutige Spalte ID, die auch mit einem UNIQUE-Index versehen ist.
select * from bigemp b
where id in (
  select /*+ dynamic_sampling(e 2)*/ e.token_text from table(string_tokenizer.all_tokens('1,78,78123',',')) e
)
/

        ID      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
        78       7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10
     78123       7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

3 Zeilen ausgewählt.

Abgelaufen: 00:00:00.07

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |       |    18 (100)|          |
|   1 |  NESTED LOOPS                         |               |       |       |            |          |
|   2 |   NESTED LOOPS                        |               |     3 |  6138 |    18   (6)| 00:00:01 |
|   3 |    VIEW                               | VW_NSO_1      |     3 |  6006 |    12   (9)| 00:00:01 |
|   4 |     HASH UNIQUE                       |               |     3 |     6 |    12   (9)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| ALL_TOKENS    |     3 |     6 |    11   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN                  | IDX_BIGEMP_ID |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID         | BIGEMP        |     1 |    44 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Wir fassen zusammen ...
  • Wir können statisches SQL nehmen, die Abfrage muss nicht stets neu zusammengesetzt werden. Damit reduzieren sich SQL Injection-Gefahren, der Code wird einfacher und die Datenbank muss nicht soviel SQL parsen.
  • Wir können einfache Datentypen übergeben; es müssen keine aufwändigen Objekte konstruiert werden; der Code dürfte sogar über einen DB-Link hinweg funktionieren.
  • Mit dem Hint dynamic_sampling kann man verhindern, dass der Optimizer von falschen "kardinalitäten" ausgeht.
Viel Spaß beim Ausprobieren
When building applications on top of the Oracle database, SQL IN queries are commonly used. This is true for all programming languages (Java, PHP, PL/SQL, .NET, ...)
select * from emp
where empno in (7839, 7844, 7900)

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7844 TURNER     SALESMAN        7698 08.09.81       1500          0         30
      7900 JAMES      CLERK           7698 03.12.81        950                    30
This query, in a java program, would look like this:
     :
     PreparedStatement pstmt =
       con.prepareStatement("select * from emp where empno in (?,?,?)");
     pstmt.setInt(1, 7839);
     pstmt.setInt(2, 7844);
     pstmt.setInt(3, 7900);
     ResultSet rs = pstmt.executeQuery();
     :
This code allows to pass exactly 3 values - not more, not less. When more or less values are needed, the SQL query would have to be "generated" - there needs to be one question mark for each value. So "dynamic SQL" is needed. But this requires more complex code. And in PL/SQL we have to switch from a "plain" SQL query to EXECUTE IMMEDIATE or OPEN .. FOR. Beyond that, dynamic SQL means a potential(!) SQL injection vulnerability. Summarized, "static SQL" would be the simpler (and often better) solution.
There are approaches to solve this problem with static SQL. And in this blog posting I'd like to introduce two of them. The first one is a rather simple one using a SQL LIKE construct. It's useful, when no index lookup is required. All the values are being concatenated using a chosen delimiting character (e.g. ":7849:7839:7900:"). Note to add the delimiter character also to the beginning and the end of the string. Having done that, we can write a simple SQL LIKE query as follows ...
     select * from emp
     where ':7839:7844:7900:' like '%:' || empno || ':%'
The java example illustrates code simplicity.
     :
     PreparedStatement pstmt =
       con.prepareStatement("select * from emp where ? like '%:' || empno || ':%'");
     pstmt.setString(1, ":7839:7844:7900:");
     ResultSet rs = pstmt.executeQuery();
     :
The disadvantage is that the optimizer will always perform a "full table scan" on the query - this cannot be sped up with an index. So it's only useful for small tables and for queries where an index lookup on that very column is not feasible. Spatial queries are a typical example for the latter case: Having a spatial index in use, using another index is, most often, more costly than simple filtering.
When we want to make use of index lookups, we need another approach. We could pass the values as a VARRAY data type. The SQL function TABLE() allows to access a VARRAY within a query. First, we need the array type definition.
create type t_numarray as table of number
/

Type created.
Now we can use it as follows ...
select * from emp
where empno in (
  select e.column_value from table(t_numarray(7839,7844,7900)) e
)
In PL/SQL, this approach is very straightforward. But in Java or other "external" programming environments, the construction of database arrays or object types is most often very complex - sometimes the database driver does even not allow it. So we'd like to use that approach, but we want to pass a simple string from the application to the database.
Then we need to convert the string, which is being received from the application, to an array. The STRING_TOKENIZER function, which I posted on this blog some time ago, might be helpful here. With this function, our query looks as follows:
select * from emp
where empno in (
  select e.token_text from table(string_tokenizer.all_tokens('7839,7844,7900',',')) e
)
/
The values are being passed as a string. The java example - again - looks very simple.
     :
     PreparedStatement pstmt =
       con.prepareStatement(
           "select * from emp where empno in ( "+
           "  select e.token_text from table(string_tokenizer.all_tokens(?, ',')) e " +
           ")";
     pstmt.setString(1, "7839,7844,7900");
     ResultSet rs = pstmt.executeQuery();
     :
So far - so good. In practice, I sometimes encountered bad performance for these kind of queries. Let's have a look at the execution plan.
SQL> select * from emp
  2  where empno in (
  3    select e.token_text from table(string_tokenizer.all_tokens('7839,7844,7900',',')) e
  4  )
  5  /

Execution plan
---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |  8168 |    16M|    34   (6)| 00:00:01 |
|*  1 |  HASH JOIN                           |            |  8168 |    16M|    34   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | EMP        |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   VIEW                               | VW_NSO_1   |  8168 |    15M|    30   (4)| 00:00:01 |
|   4 |    HASH UNIQUE                       |            |  8168 | 16336 |    30   (4)| 00:00:01 |
|   5 |     COLLECTION ITERATOR PICKLER FETCH| ALL_TOKENS |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
The Rows column shows, that the optimizer assumes an "array cardinality" of ~8100 rows. In reality, there were only three. This mismatch might lead to suboptimal execution plans. But with a hint, we can let the optimizer "take another look" into the array.
select * from emp
where empno in (
  select /*+ dynamic_sampling (e 2) */ e.token_text from table(string_tokenizer.all_tokens('7839,7844,7900', ',')) e
)
/
The new execution plan shows that the optimizer now can work with more accurate information.
---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |     3 |  6120 |    16  (13)| 00:00:01 |
|*  1 |  HASH JOIN                           |            |     3 |  6120 |    16  (13)| 00:00:01 |
|   2 |   VIEW                               | VW_NSO_1   |     3 |  6006 |    12   (9)| 00:00:01 |
|   3 |    HASH UNIQUE                       |            |     3 |     6 |    12   (9)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| ALL_TOKENS |     3 |     6 |    11   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL                  | EMP        |    14 |   532 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
For a small table, this "hash join" with a "full table scan" is OK. But how does it look for a "BIGEMP" table with 6.8 million rows. To create this table I needed another ID column with a unique index. The rest of the data is just N copies of the original EMP table.
select * from bigemp b
where id in (
  select /*+ dynamic_sampling(e 2)*/ e.token_text from table(string_tokenizer.all_tokens('1,78,78123',',')) e
)
/

        ID      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
        78       7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10
     78123       7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

3 rows selected.

Elapsed: 00:00:00.07

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |       |    18 (100)|          |
|   1 |  NESTED LOOPS                         |               |       |       |            |          |
|   2 |   NESTED LOOPS                        |               |     3 |  6138 |    18   (6)| 00:00:01 |
|   3 |    VIEW                               | VW_NSO_1      |     3 |  6006 |    12   (9)| 00:00:01 |
|   4 |     HASH UNIQUE                       |               |     3 |     6 |    12   (9)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| ALL_TOKENS    |     3 |     6 |    11   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN                  | IDX_BIGEMP_ID |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID         | BIGEMP        |     1 |    44 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Summarized:
We can use static SQL even for SQL IN queries with dynamic values - we do not have to use dynamic SQL or string concatenation. The advantage of "static SQL" is less SQL injection vulnerability and - overall - less overhead for SQL parsing. Using a "string tokenizer" function, we can keep passing simple string values - constructing database object types or database arrays within the application is not required. And with a DYNAMIC_SAMPLING hint we can let the optimizer determine the correct cardinality of the array being passed.
Have fun trying it out.

Keine Kommentare:

Beliebte Postings