Wie effizient hat Oracle mit einem sehr lange IN der Liste operator
Habe ich die folgende Abfrage (dies ist die vereinfachte version eines sehr viel komplizierter query):
SELECT * FROM TPM_TASK
WHERE (PROJECTID, VERSIONID) IN ((3,1), (24,1), (4,1))
Im code ich, dass (PROJECTID,VERSIONID)
Schlüssel-Liste programmgesteuert, und diese Liste könnte sich möglicherweise ein paar tausend Paare lange.
Meine Frage ist, wie Oracle optimieren wird, diese Abfrage gegeben, dass ProjectId
und VersionId
indiziert sind. Wird die Liste umgewandelt werden in eine hash-Tabelle, die ähnlich wie ein join
gegen eine temp-Tabelle? Oder wird jeder Schlüssel-lookup getan werden, ein zu einer Zeit?
Habe ich versucht, diese Abfrage unter meine test-Datenbank und habe:
SELECT STATEMENT 68.0 68 2989732 19 8759 68 ALL_ROWS
TABLE ACCESS (FULL) 68.0 68 2989732 19 8759 1 TPMDBO TPM_TASK FULL TABLE ANALYZED 1
Allerdings glaube ich, dass diese Datenbank nicht über genug Daten, um zu gewährleisten einen index-scan. Ich habe versucht, die Abfrage auf die Produktion, und bekam:
SELECT STATEMENT 19.0 19 230367 23 9683 19 ALL_ROWS
INLIST ITERATOR 1
TABLE ACCESS (BY INDEX ROWID) 19.0 19 230367 23 9683 1 TPMDBO TPM_TASK BY INDEX ROWID TABLE ANALYZED 1
INDEX (RANGE SCAN) 4.0 4 64457 29 1 TPMDBO TPM_H1_TASK RANGE SCAN INDEX ANALYZED 1
Scheint dies trifft den index, aber ich bin mir nicht sicher, was INLIST ITERATOR bedeutet. Ich vermute, dies bedeutet, dass Oracle das Durchlaufen der Liste und dabei eine access-Tabelle für jedes Element in der Liste, die würde wohl auch nicht zu effizient mit tausenden von Tasten. Aber vielleicht Oracle ist intelligent genug, um dies zu optimieren, besser, wenn ich tatsächlich hat geben Sie mehrere tausend Schlüssel.
HINWEIS: ich don ' T wollen, laden Sie diesen Schlüssel in eine temp-Tabelle, weil ehrlich gesagt, ich mag nicht die Weise, temp-Tabellen unter Oracle, und Sie sind meist am Ende mehr Frust als Sie Wert sind (in meiner nicht-Experten-Meinung sowieso.)
- Beachten Sie, dass Oracle hat ein limit von 1000 Elemente in der Liste in. Zitat: "Sie können bis zu 1000 Ausdrücke im expression_list.". Also ein "paar tausend Paare long" wäre ein problem.
- Interessant! Über tausend wäre sehr selten, aber ich kann haben, zu überdenken, mein design..
- Obwohl dies, natürlich, stellt sich die Frage, ob ich mit
OR
trennen Sie mehrere großeIN
Ausdrücken, a-la -WHERE x in (0,1,999) OR x in (1001, 1002, 1003)
- Mike - Tatsächlich, Sie können mehr als 1000 Elemente in einem
IN
Liste, wenn Sie die Tupel syntax, die Sie ursprünglich gepostet. Die 1000-element-Grenze gilt nur für Skalare Elemente. - Wusste das nicht, danke! Wie immer, Sie sind eine wahre Quelle der nuancierten wissen.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Soll der Optimierer stützt Ihre Entscheidung auf die Anzahl der Elemente in der Liste und die Anzahl der Zeilen in der Tabelle. Wenn der Tisch hat Millionen von Zeilen, und die Liste hat noch ein paar tausend Artikel, würde ich generell erwarten, dass es mit dem index zu tun, der ein paar tausend single-row-lookups. Wenn der Tisch hat ein paar tausend Zeilen und die Liste hat ein paar tausend Stück, würde ich erwarten, dass der optimizer führen Sie einen vollständigen scan der Tabelle. In der Mitte, natürlich, ist, wo alle die interessanten Sachen passiert und wo es schwerer wird, arbeiten Sie heraus, was genau planen die Optimierung wählen.
Im Allgemeinen, jedoch dynamisch Gebäude dieser Art der Abfrage wird problematisch sein, die aus einer performance-Sicht nicht, weil, wie teuer eine bestimmte Ausführung der Abfrage ist, sondern weil die Abfragen, die Sie generieren, sind nicht teilbar. Da können Sie nicht verwenden Sie bind-Variablen (oder, wenn Sie mit bind-Variablen, benötigen Sie eine unterschiedliche Anzahl von bind-Variablen). Das zwingt Oracle zu tun, eher teuer, schwer analysieren der Abfrage jedes mal und macht Druck auf Ihre shared-pool wird wahrscheinlich zwingen, aus anderen Abfragen, sind teilbar, das werden Sie verursachen mehr schwer Analyse in das system. Sie werden in der Regel besser bedient, warf die Daten, die Sie anpassen möchten, auf in eine temporäre Tabelle (oder auch eine permanente Tabelle), so dass Sie Ihre Abfrage kann dann vorgenommen werden, geteilt und analysiert nur einmal.
Zu Branko Kommentar, während Oracle ist auf 1000 begrenzt Literale in einer
IN
Liste, das ist nur, wenn Sie mit den "normalen" syntax, d.h.Wenn Sie die Tupel syntax, die Sie weiter oben geschrieben, jedoch, Sie können eine unbegrenzte Anzahl von Elementen.
So, zum Beispiel, werde ich erhalten eine Fehlermeldung, wenn ich den Aufbau einer Abfrage mit 2000 Elementen in der
IN
ListeAber nicht, wenn ich mit dem Tupel syntax
Eine bessere Lösung, das keine temp-Tabellen möglicherweise werden die Daten in eine PL/SQL-Tabelle, und dann fügen Sie es. Tom Kyte hat ein hervorragendes Beispiel hier:
PL/SQL-Tabellen-join Beispiel
Hoffe, das hilft.