Verwenden des Suchoptimierungsdienstes¶
Der Suchoptimierungsdienst kann die Leistung bestimmter Typen von Such- und Analyseabfragen, die einen umfangreichen Satz von Prädikaten zur Filterung verwenden, erheblich verbessern.
Um mit einem Tutorial zu beginnen, das die Ausführungszeit mit und ohne Suchoptimierung vergleicht, fahren Sie mit Erste Schritte mit der Suchoptimierung fort.
Unter diesem Thema:
Identifizieren der Tabellen und Spalten, die von der Suchoptimierung profitieren
Abfragen identifzieren, die von der Suchoptimierung profitieren
Konfigurieren der Suchoptimierung für eine Tabelle
Konfigurieren der Suchoptimierung für bestimmte Spalten
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für bestimmte Spalten
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für alle anwendbaren Spalten
Beispiel: Unterstützung unterschiedlicher Typen von Prädikaten
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für ein VARIANT-Feld
Entfernen der Suchoptimierung aus bestimmten Spalten oder aus der gesamten Tabelle
Grundlegendes zum Suchoptimierungsdienst¶
Der Suchoptimierungsdienst zielt darauf ab, die Leistung bestimmter Typen von Abfragen auf Tabellen erheblich zu verbessern. Dazu zählen:
Selektive Punktsuchabfragen auf Tabellen. Eine Punktsuchabfrage gibt nur eine oder eine geringe Anzahl unterschiedlicher Zeilen zurück. Typische Anwendungsfälle sind z. B.:
Geschäftsanwender, die schnelle Antwortzeiten bei kritischen Dashboards mit hochselektiven Filtern benötigen.
Datenwissenschaftler, die große Datenmengen untersuchen und nach bestimmten Teilmengen von Daten suchen.
Datenanwendungen, die eine kleine Ergebnismenge auf der Grundlage eines umfangreichen Satzes von Filterprädikaten abrufen.
Suche nach Teilzeichenfolgen und regulären Ausdrücken (z. B. LIKE, ILIKE, RLIKE usw.).
Abfragen auf Felder in den Spalten VARIANT, OBJECT und ARRAY, die bestimmte Typen von Prädikaten verwenden:
Gleichheitsprädikate
IN-Prädikate
Prädikate, die ARRAY_CONTAINS-Typen verwenden
Prädikate, die ARRAYS_OVERLAP-Typen verwenden
Prädikate mit Teilzeichenfolgen und regulären Ausdrücken
Prädikate, die auf NULL-Werte prüfen
Abfragen, die ausgewählte Geodatenfunktionen mit GEOGRAPHY-Werten verwenden.
Sobald Sie die Abfragen identifiziert haben, die vom Suchoptimierungsdienst profitieren können, können Sie die Suchoptimierung für die in diesen Abfragen verwendeten Spalten und Tabellen konfigurieren.
Wie funktioniert der Suchoptimierungsdienst?¶
Um die Performance von Punktsuchabfragen zu verbessern, erstellt und verwaltet der Suchoptimierungsdienst eine dauerhafte Datenstruktur, die Suchzugriffspfad genannt wird. Der Suchzugriffspfad verfolgt, welche Werte der Tabellenspalten in jeder der Mikropartitionen gefunden werden könnten, sodass einige beim Durchsuchen der Tabelle übersprungen werden können.
Ein im Hintergrund ausgeführter Wartungsdienst ist für das Erstellen und Warten des Suchzugriffspfads verantwortlich:
Wenn Sie für eine Tabelle die Suchoptimierung konfigurieren, erstellt der Wartungsdienst den Suchzugriffspfad und füllt ihn mit den Daten, die zum Ausführen der Suchläufe erforderlich sind.
Das Auffüllen von Daten kann je nach Größe der Tabelle sehr viel Zeit in Anspruch nehmen. Der Wartungsdienst wird im Hintergrund ausgeführt und blockiert keine gleichzeitigen Operationen auf der Tabelle.
Wenn Daten in der Tabelle aktualisiert werden (z. B. durch Laden neuer Datasets oder durch DML-Operationen), aktualisiert der Wartungsdienst automatisch den Suchzugriffspfad, um die Änderungen an den Daten widerzuspiegeln.
Wenn Abfragen ausgeführt werden, aber der Suchzugriffspfad noch nicht aktualisiert wurde, werden die Abfragen zwar möglicherweise langsamer ausgeführt, aber es werden immer aktuelle Ergebnisse zurückgegeben.
Der Fortschritt des Wartungsdienstes jeder Tabelle wird in der Spalte search_optimization_progress
in der Ausgabe von SHOW TABLES angezeigt. Bevor Sie die Leistungsverbesserung der Suchoptimierung für eine neu optimierte Tabelle messen, warten Sie, bis die Spalte anzeigt, dass die Tabelle vollständig optimiert wurde.
Suchzugriffspfad und Wartungsdienst sind für den Benutzer transparent. Sie müssen kein Warehouse für den Dienst erstellen, der den Suchzugriffspfad verwaltet.
Beachten Sie jedoch, dass bei diesem Dienst Kosten für Speicher- und Computeressourcen anfallen. Weitere Informationen dazu finden Sie unter Verwalten der Kosten des Suchoptimierungsdienstes (unter diesem Thema).
Berücksichtigung anderer Lösungen zur Optimierung der Abfrageleistung¶
Der Suchoptimierungsdienst ist eine von mehreren Möglichkeiten zur Optimierung der Abfrageleistung. Verwandte Techniken sind:
Abfragebeschleunigung
Clustering von Tabellen
Erstellen von materialisierten Ansichten (gruppiert oder nicht gruppiert)
Jede dieser Optionen hat unterschiedliche Vorteile, wie die folgende Tabelle zeigt:
Feature |
Unterstützte Abfragetypen |
Anmerkungen |
---|---|---|
Suchoptimierungsdienst |
Der Suchoptimierungsdienst kann die Performance dieser Suchtypen für die unterstützten Datentypen verbessern. |
|
Abfragen mit Filtern oder Aggregation. Wenn die Abfrage LIMIT enthält, muss die Abfrage auch ORDER BY enthalten.
Die Filter müssen hochselektiv sein, und die ORDER BY-Klausel muss eine niedrige Kardinalität haben.
Die Abfragebeschleunigung funktioniert gut bei Ad-hoc-Analysen, Abfragen mit unvorhersehbarem Datenvolumen
und Abfragen mit großen Scans und selektiven Filtern.
|
Abfragebeschleunigung und Suchoptimierung ergänzen sich gegenseitig. Beide können die gleiche Abfrage beschleunigen. Siehe Kompatibilität mit Abfragebeschleunigung. |
|
Hinweis: Die Performance kann nur für die Teilmenge der Zeilen und Spalten verbessert werden, die in der materialisierten Ansicht enthalten sind. |
Sie können materialisierte Ansichten auch verwenden, um verschiedene Gruppierungsschlüssel für dieselbe Quelltabelle (oder eine Teilmenge dieser Tabelle) zu definieren oder um vereinfachte JSON- oder Variant-Daten zu speichern, damit sie nur einmal vereinfacht werden müssen. |
|
Hinweis: Eine Tabelle kann jedoch nur auf einem einzigen Schlüssel gruppiert werden, der eine oder mehrere Spalten oder Ausdrücke umfassen kann. |
In der folgenden Tabelle wird zeigt, welche dieser drei Optimierungsverfahren Speicher- oder Computekosten verursachen:
Speicherkosten |
Computekosten |
|
---|---|---|
Suchoptimierungsdienst |
✔ |
✔ |
Query Acceleration Service |
✔ |
|
Materialisierte Ansicht |
✔ |
✔ |
Clustering der Tabelle |
✔ 1 |
✔ |
- 1
Der Reclustering-Prozess kann die Größe des Fail-safe-Speichers erhöhen, da bestehende Partitionen in neue Partitionen umgeschrieben werden. (Beachten Sie, dass bei diesem Prozess keine neuen Zeilen eingefügt werden. Es werden lediglich die vorhandenen Zeilen umorganisiert.) Weitere Details dazu finden Sie unter Auswirkung von Reclustering auf Credit- und Speicherverbrauch.
Welche Zugriffssteuerungsrechte sind für den Suchoptimierungsdienst erforderlich?¶
Um die Suchoptimierung einer Tabelle hinzuzufügen, zu konfigurieren oder zu entfernen, müssen Sie über die folgenden Berechtigungen verfügen:
Berechtigung OWNERSHIP für die Tabelle
Berechtigung ADD SEARCH OPTIMIZATION für das Schema, das die Tabelle enthält
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>
Um den Suchoptimierungsdienst für eine Abfrage zu verwenden, benötigen Sie nur die SELECT-Berechtigung für die Tabelle.
Sie benötigen keine zusätzlichen Berechtigungen. Da SEARCH OPTIMIZATION eine Tabelleneigenschaft ist, wird sie beim Abfragen einer Tabelle automatisch erkannt und (falls zutreffend) verwendet.
Identifizieren der Tabellen und Spalten, die von der Suchoptimierung profitieren¶
Die Suchoptimierung ist besonders wirkungsvoll bei der Verbesserung der Leistung von Abfragen, die häufig auf anderen Spalten als dem primären Clusterschlüssel der Tabelle ausgeführt werden.
Der Suchoptimierungsdienst unterstützt derzeit bestimmte Typen von Abfragen für die folgenden Datentypen:
Festkommazahlen (z. B. INTEGER, NUMERIC)
DATE, TIME und TIMESTAMP
VARCHAR
BINARY
VARIANT, OBJECT und ARRAY
GEOGRAPHY.
Derzeit unterstützt der Suchoptimierungsdienst keine Gleitkomma-Datentypen, GEOMETRY oder andere Datentypen, die oben nicht aufgeführt sind. Snowflake bietet möglicherweise in Zukunft Unterstützung von weiteren Datentypen.
Der Suchoptimierungsdienst unterstützt auch keine Sortierungen.
Abfragen identifzieren, die von der Suchoptimierung profitieren¶
Die Suchoptimierung eignet sich am besten zur Verbesserung der Leistung bei folgenden Abfragetypen:
Eine Abfrage, die normalerweise einige Sekunden oder länger dauert (vor Anwendung der Suchoptimierung). In den meisten Fällen wird die Suchoptimierung die Leistung einer Abfrage mit einer Ausführungszeit von weniger als einer Sekunde nicht wesentlich verbessern.
Mindestens eine der Spalten, auf die über die Abfragefilteroperation zugegriffen wird, weist mindestens 100.000–200.000 diskrete Werte auf.
Um die Anzahl der unterschiedlichen Werte zu bestimmen, können Sie eine der folgenden Methoden verwenden:
Verwenden Sie
APPROX_COUNT_DISTINCT
, um die ungefähre Anzahl unterschiedlicher Werte zu erhalten:select approx_count_distinct(column1) from table1;
Verwenden Sie
COUNT(DISTINCT <Spaltenname>)
, um die tatsächliche Anzahl unterschiedlicher Werte zu erhalten:select count(distinct c1), count (distinct c2) from test_table;
Da Sie nur eine Annäherung an die Anzahl der unterschiedlichen Werte benötigen, sollten Sie
APPROX_COUNT_DISTINCT
verwenden, das im Allgemeinen schneller und kostengünstiger alsCOUNT(DISTINCT <Spaltenname>)
ist.
Die Suchoptimierung kann die Leistung bei folgender Typen von Abfragen verbessern:
Gleichheits- oder IN-Prädikate¶
Der Suchoptimierungsdienst kann die Leistung von Abfragen verbessern, die Folgendes verwenden:
Teilzeichenfolgen und reguläre Ausdrücke¶
Der Suchoptimierungsdienst kann die Leistung von Abfragen mit Prädikaten verbessern, die in Textdaten oder semistrukturierten Daten nach Teilzeichenfolgen suchen oder reguläre Ausdrücke verwenden. (Weitere Informationen zur Funktionsweise der Suche nach Teilzeichenfolgen in semistrukturierten Daten finden Sie unter Suche nach Teilzeichenfolgen in VARIANT-Typen.)
Diese Fähigkeit umfasst Prädikate, die Folgendes verwenden:
SPLIT_PART (in Gleichheitsprädikaten)
Bemerkung
Sie müssen dieses Feature für bestimmte Spalten mit der ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION aktivieren. (Wenn Sie die Suchoptimierung auf Tabellenebene aktivieren, wird die Suche nach Teilzeichenfolgen nicht optimiert.) Beispiel:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(mycol);
Suchläufe nach Teilzeichenfolgen werden nur optimiert, wenn Sie die ON-Klausel verwenden.
Der Suchoptimierungsdienst kann die Leistung bei der Suche nach Teilzeichenfolgen verbessern, die 5 oder mehr Zeichen lang sind. (Selektivere Teilzeichenfolgen können zu einer besseren Leistung führen.) Der Suchoptimierungsdienst verwendet für das folgende Prädikat keine Suchzugriffspfade, da die Teilzeichenfolge kürzer als 5 Zeichen ist:
LIKE '%TEST%'
Für das folgende Prädikat kann der Suchoptimierungsdienst die Abfrage optimieren, indem für die Suche nach den Teilzeichenfolgen für SEARCH
und OPTIMIZED
Suchzugriffspfade verwendet werden. Die Suchzugriffspfade werden jedoch nicht für IS
verwendet, da die Teilzeichenfolge kürzer als 5 Zeichen ist.
LIKE '%SEARCH%IS%OPTIMIZED%'
Bei Abfragen, die RLIKE, REGEXP und REGEXP_LIKE verwenden, gilt Folgendes:
Das Argument
subject
muss eine TEXT-Spalte in einer Tabelle sein, bei der die Suchoptimierung aktiviert ist.Das Argument
pattern
muss eine Zeichenfolgenkonstante sein.
Bei regulären Ausdrücken funktioniert der Suchoptimierungsdienst in folgenden Fällen am besten:
Das Muster enthält mindestens ein Teilzeichenfolgen-Literal, das 5 oder mehr Zeichen lang ist.
Das Muster gibt an, dass die Teilzeichenfolge mindestens einmal vorkommen muss.
Das folgende Muster legt zum Beispiel fest, dass string
ein oder mehrere Male im „subject“ vorkommen soll:
RLIKE '(string)+'
Der Suchoptimierungsdienst kann die Leistung von Abfragen mit den folgenden Mustern verbessern, da jedes Prädikat angibt, dass eine Teilzeichenfolge aus 5 oder mehr Zeichen mindestens einmal vorkommen muss. (Beachten Sie, dass im ersten Beispiel eine Zeichenfolgenkonstanten in Dollarzeichen verwendet wird, damit die Backslash-Zeichen nicht mit Escapezeichen umschlossen werden müssen.)
RLIKE $$.*email=[\w\.]+@snowflake\.com.*$$RLIKE '.*country=(Germany|France|Spain).*'RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
Im Gegensatz dazu verwendet der Suchoptimierungsdienst keine Suchzugriffspfade bei Abfragen mit den folgenden Mustern:
Muster ohne Teilzeichenfolgen:
RLIKE '.*[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
Muster, die nur Teilzeichenfolgen enthalten, die weniger als 5 Zeichen enthalten:
RLIKE '.*tel=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
Muster, die den Optionsoperator verwenden, wobei eine Option eine Teilzeichenfolge ist, die weniger als 5 Zeichen enthält:
RLIKE '.*(option1|option2|opt3).*'
Muster, bei denen die Teilzeichenfolge optional ist:
RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
Auch wenn die Teilzeichenfolgen-Literale weniger als 5 Zeichen enthalten, kann der Suchoptimierungsdienst die Abfrageleistung verbessern, wenn die Erweiterung des regulären Ausdrucks ein Teilzeichenfolgen-Literal ergibt, das 5 Zeichen oder mehr enthält.
Betrachten Sie beispielsweise folgendes Muster:
.*st=(CA|AZ|NV).*(-->){2,4}.*
Bei diesem Beispiel ist Folgendes zu beachten:
Obwohl die Teilzeichenfolgen-Literale (z. B.
st=
,CA
usw.) kürzer als 5 Zeichen sind, erkennt der Suchoptimierungsdienst, dass die Teilzeichenfolgenst=CA
,st=AZ
oderst=NV
(die jeweils 5 Zeichen lang sind) im Text vorkommen müssen.Auch wenn das Teilzeichenfolgen-Literal
-->
kürzer als 5 Zeichen ist, stellt der Suchoptimierungsdienst fest, dass die Teilzeichenfolge-->-->
(die länger als 5 Zeichen ist) im Text auftreten muss.
Der Suchoptimierungsdienst kann Suchzugriffspfade verwenden, um diese Teilzeichenfolgen abzugleichen, was die Leistung der Abfrage verbessern kann.
Felder in VARIANT-Spalten¶
Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen und Abfragen mit Teilzeichenfolgen auf semistrukturierten Daten von Snowflake-Tabellen (Daten in VARIANT-, OBJECT- und ARRAY-Spalten) verbessern.
Wenn bei Spalten einer Tabelle die VARIANT-Unterstützung für den Suchoptimierungsdienst konfiguriert ist, schließt der Suchoptimierungsdienst automatisch VARIANT-, OBJECT- und ARRAY-Spalten in einen Suchzugriffspfad ein. Dies gilt sogar für Spalten, deren Struktur tief verschachtelt ist und sich häufig ändert. Sie können die Suchoptimierung auch für bestimmte Felder innerhalb einer semistrukturierten Spalte aktivieren.
Bemerkung
Sie müssen dieses Feature explizit für bestimmte Spalten oder bestimmte Felder in Spalten aktivieren, indem Sie die ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION verwenden. (Wenn Sie die Suchoptimierung auf Tabellenebene aktivieren, wird sie nicht für VARIANT-Spalten aktiviert.) Beispiel:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);
Abfragen auf VARIANT-Spalten werden nur optimiert, wenn Sie die ON-Klausel verwenden.
In den nächsten Abschnitten finden Sie weitere Details dazu:
Unterstützte Datentypen für Konstanten und Umwandlungen in Prädikaten für VARIANT-Typen
Aktuelle Einschränkungen bei der Unterstützung von VARIANT-Typen
Unterstützte Datentypen für Konstanten und Umwandlungen in Prädikaten für VARIANT-Typen¶
Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen auf semistrukturierten Daten verbessern, bei denen die folgenden Typen für Konstanten und für die implizite oder explizite Umwandlung von Elementen verwendet werden:
FIXED (einschließlich Umwandlungen, die eine gültige Genauigkeit und Skalierung angeben)
INTEGER
TEXT
DATE (einschließlich Umwandlungen, die eine Skalierung angeben)
TIME (einschließlich Umwandlungen, die eine Skalierung angeben)
TIMESTAMP TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (einschließlich Umwandlungen, die eine Skalierung angeben)
Der Suchoptimierungsdienst unterstützt die Umwandlung von Typen mit:
Unterstützung der Umwandlung von VARIANT-Werten in TEXT¶
Der Suchoptimierungsdienst kann auch die Leistung von Punktsuchabfragen verbessern, bei denen VARIANT-Spalten in TEXT umgewandelt und mit in TEXT umgewandelten Konstanten verglichen werden.
Angenommen, src
ist eine VARIANT-Spalte, die Boolesche, Datums- und Zeit-Werte enthält, die in VARIANT konvertiert worden sind:
create or replace TABLE test_table
(
ID INTEGER,
SRC VARIANT
);
insert into test_table select 1, to_variant('true'::boolean); -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date); -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time); -- TIME
Bei dieser Tabelle kann der Suchoptimierungsdienst die folgenden Abfragen verbessern, bei denen die VARIANT-Spalte in TEXT umgewandelt und die Spalte mit Zeichenfolgenkonstante verglichen wird:
select * from test_table where src::TEXT = 'true';
select * from test_table where src::TEXT = '2020-01-09';
select * from test_table where src::TEXT = '01:02:03.899213';
Unterstützte Prädikate für Punktsuche auf VARIANT-Typen¶
Der Suchoptimierungsdienst kann Punktsuchabfragen mit den unten aufgeführten Typen von Prädikaten verbessern. In den folgenden Beispielen ist src
die VARIANT-Spalte und path_to_variant_field
ist ein Pfad zu einem Feld in der VARIANT-Spalte.
Gleichheitsprädikate im folgenden Format:
where path_to_variant_field[::target_data_type] = constant
target_data_type
(falls angegeben) und der Datentyp vonconstant
müssen einen der oben aufgeführten unterstützten Typen aufweisen.Beachten Sie, dass
::
nur ein Beispiel für eine der unterstützten Möglichkeiten zur Umwandlung des Werts in einen bestimmten Typ ist.Der Suchoptimierungsdienst unterstützt beispielsweise Folgendes:
Abgleich eines Elements mit einer NUMBER-Konstante ohne explizite Umwandlung des Elements.
where src:person.age = 42;
Explizite Umwandlung eines Elements in NUMBER mit einer bestimmten Genauigkeit und Skalierung.
where src:location.temperature::NUMBER(8, 6) = 23.456789;
Abgleich eines Elements mit einer TEXT-Konstante ohne explizite Umwandlung des Elements.
where src:sender_info.ip_address = '123.123.123.123';
Explizite Umwandlung eines Elements in TEXT.
where src:salesperson.name::TEXT = 'John Appleseed';
Explizite Umwandlung eines Elements in DATE.
where src:events.date::DATE = '2021-03-26';
Explizite Umwandlung eines Elements in TIME mit einer bestimmten Skalierung.
where src:events.time_info::TIME(6) = '01:02:03.456789';
Explizite Umwandlung eines Elements in TIMESTAMP mit einer bestimmten Skalierung.
where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
Prädikate, die ARRAY-Funktionen verwenden, wie z. B.:
where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)
constant
darf nicht NULL sein, und der Datentyp vonconstant
muss einen der oben aufgeführten unterstützten Typen aufweisen.Beachten Sie, dass
::
nur ein Beispiel für eine der unterstützten Möglichkeiten zur Umwandlung des Werts in einen bestimmten Typ ist.Beispiel:
where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)
Der Datentyp jeder Konstanten (
constant_1
,constant_2
usw.) muss einer der oben aufgeführten unterstützten Typen sein. Das konstruierte ARRAY kann NULL-Konstanten enthalten.Beispiel:
where ARRAYS_OVERLAP( ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
Die folgenden Prädikate, die auf NULL-Werte prüfen:
where IS_NULL_VALUE(path_to_variant_field)
Beachten Sie, dass IS_NULL_VALUE für JSON-Nullwerte und nicht für SQL-NULL-Werte gilt.
where path_to_variant_field IS NOT NULL
where variant_column IS NULL
wobei sich
variant_column
auf die Spalte und nicht auf einen Pfad zu einem Element in den semistrukturierten Daten bezieht.So unterstützt der Suchoptimierungsdienst beispielsweise die Verwendung der VARIANT-Spalte
src
, nicht aber den Pfad zum Feldsrc:person.age
in dieser VARIANT-Spalte.
Suche nach Teilzeichenfolgen in VARIANT-Typen¶
Der Suchoptimierungsdienst kann Platzhalter oder reguläre Ausdrücke in semistrukturierten Spalten (einschließlich ARRAY-, OBJECT- und VARIANT-Spalten) oder in Feldern in solchen Spalten optimieren. Dazu zählen Prädikate, die Folgendes verwenden:
SPLIT_PART (in Gleichheitsprädikaten)
Sie können die Suchoptimierung nach Teilzeichenfolgen für eine Spalte oder für mehrere einzelne Felder innerhalb einer Spalte aktivieren. Die folgende Anweisung aktiviert beispielsweise die Suchoptimierung nach Teilzeichenfolgen für ein verschachteltes Feld in einer Spalte.
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
Sobald der Suchzugriffspfad erstellt wurde, kann die folgende Abfrage optimiert werden:
SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';
Die folgenden Abfragen werden jedoch nicht optimiert, da sie sich nicht auf das Feld beziehen, das bei der Aktivierung der Suchoptimierung angegeben wurde.
SELECT * FROM test_table WHERE col2:name LIKE '%john%doe%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';
Sie können mehrere Felder angeben, die optimiert werden sollen. Hier ist die Suchoptimierung für zwei bestimmte Felder in der Spalte col2
aktiviert.
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
Wenn Sie die Suchoptimierung für ein bestimmtes Feld aktivieren, wird sie auch für alle Unterfelder aktiviert. Die zweite ALTER TABLE-Anweisung unten ist redundant, da die erste Anweisung die Suchoptimierung für das gesamte data
-Feld ermöglicht, einschließlich des verschachtelten search
-Feldes.
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
Wenn Sie die Suchoptimierung für eine ganze Spalte aktivieren, werden alle Suchläufe nach Teilzeichenfolgen in dieser Spalte optimiert, einschließlich für Felder, die in beliebiger Tiefe in der Spalte verschachtelt sind.
Wie Konstanten für Suche von VARIANT-Teilzeichenfolgen ausgewertet werden
Bei der Auswertung der konstanten Zeichenfolge in einer Abfrage (z. B. LIKE 'constant_string'
) teilt der Suchoptimierungsdienst die Zeichenfolge in Token auf und verwendet dabei die folgenden Zeichen als Trennzeichen:
Eckige Klammern (
[
und]
)Geschweifte Klammern (
{
und}
)Doppelpunkte (
:
)Kommas (
,
)Doppelte Anführungszeichen (
"
)
Nach der Aufteilung der Zeichenfolge in Token berücksichtigt der Suchoptimierungsdienst nur Token, die mindestens 5 Zeichen lang sind.
Beispiel für ein Prädikat |
Wie der Suchoptimierungsdienst die Abfrage verarbeitet |
---|---|
|
Der Suchoptimierungsdienst verwendet für das folgende Prädikat keine Suchzugriffspfade, da die Teilzeichenfolge kürzer als 5 Zeichen ist: |
|
Der Suchoptimierungsdienst kann diese Abfrage optimieren, indem er Suchzugriffspfade für die Suche nach |
|
Der Suchoptimierungsdienst kann die Abfrage optimieren, indem Suchzugriffspfade für die Suche nach |
|
Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in |
|
Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in die Token |
|
Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in die Token |
Aktuelle Einschränkungen bei der Unterstützung von VARIANT-Typen¶
Die Unterstützung von VARIANT-Typen im Suchoptimierungsdienst unterliegt derzeit den folgenden Einschränkungen:
Prädikate, die XMLGET verwenden, werden nicht unterstützt.
Prädikate der Form
variant_field IS NULL
werden nicht unterstützt.Prädikate, bei denen die Konstanten die Ergebnisse von skalaren Unterabfragen sind, werden nicht unterstützt.
Prädikate, die Pfade zu Elementen angeben, die Unterelemente enthalten, werden nicht unterstützt.
Die aktuell für den Suchoptimierungsdienst geltenden Einschränkungen gelten auch für dieses Feature.
Geodatenfunktionen¶
Der Suchoptimierungsdienst kann die Leistung von Abfragen mit Prädikaten verbessern, bei denen Geodatenfunktionen mit GEOGRAPHY-Objekten verwendet werden.
Bemerkung
Sie müssen dieses Feature für bestimmte Spalten mit der ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION aktivieren. Beispiel:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
Abfragen, die Geodatenfunktionen verwenden, werden nicht verbessert, wenn Sie die ON-Klausel weglassen.
Weitere Details dazu finden Sie in den folgenden Abschnitten:
Bemerkung
GEOMETRY-Objekte werden noch nicht unterstützt.
Unterstützte Prädikate mit Geodatenfunktionen¶
Für Abfragen mit Prädikaten, die die folgenden Funktionen verwenden:
Der Suchoptimierungsdienst kann die Leistung verbessern, wenn Folgendes gilt:
Ein Eingabeausdruck ist eine GEOGRAPHY-Spalte in einer Tabelle.
Der andere Eingabeausdruck ist eine GEOGRAPHY-Konstante (die durch eine Konvertierung oder eine Konstruktorfunktion erstellt wird).
Für ST_DWITHIN ist das Distanzargument eine nicht negative REAL-Konstante.
Beachten Sie, dass für dieses Feature die gleichen Einschränkungen gelten wie für den Suchoptimierungsdienst.
Weitere Hinweise zur Performance¶
Da der Suchoptimierungsdienst für Prädikate konzipiert ist, die sehr selektiv sind, und da Prädikate nach der Nähe zwischen Geodatenobjekten filtern, kann das Clustering von Geodatenobjekten nach Nähe in der Tabelle zu einer besseren Leistung führen. Sie können Ihre Daten gruppieren, indem Sie entweder die Sortierreihenfolge beim Laden der Daten angeben oder Automatic Clustering verwenden, je nachdem, ob sich die Basistabelle häufig ändert:
- Laden von vorsortierten Daten
Wenn sich die Daten in Ihrer Basistabelle selten ändern, können Sie beim Laden der Daten die Sortierreihenfolge festlegen. Anschließend können Sie die Suchoptimierung für die Spalte GEOGRAPHY aktivieren. Beispiel:
CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom); ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Nach jeder größeren Änderung Ihrer Stammdaten können Sie die Daten manuell neu sortieren.
- Automatic Clustering
Wenn Ihre Basistabelle häufig aktualisiert wird, können Sie den Befehl ALTER TABLE … CLUSTER BY … verwenden, um Automatic Clustering zu aktivieren, damit die Tabelle bei Änderungen automatisch neu geclustert wird.
Das folgende Beispiel fügt eine neue Spalte
geom_geohash
vom Typ VARCHAR hinzu und speichert den Geohash- oder H3-Index der GEOGRAPHY-Spaltegeom
in dieser neuen Spalte. Anschließend wird Automatic Clustering aktiviert, wobei die neue Spalte der Clusterschlüssel ist. Bei diesem Ansatz werden die Teile der Tabelle, die sich ändern, automatisch neu geclustert.CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table; ALTER TABLE new_table CLUSTER BY (geom_geohash); ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Beispiele für die Verwendung von Geodatenfunktionen¶
Mit den folgenden Anweisungen wird die Tabelle erstellt und konfiguriert, die in den Beispielen dieses Abschnitts verwendet wird. Die letzte Anweisung verwendet die ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION, um die Suchoptimierung für die GEOGRAPHY-Spalte g1
hinzuzufügen.
CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
(1, 'POINT(-122.35 37.55)'),
(2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
(3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);
Beispiele für unterstützte Prädikate¶
Die folgende Abfrage ist ein Beispiel für eine vom Suchoptimierungsdienst unterstützte Abfrage. Der Suchoptimierungsdienst kann Suchzugriffspfade verwenden, um die Leistung dieser Abfrage zu verbessern:
SELECT id FROM geospatial_table WHERE
ST_INTERSECTS(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
Im Folgenden finden Sie Beispiele für zusätzliche Prädikate, die vom Suchoptimierungsdienst unterstützt werden:
...
ST_INTERSECTS(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_CONTAINS(
g1,
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
...
ST_WITHIN(
TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
g1)
...
ST_WITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_COVERS(
TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
g1)
...
ST_COVERS(
g1,
TO_GEOGRAPHY('POINT(0 0)'))
...
ST_COVEREDBY(
TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
g1)
...
ST_COVEREDBY(
g1,
TO_GEOGRAPHY('POINT(-122.35 37.55)'))
...
ST_DWITHIN(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1,
100000)
...
ST_DWITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
100000)
Beispiele für die Konstruktion von GEOGRAPHY-Konstanten¶
Im Folgenden sind Beispiele für Prädikate aufgeführt, die verschiedene Umwandlungs- und Konstruktorfunktionen für die GEOGRAPHY-Konstante verwenden.
...
ST_INTERSECTS(
g1,
ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_WITHIN(
ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
g1)
...
ST_COVERS(
g1,
ST_MAKEPOINT(0.2, 0.8))
...
ST_INTERSECTS(
g1,
ST_MAKELINE(
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
TO_GEOGRAPHY('POINT(0.8 0.2)')))
...
ST_INTERSECTS(
ST_POLYGON(
TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
g1)
...
ST_WITHIN(
g1,
TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
...
ST_COVERS(
g1,
ST_GEOGPOINTFROMGEOHASH('s00'))
Konjunktionen von unterstützten Prädikaten (AND)¶
Bei Abfragen, die Konjunktionen von Prädikaten (d. h. AND) verwenden, kann die Abfrageleistung durch Suchoptimierung verbessert werden, wenn eines der Prädikate die oben genannten Bedingungen erfüllt.
Angenommen, eine Abfrage ist wie folgt definiert:
where condition_x and condition_y
Die Suchoptimierung kann die Leistung verbessern, wenn eine der Bedingungen einige Zeilen separat zurückgibt (d. h. condition_x
gibt einige Zeilen zurück oder condition_y
gibt einige Zeilen zurück).
Wenn condition_x
einige Zeilen zurückgibt, aber condition_y
viele Zeilen zurückgibt, kann die Abfrageleistung von der Suchoptimierung profitieren.
Siehe diese zusätzlichen Beispiele.
Disjunktionen von unterstützten Prädikaten (OR)¶
Bei Abfragen, die Disjunktionen von Prädikaten (d. h. OR) verwenden, kann die Abfrageleistung durch Suchoptimierung verbessert werden, wenn alle Prädikate die oben genannten Bedingungen erfüllt.
Angenommen, eine Abfrage ist wie folgt definiert:
where condition_x or condition_y
Die Suchoptimierung kann die Leistung verbessern, wenn jede Bedingung separat einige Zeilen zurückgibt (d. h. condition_x
gibt einige Zeilen zurück und condition_y
gibt einige Zeilen zurück).
Wenn condition_x
einige Zeilen zurückgibt, aber condition_y
viele Zeilen zurückgibt, profitiert die Abfrageleistung nicht von der Suchoptimierung.
Bei Disjunktionen ist jedes isolierte Prädikat für sich nicht entscheidend für die Abfrage. Die anderen Prädikate müssen ausgewertet werden, bevor festgestellt werden kann, ob die Suchoptimierung die Leistung verbessern kann.
Joins¶
Der Suchoptimierungsdienst verbessert die Leistung von Joins nicht direkt. Es kann jedoch die Leistung beim Filtern von Zeilen aus beiden Tabellen vor dem Join verbessern, wenn für die Tabelle die Suchoptimierung aktiviert ist und das Prädikat selektiv ist.
Für beide Tabellen muss die Suchoptimierung nicht aktiviert sein. Die Entscheidung für die Suchoptimierung wird für jede Tabelle unabhängig getroffen.
Ansichten¶
Der Suchoptimierungsdienst kann indirekt die Leistung von Ansichten (inkl. sichere Ansichten) verbessern. Wenn für die Basistabelle einer Ansicht die Suchoptimierung aktiviert ist und die Abfrage ein selektives Prädikat für diese Tabelle verwendet, kann der Suchoptimierungsdienst die Leistung beim Filtern von Zeilen verbessern.
Die Suchoptimierung muss nicht für alle Tabellen in der Ansicht aktiviert sein. Die Suchoptimierung wird für jede Tabelle unabhängig durchgeführt.
Tabellen mit Maskierungsrichtlinien und Zeilenzugriffsrichtlinien¶
Der Suchoptimierungsdienst kann die Performance von Abfragen auf Tabellen verbessern, die Maskierungsrichtlinien und Zeilenzugriffsrichtlinien verwenden.
Bemerkung
Wenn die Suchoptimierung aktiviert ist, kann ein Benutzer, der einen Wert aufgrund einer Maskierungsrichtlinie oder einer Zeilenzugriffsrichtlinie nicht sehen kann, möglicherweise mit größerer Sicherheit ableiten, ob dieser Wert existiert oder nicht. Mit oder ohne Suchoptimierung können Unterschiede in der Abfragelatenz Hinweise auf das Vorhandensein oder Nichtvorhandensein von Daten liefern, die durch eine Richtlinie eingeschränkt sind, wobei dieser Effekt in einigen Situationen durch die Suchoptimierung noch verstärkt werden kann.
Angenommen, eine Zeilenzugriffsrichtlinie verhindert, dass ein Benutzer auf Zeilen mit country = US
zugreift, aber die Daten enthalten keine Zeilen mit country = US
. Angenommen, die Suchoptimierung ist für die Spalte country
aktiviert und der Benutzer führt eine Abfrage mit WHERE country = US
aus. Die Abfrage gibt wie erwartet leere Ergebnisse zurück, aber die Abfrage wird mit der Suchoptimierung möglicherweise schneller ausgeführt als ohne. In diesem Fall kann der Benutzer basierend auf der für die Ausführung der Abfrage benötigten Zeit schließen, dass die Daten keine Zeile mit country = US
enthalten.
Vom Suchoptimierungsdienst nicht unterstützte Abfragen¶
Der Suchoptimierungsdienst unterstützt Folgendes nicht:
Externe Tabellen
Materialisierte Ansichten
Spalten, die mit einer COLLATE-Klausel definiert sind
Verketten von Spalten
Analytische Ausdrücke
Umwandlungen auf Tabellenspalten (außer für Festkommazahlen, die in Zeichenfolgen umgewandelt werden).
Obwohl die Suchoptimierung Prädikate mit impliziten und expliziten Umwandlungen auf Konstantenwerten unterstützt, werden keine Prädikate unterstützt, die Werte in der tatsächlichen Tabellenspalte umwandeln (außer für Umwandlungen von INTEGER und NUMBER in VARCHAR).
Die folgenden Prädikate werden beispielsweise unterstützt, da sie die implizite und explizite Umwandlung von Konstantenwerten verwenden (nicht Werten in der Tabellenspalte):
-- Supported predicate -- (where the string '2020-01-01' is implicitly cast to a date) WHERE timestamp1 = '2020-01-01'; -- Supported predicate -- (where the string '2020-01-01' is explicitly cast to a date) WHERE timestamp1 = '2020-01-01'::date;
Das folgende Prädikat wird nicht unterstützt, da es eine Umwandlung von Werten in der Tabellenspalte verwendet:
-- Unsupported predicate -- (where values in a VARCHAR column are cast to DATE) WHERE to_date(varchar_column) = '2020-01-01';
Der Suchoptimierungsdienst berücksichtigt die ursprünglichen Spaltenwerte und nicht die Werte nach der Umwandlung. Daher wird der Suchoptimierungsdienst nicht für Abfragen mit diesen Prädikaten verwendet.
Wie bereits erwähnt, ist die Ausnahme von dieser Regel das Umwandeln von NUMBER- oder INTEGER-Werten in VARCHAR-Werte in der Tabellenspalte. Der Suchoptimierungsdienst unterstützt diesen Typ von Prädikat:
-- Supported predicate -- (where values in a numeric column are cast to a string) WHERE cast(numeric_column as varchar) = '2'
Die Suchoptimierung verbessert nicht die Leistung von Abfragen, die Time Travel verwenden, da die Suchoptimierung nur für aktive Daten funktioniert.
Konfigurieren der Suchoptimierung für eine Tabelle¶
Bemerkung
Das Hinzufügen einer Suchoptimierung zu einer großen Tabelle (mit Terabytes (TB) oder mehr an Daten) kann zu einem sofortigen Anstieg des Credit-Verbrauchs über einen kurzen Zeitraum führen.
Wenn Sie einer Tabelle eine Suchoptimierung hinzufügen, beginnt der Wartungsdienst sofort im Hintergrund mit dem Aufbau der Suchzugriffspfade für die Tabelle. Wenn die Tabelle groß ist, kann der Wartungsdienst diese Arbeit massiv parallelisieren, was in kurzer Zeit zu erhöhten Kosten führen kann.
Bevor Sie die Suchoptimierung einer großen Tabelle hinzufügen, führen Sie eine Kostenschätzung durch, damit Sie wissen, was Sie zu erwarten haben.
Um die Suchoptimierung zu einer Tabelle hinzuzufügen, verwenden Sie den Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION mit oder ohne ON-Klausel.
Bei Verwendung des Befehls ohne ON-Klausel werden Suchzugriffspfade eingerichtet, um mithilfe der EQUALITY-Suchmethode die Performance von Abfragen für alle Spalten zu verbessern, die die unterstützten Datentypen verwenden.
Wenn Sie die Leistung für andere Abfragetypen verbessern möchten oder mehr Kontrolle darüber benötigen, welche Spalten für die Suchoptimierung konfiguriert werden, verwenden Sie die ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION.
In der ON-Klausel in ADD SEARCH OPTIMIZATION geben Sie an, welche Spalten für die Suchoptimierung aktiviert werden sollen. Wenn Sie die Suchoptimierung für eine bestimmte Spalte aktivieren, können Sie auch eine Suchmethode angeben (z. B. EQUALITY für die Gleichheits- und IN-Suche, GEO für die GEOGRAPHY-Suche und SUBSTRING für die Suche nach Teilzeichenfolgen).
Um die Kosten für die Suchoptimierung zu verwalten, können Sie die Suchoptimierung von bestimmten Spalten entfernen, für die keine Suchoptimierung erforderlich ist.
In den nächsten Abschnitten wird erläutert, wie Sie die Suchoptimierung für eine Tabelle konfigurieren:
Konfigurieren der Suchoptimierung für bestimmte Spalten¶
Um die Suchoptimierung für eine bestimmte Spalte zu konfigurieren, verwenden Sie den Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION mit der ON-Klausel.
Bemerkung
Zum Ausführen dieses Befehls müssen Sie die Rolle verwenden, die über Berechtigungen zum Hinzufügen der Suchoptimierung zu der Tabelle verfügt.
Die ON-Klausel gibt an, dass Sie die Suchoptimierung für bestimmte Spalten konfigurieren möchten. Weitere Informationen zur Syntax finden Sie im Abschnitt zu ALTER TABLE … ADD SEARCH OPTIMIZATION.
Bemerkung
Wenn Sie nur die Suchoptimierung für Gleichheits- und IN-Prädikate auf allen anwendbaren Spalten der Tabelle anwenden möchten, finden Sie unter Verwalten der Suchoptimierung für die gesamte Tabelle weitere Informationen.
Nachdem Sie diesen Befehl ausgeführt haben, können Sie überprüfen, ob die Spalten für die Suchoptimierung konfiguriert wurden.
Die nächsten Abschnitte enthalten Beispiele, die zeigen, wie Sie die Konfiguration für die Suchoptimierung angeben:
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für bestimmte Spalten
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für alle anwendbaren Spalten
Beispiel: Unterstützung unterschiedlicher Typen von Prädikaten
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für ein VARIANT-Feld
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für bestimmte Spalten¶
Um die Suche mit Gleichheitsprädikaten für die Spalten c1
, c2
und c3
in der Tabelle t1
zu optimieren, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3);
Sie können dieselbe Suchmethode auch mehrmals in der ON-Klausel angeben:
-- This statement is equivalent to the previous statement.
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für alle anwendbaren Spalten¶
Um die Suche mit Gleichheitsprädikaten für die alle anwendbaren Spalten der Tabelle zu optimieren, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(*);
Beachten Sie Folgendes:
Wie unter Beschreibung der Syntax für Suchmethode und Ziel erläutert, können Sie für eine bestimmte Methode kein Sternchen und keine spezifischen Spalten angeben.
Obwohl durch Weglassen der ON-Klausel auch die Suchoptimierung für Gleichheits- und IN-Prädikate auf allen anwendbaren Spalten der Tabelle konfiguriert wird, gibt es Unterschiede zwischen Angeben und Weglassen der ON-Klausel. Siehe Verwalten der Suchoptimierung für die gesamte Tabelle.
Beispiel: Unterstützung unterschiedlicher Typen von Prädikaten¶
Um die Suche mit Gleichheitsprädikaten für die Spalten c1
und c2
und die Teilzeichenfolge-Suche für die Spalte c3
zu optimieren, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2), SUBSTRING(c3);
Beispiel: Unterstützung von Gleichheits- und IN-Prädikaten für ein VARIANT-Feld¶
Um die Suche mit Gleichheitsprädikaten auf dem VARIANT-Feld uuid
zu optimieren, das im Feld user
der VARIANT-Spalte c4
verschachtelt ist, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);
Beispiel: Unterstützung von Geodatenfunktionen¶
Um die Suche mit Prädikaten zu optimieren, die Geodatenfunktionen mit GEOGRAPHY-Objekten in der Spalte c1
verwenden, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON GEO(c1);
Verwalten der Suchoptimierung für die gesamte Tabelle¶
Wenn Sie nur EQUALITY für alle Spalten der unterstützten Datentypen (außer VARIANT und GEOGRAPHY) angeben möchten, verwenden Sie den Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION ohne ON-Klausel.
Bemerkung
Zum Ausführen dieses Befehls müssen Sie die Rolle verwenden, die über Berechtigungen zum Hinzufügen der Suchoptimierung zu der Tabelle verfügt.
Beispiel:
alter table test_table add search optimization;
Weitere Informationen dazu finden Sie im Abschnitt zur Suchoptimierung in ALTER TABLE.
Nachdem Sie diesen Befehl ausgeführt haben, können Sie überprüfen, ob die Spalten für die Suchoptimierung konfiguriert wurden.
Auswirkung auf nachträglich hinzugefügte Spalten¶
Nachdem Sie den Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION ohne ON-Klausel ausgeführt haben, werden alle Spalten, die der Tabelle später hinzugefügt werden, ebenfalls für EQUALITY konfiguriert.
Wenn Sie ALTER TABLE … { ADD | DROP } SEARCH OPTIMIZATION auf derselben Tabelle mit ON-Klausel ausführen, werden alle Spalten, die der Tabelle später hinzugefügt werden, nicht automatisch für EQUALITY konfiguriert. Sie müssen ALTER TABLE … ADD SEARCH OPTIMIZATION ON … ausführen, um diese neu hinzugefügten Spalten für EQUALITY zu konfigurieren.
Überprüfen, ob Tabelle für Suchoptimierung konfiguriert ist¶
So überprüfen Sie, ob die Tabelle und deren Spalten für die Suchoptimierung konfiguriert wurden:
Zeigen Sie die Konfiguration der Suchoptimierung für die Tabelle und deren Spalten an.
Führen Sie den SHOW TABLES-Befehl aus, um zu überprüfen, ob die Suchoptimierung hinzugefügt wurde, und um festzustellen, in welchem Umfang die Tabelle optimiert wurde:
Beispiel:
SHOW TABLES LIKE '%test_table%';
In der Ausgabe dieses Befehls:
Prüfen Sie, ob SEARCH_OPTIMIZATION auf
ON
gesetzt ist, was darauf hinweist, dass die Suchoptimierung hinzugefügt wurde.Überprüfen Sie den Wert von SEARCH_OPTIMIZATION_PROGRESS. Dieser gibt den Prozentsatz der Tabelle an, der bisher optimiert wurde.
Wenn die Suchoptimierung zum ersten Mal zu einer Tabelle hinzugefügt wird, zeigen sich die Leistungsvorteile nicht sofort. Der Suchoptimierungsdienst beginnt mit dem Auffüllen von Daten im Hintergrund. Die Vorteile zeigen sich nach und nach, bis die Wartung den aktuellen Status der Tabelle erreicht.
Bevor Sie eine Abfrage ausführen, um zu überprüfen, ob die Suchoptimierung funktioniert, warten Sie, bis dieser Wert anzeigt, dass die Tabelle vollständig optimiert wurde.
Führen Sie eine Abfrage aus, um zu überprüfen, ob die Suchoptimierung funktioniert.
Beachten Sie, dass das Snowflake-Optimierungsprogramm automatisch entscheidet, ob der Suchoptimierungsdienst für eine bestimmte Abfrage verwendet wird. Benutzer können nicht steuern, für welche Abfragen die Suchoptimierung verwendet wird.
Wählen Sie eine Abfrage aus, die der Suchoptimierungsdienst optimieren soll. Siehe Identifizieren der Tabellen und Spalten, die von der Suchoptimierung profitieren.
Zeigen Sie auf der Web-UI den Abfrageplan für diese Abfrage an, und stellen Sie sicher, dass der Abfrageknoten „Search Optimization Access“-Teil des Abfrageplans ist.
Anzeigen der Suchoptimierungskonfiguration für eine Tabelle¶
Um die Konfiguration der Suchoptimierung für eine Tabelle anzuzeigen, verwenden Sie den Befehl DESCRIBE SEARCH OPTIMIZATION.
Angenommen, Sie führen die folgende Anweisung aus, um die Suchoptimierung für eine Spalte zu konfigurieren:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1);
Das Ausführen von DESCRIBE SEARCH OPTIMIZATION ergibt die folgende Ausgabe:
DESCRIBE SEARCH OPTIMIZATION ON t1;
+---------------+----------+--------+------------------+--------+
| expression_id | method | target | target_data_type | active |
+---------------+----------+--------+------------------+--------+
| 1 | EQUALITY | C1 | NUMBER(38,0) | true |
+---------------+----------+--------+------------------+--------+
Verwenden von suchoptimierten Tabellen¶
Wenn Sie mit einer Tabelle arbeiten, die die Suchoptimierung verwendet, müssen Sie die Auswirkungen auf den Suchoptimierungsdienst kennen.
Ändern der Tabelle¶
Ein Suchzugriffspfad wird ungültig, wenn der Standardwert einer Spalte geändert wird.
Um die Suchoptimierung erneut verwenden zu können, nachdem ein Suchzugriffspfad ungültig geworden ist, müssen Sie die SEARCH OPTIMIZATION-Eigenschaft für die Tabelle löschen und dann die SEARCH OPTIMIZATION-Eigenschaft wieder zur Tabelle hinzufügen.
Ein Suchzugriffspfad bleibt gültig, wenn Sie eine Spalte hinzufügen, löschen oder umbenennen:
Wenn Sie die Suchoptimierung für eine gesamte Tabelle ohne Angabe bestimmter Spalten aktiviert haben und eine Spalte zu der Tabelle hinzufügen, wird die neue Spalte automatisch zum Suchzugriffspfad hinzugefügt. Wenn Sie jedoch beim Aktivieren der Suchoptimierung für eine Spalte die ON-Klausel verwendet haben, werden neue Spalten nicht automatisch hinzugefügt.
Wenn Sie eine Spalte aus einer Tabelle löschen, wird die gelöschte Spalte automatisch aus dem Suchzugriffspfad entfernt.
Das Umbenennen einer Spalte erfordert keine Änderungen am Suchzugriffspfad.
Wenn Sie eine Tabelle löschen, werden auch die SEARCH OPTIMIZATION-Eigenschaft und die Suchzugriffspfade gelöscht. Beachten Sie Folgendes:
Bei Rücknahme des Löschens der Tabelle wird auch die Suchoptimierung als Eigenschaft der Tabelle wiederhergestellt.
Wenn Sie eine Tabelle löschen, hat der Suchzugriffspfad dieselbe Datenaufbewahrungsfrist wie die Tabelle.
Wenn Sie die SEARCH OPTIMIZATION-Eigenschaft aus der Tabelle löschen, wird auch der Suchzugriffspfad entfernt. Wenn Sie die SEARCH OPTIMIZATION-Eigenschaft wieder zur Tabelle hinzufügen, muss der Wartungsdienst den Suchzugriffspfad neu erstellen. (Es gibt keine Möglichkeit, das Löschen der Eigenschaft zurückzunehmen.)
Klonen von Tabelle, Schema oder Datenbank¶
Wenn Sie eine Tabelle, ein Schema oder eine Datenbank klonen, werden auch die SEARCH OPTIMIZATION-Eigenschaft und die Suchzugriffspfade jeder Tabelle geklont. (Durch das Klonen einer Tabelle, eines Schemas oder einer Datenbank wird ein Nullkopie-Klon jeder Tabelle und der entsprechenden Suchzugriffspfade erstellt.)
Beachten Sie, dass bei Verwendung von CREATE TABLE … LIKE zum Erstellen einer neuen Tabelle mit denselben Spalten wie die ursprüngliche Tabelle die SEARCH OPTIMIZATION-Eigenschaft nicht in die neue Tabelle kopiert wird.
Verwenden von Tabellen in einer sekundären Datenbank (Unterstützung der Datenbankreplikation)¶
Wenn bei einer Tabelle der Primärdatenbank die SEARCH OPTIMIZATION-Eigenschaft aktiviert ist, wird diese Eigenschaft in die entsprechende Tabelle der Sekundärdatenbank repliziert.
Suchzugriffspfade in der sekundären Datenbank werden nicht repliziert, sondern automatisch neu aufgebaut. Beachten Sie, dass dieser Prozess die gleichen Arten von Kosten verursacht, die unter Verwalten der Kosten des Suchoptimierungsdienstes beschrieben sind.
Freigabe der Tabelle¶
Datenanbieter können Secure Data Sharing verwenden, um Tabellen freizugeben, für die die Suchoptimierung aktiviert ist.
Bei der Abfrage freigegebener Tabellen können Datenverbraucher von Leistungsverbesserungen des Suchoptimierungsdienstes profitieren.
Verwalten der Kosten des Suchoptimierungsdienstes¶
Der Suchoptimierungsdienst wirkt sich auf die Kosten für Speicher- und Computeressourcen aus:
Speicherressourcen: Der Suchoptimierungsdienst erstellt eine Datenstruktur für den Suchzugriffspfad, die Platz bei jeder Tabelle benötigt, für die die Suchoptimierung aktiviert ist. Die Speicherkosten für den Suchzugriffspfad hängen von mehreren Faktoren ab, darunter:
Anzahl unterschiedlicher Werte (NDVs) in der Tabelle. Im Extremfall, wenn alle Spalten Datentypen haben, die den Suchzugriffspfad verwenden, und alle Datenwerte in jeder Spalte eindeutig sind, kann der erforderliche Speicher so groß sein wie die Größe der Originaltabelle.
In der Regel beträgt die Größe jedoch ungefähr 1/4 der ursprünglichen Tabellengröße.
Computeressourcen:
Das Hinzufügen der Suchoptimierung zu einer Tabelle verbraucht Ressourcen während der erstmaligen Erstellungsphase.
Die Wartung des Suchoptimierungsdienstes erfordert ebenfalls Ressourcen. Der Ressourcenverbrauch ist höher, wenn eine hohe Änderungsrate vorliegt (d. h. wenn sich große Datenmengen in der Tabelle ändern). Diese Kosten sind in etwa proportional zur Menge der aufgenommenen (hinzugefügten oder geänderten) Daten. Löschungen verursachen ebenfalls einige Kosten.
Mit Automatic Clustering kann zwar die Latenz von Abfragen auf Tabellen mit Suchoptimierung verbessert werden, aber die Wartungskosten für die Suchoptimierung werden weiter erhöht. Wenn eine Tabelle eine hohe Änderungsrate hat, kann das Aktivieren des automatischen Clusterings und das Konfigurieren der Suchoptimierung für die Tabelle zu höheren Wartungskosten führen, als wenn die Tabelle nur für die Suchoptimierung konfiguriert ist.
Snowflake stellt jedoch eine effiziente Credit-Nutzung sicher, indem nur die tatsächlich verwendeten Ressourcen in Rechnung gestellt werden. Die Abrechnung erfolgt in Schritten von 1 Sekunde.
Die Kosten pro Computestunde finden Sie in der Tabelle „Serverless Feature Credits Table“ unter Snowflake Service Consumption Table.
Sobald Sie den Suchoptimierungsdienst aktiviert haben, können Sie die Kosten für Ihre Nutzung des Dienstes anzeigen.
Tipp
Snowflake empfiehlt, dieses Feature langsam einzuführen (d. h. die Suchoptimierung zunächst nur für wenige Tabellen hinzuzufügen) und Kosten und Nutzen genau zu überwachen.
Abschätzen der Kosten¶
Verwenden Sie die Funktion SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS, um die Kosten für das Hinzufügen der Suchoptimierung zu einer Tabelle und das Konfigurieren bestimmter Spalten für die Suchoptimierung abzuschätzen.
Im Allgemeinen sind die Kosten proportional zur:
Anzahl der Spalten, für die das Feature aktiviert ist, und Anzahl der diskreten Werte in diesen Spalten.
Datenmenge, die sich in diesen Tabellen ändert.
Wichtig
Die von der Funktion SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS zurückgegebenen Kostenschätzungen werden mit bestmöglichen Bemühungen vorgenommen. Die geschätzten Kosten können allerdings erheblich (bis zu 50 % oder in seltenen Fällen um ein Vielfaches) von den tatsächlich realisierten Kosten abweichen.
Die Schätzungen der Build- und Speicherkosten basieren auf einer Stichprobe einer Teilmenge der Zeilen in der Tabelle
Die Schätzungen der Wartungskosten basieren auf den jüngsten Erstellungs-, Lösch- und Aktualisierungsaktivitäten in der Tabelle
Anzeigen der Kosten¶
Sie können die Abrechnungskosten für den Suchoptimierungsdienst über die Weboberfläche oder mit SQL anzeigen. Siehe Untersuchen der Computekosten.
Senken der Kosten¶
Sie können die Kosten des Suchoptimierungsdienstes steuern, indem Sie sorgfältig die Tabellen und Spalten auswählen, für die die Suchoptimierung aktiviert werden soll.
Außerdem können Sie die Kosten des Suchoptimierungsdienstes wie folgt senken:
Snowflake empfiehlt für DML-Operationen auf der Tabelle die Verwendung von Batches:
DELETE
: Wenn in Tabellen Daten für den letzten Zeitraum (z. B. den letzten Tag, die letzte Woche oder den letzten Monat) gespeichert sind und Sie die Tabelle durch Löschen alter Daten verkleinern, muss der Suchoptimierungsdienst die Aktualisierungen berücksichtigen. In einigen Fällen können Sie Kosten ggf. senken, indem Sie Löschoperationen seltener ausführen (z. B. täglich anstatt stündlich).INSERT
,UPDATE
undMERGE
: Durch Verwenden von Batches für diese DML-Anweisungen auf der Tabelle können die Kosten zur Wartung des Suchoptimierungsdienstes gesenkt werden.
Wenn Sie ein Reclustering der gesamten Tabelle durchführen möchten, müssen Sie vor dem Reclustering erst die SEARCH OPTIMIZATION-Eigenschaft für diese Tabelle löschen und nach dem Reclustering die SEARCH OPTIMIZATION-Eigenschaft wieder zu der Tabelle hinzufügen.
Bevor Sie die Suchoptimierung für die Suche nach Teilzeichenfolgen (
ON SUBSTRING(col)
) oder VARIANTs (ON EQUALITY(variant_col)
) aktivieren, sollten Sie SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS aufrufen, um die Kosten zu schätzen. Das erstmalige Erstellen und die Wartung können bei diesen Suchmethoden mit hohem Ressourcenverbrauch verbunden sein, daher sollten Sie zwischen Performance und Kosten abwägen.
Entfernen der Suchoptimierung aus bestimmten Spalten oder aus der gesamten Tabelle¶
Sie können die Suchoptimierungskonfiguration für bestimmte Spalten entfernen, oder Sie können die SEARCH OPTIMIZATION-Eigenschaft für die gesamte Tabelle entfernen.
Löschen der Suchoptimierung für bestimmte Spalten¶
Um die Konfiguration der Suchoptimierung für bestimmte Spalten zu löschen, verwenden Sie den folgenden Befehl: ALTER TABLE … DROP SEARCH OPTIMIZATION mit ON-Klausel.
Angenommen, die Ausführung des Befehls DESCRIBE SEARCH OPTIMIZATION gibt die folgenden Ausdrücke aus:
DESCRIBE SEARCH OPTIMIZATION ON t1;
+---------------+-----------+-----------+-------------------+--------+
| expression_id | method | target | target_data_type | active |
+---------------+-----------+-----------+-------------------+--------+
| 1 | EQUALITY | C1 | NUMBER(38,0) | true |
| 2 | EQUALITY | C2 | VARCHAR(16777216) | true |
| 3 | EQUALITY | C4 | NUMBER(38,0) | true |
| 4 | EQUALITY | C5 | VARCHAR(16777216) | true |
| 5 | EQUALITY | V1 | VARIANT | true |
| 6 | SUBSTRING | C2 | VARCHAR(16777216) | true |
| 7 | SUBSTRING | C5 | VARCHAR(16777216) | true |
| 8 | GEO | G1 | GEOGRAPHY | true |
| 9 | EQUALITY | V1:"key1" | VARIANT | true |
| 10 | EQUALITY | V1:"key2" | VARIANT | true |
+---------------+-----------+-----------+-------------------+--------+
Um die Suchoptimierung für Teilzeichenfolgen auf Spalte c2
zu löschen, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON SUBSTRING(c2);
Um die Suchoptimierung für alle Methoden auf Spalte c5
zu löschen, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON c5;
Da die Spalte c5
für die Optimierung von Gleichheits- und Teilzeichenfolgen-Suche konfiguriert ist, wird mit der obigen Anweisung die Konfiguration für Gleichheits- und Teilzeichenfolgen-Suche für c5
gelöscht.
Um die Suchoptimierung für Gleichheit auf Spalte c1
und auch die durch die Ausdrucks-IDs 6
und 8
angegebene Konfiguration zu löschen, führen Sie die folgende Anweisung aus:
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON EQUALITY(c1), 6, 8;
Weitere Informationen dazu finden Sie im Abschnitt zu ALTER TABLE … DROP SEARCH OPTIMIZATION.
Entfernen der Suchoptimierung aus der Tabelle¶
So entfernen Sie die SEARCH OPTIMIZATION-Eigenschaft aus einer Tabelle:
Wechseln Sie zu einer Rolle mit Berechtigungen zum Entfernen der Suchoptimierung aus der Tabelle.
Führen Sie den Befehl ALTER TABLE … DROP SEARCH OPTIMIZATION ohne ON-Klausel aus:
ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
Beispiel:
alter table test_table drop search optimization;
Weitere Informationen dazu finden Sie im Abschnitt zu ALTER TABLE … DROP SEARCH OPTIMIZATION.
Beispiele¶
Der folgende Code zeigt die Erstellung und Verwendung eines Suchoptimierungsdienstes.
Beginnen Sie, indem Sie eine Tabelle mit Daten erstellen:
create or replace table test_table (id int, c1 int, c2 string, c3 date) as select * from values (1, 3, '4', '1985-05-11'), (2, 4, '3', '1996-12-20'), (3, 2, '1', '1974-02-03'), (4, 1, '2', '2004-03-09'), (5, null, null, null);Fügen Sie die SEARCH OPTIMIZATION-Eigenschaft zur Tabelle hinzu.
alter table test_table add search optimization;Die folgenden Abfragen können den Suchoptimierungsdienst verwenden:
select * from test_table where id = 2;select * from test_table where c2 = '1';select * from test_table where c3 = '1985-05-11';select * from test_table where c1 is null;select * from test_table where c1 = 4 and c3 = '1996-12-20';Bei der folgenden Abfrage kann der Suchoptimierungsdienst verwendet werden, da die implizite Umwandlung auf der Konstante und nicht auf der Spalte erfolgt:
select * from test_table where c2 = 2;Im Folgenden kann der Suchoptimierungsdienst nicht verwendet werden, da die Umwandlung auf der Tabellenspalte erfolgt:
select * from test_table where cast(c2 as number) = 2;Eine IN-Klausel wird vom Suchoptimierungsdienst unterstützt:
select id, c1, c2, c3 from test_table where id IN (2, 3) order by id;Wenn einzelne Prädikate vom Suchoptimierungsdienst unterstützt werden, können sie mit der Konjunktion
AND
verknüpft werden und werden dennoch vom Suchoptimierungsdienst unterstützt:select id, c1, c2, c3 from test_table where c1 = 1 and c3 = TO_DATE('2004-03-09') order by id;DELETE und UPDATE (und MERGE) sind ebenfalls mit dem Suchoptimierungsdienst kompatibel:
delete from test_table where id = 3;update test_table set c1 = 99 where id = 4;
Kompatibilität mit Abfragebeschleunigung¶
Suchoptimierung und Abfragebeschleunigung können zusammen genutzt werden, um die Abfrageleistung zu optimieren. Erstens kann die Suchoptimierung die Mikropartitionen entfernen, die für eine Abfrage nicht benötigt werden. Dann kann die Abfragebeschleunigung für in Frage kommende Abfragen einen Teil der verbleibenden Verarbeitung auf freigegebene Computeressourcen verlagern, die vom Dienst bereitgestellt werden.
Die Verarbeitungsleistung von Abfragen, die durch beide Dienste beschleunigt werden, variiert je nach Workload und verfügbaren Ressourcen.