Ich habe eine Tabelle TAB_1 mit folgendem Schema

    CAR_NO(Varchar)  START_DATE(Date)  ACTUAL_ARRIVAL_TIME(Number)  SOURCE_POINT(Varchar) 
   END_POINT(Varchar)

Tabelle TAB_2 mit folgendem Schema

CAR_NO(Varchar)   ACTL_TIME_OF_ARRVL(Date)  EVENT_CODE(Varchar) 

Meine Anfrage lautet:

 SELECT DISTINCT CAR_NO,START_DATE FROM TAB_1 WHERE
(TRUNC(START_DATE +CASE WHEN ACTUAL_ARRIVAL_TIME=0 THEN NULL ELSE ACTUAL_ARRIVAL_TIME END/1440)='10-Feb-2020' )  
    AND SOURCE_POINT=END_POINT
    UNION
    SELECT DISTINCT  CAR_NO,START_DATE FROM TAB_2 WHERE EVENT_CODE='TD' 
    AND TRUNC( ACTL_TIME_OF_ARRVL)='10-Feb-2020' 

In der Spalte ACTUAL_ARRIVAL_TIME wird der Zeitwert in Minuten gespeichert. In der Spalte ACTL_TIME_OF_ARRVL wird das Datum als Zeitstempelwert gespeichert. Ich versuche, alle Fahrzeuge zu finden, die am 10. Februar 2020 enden kann in keiner der Tabellen fehlen, daher habe ich hier die UNION-Operation verwendet. Zusätzlich zu dieser Abfrage muss ich weitere Operationen anwenden, sodass die Gesamtdauer ca. 35 Sekunden beträgt. Bitte führen Sie, um diese Abfrage zu optimieren.

0
radha 17 Feb. 2020 im 14:06

3 Antworten

Beste Antwort

Die Verwendung von trunc() für den Spaltenwert verhindert, dass ein Index für diese Spalte verwendet wird (es sei denn, es handelt sich um einen funktionsbasierten Index). Es ist besser, einen Datumsbereich zu verwenden, der den gesamten Tag abdeckt, an dem Sie interessiert sind. Das Hinzufügen des Zeitversatzes zu start_date wirkt sich auch auf den Index aus. und '10-Feb-2020' ist eine Zeichenfolge, kein Datum, daher erzwingen Sie eine implizite Konvertierung - niemals eine gute Idee. Sie brauchen auch nicht distinct mit union als (im Gegensatz zu union all), das ohnehin Duplikate eliminiert.

Ich würde so etwas als Ausgangspunkt vorschlagen:

select car_no, start_date
from tab_1
where source_point = end_point
and start_date >= date '2020-02-10' - actual_arrival_time * interval '1' minute
and start_date < date '2020-02-11' - actual_arrival_time * interval '1' minute
union
select car_no, start_date
from tab_2
where event_code='TD' 
and actl_time_of_arrvl >= date '2020-02-10'
and actl_time_of_arrvl < date '2020-02-11'

Mit actual_arrival_time * interval '1' minute erzielen Sie den gleichen Effekt wie mit ACTUAL_ARRIVAL_TIME END/1440. Der erste ist ein Intervalldatentyp, der zweite ist ein Bruchteil eines Tages, aber beide repräsentieren die Anzahl der Minuten als Wert, der zu einem Datumswert hinzugefügt werden kann.

Ich verwende Datumsliterale, die immer noch fest codiert sind. Wenn Sie wirklich einen Parameter verwenden, können Sie interval '1' day zum gewünschten Datum hinzufügen, anstatt einen Tag später fest zu codieren. In beiden Fällen wird nach Werten um oder nach Mitternacht am ersten Datum und vor Mitternacht am zweiten Datum gesucht - dies deckt alle möglichen Zeiten an diesem Tag ab.

Der erste Zweig kann den Index aufgrund der Variabilität der Bezugnahme auf einen anderen Spaltenwert in der Bereichsberechnung immer noch nicht richtig verwenden. Wenn Sie jedoch beispielsweise wissen, dass actual_arrival_time innerhalb von 24 Stunden liegt, können Sie ihm dabei weiterhelfen starre Einschränkung:

select car_no,start_date
from tab_1
where source_point = end_point
and start_date >= date '2020-02-10'
and start_date < date '2020-02-10' + interval '2' day -- depending on allowed ranges
and start_date + actual_arrival_time * interval '1' minute >= date '2020-02-10'
and start_date + actual_arrival_time * interval '1' minute < date '2020-02-10' + interval '1' day 
union
select car_no, start_date
from tab_2
where event_code='TD' 
and actl_time_of_arrvl >= date '2020-02-10'
and actl_time_of_arrvl < date '2020-02-10' + interval '1' day

Hier gibt start_date >= date '2020-02-10' dem Index eine Untergrenze für die Suche (vorausgesetzt, actual_arrival_time kann nicht negativ sein, was vernünftig erscheint); und start_date < date '2020-02-10' + interval '2' day gibt eine Obergrenze an. Was diese Obergrenze ist, hängt von den zulässigen Werten ab, insbesondere für actual_arrival_time.

In Ihrer Frage ist unklar, ob tab1.start_date immer Mitternacht ist, sodass Sie möglicherweise den ersten Teil der Prüfung auf eine genaue Datumsübereinstimmung anstelle eines Bereichs vereinfachen können. Aber dann wäre ein Blick auf actual_arrival_time wahrscheinlich nicht notwendig ... Wenn start_date tatsächlich immer Mitternacht ist und actual_arrival_time auf 0 bis 1440 beschränkt ist, könnte dies so einfach sein wie:

select car_no,start_date
from tab_1
where source_point = end_point
and start_date >= date '2020-02-10'
and start_date < date '2020-02-10' + interval '1' day
union
...

Genau wie der zweite Zweig. Die Art und Weise, wie Sie versucht haben, sich dem anzunähern, deutet jedoch darauf hin, dass dies möglicherweise nicht der Fall ist. Sie müssen lediglich die anfängliche Indexsuche so weit wie möglich eingrenzen, bevor Sie nach der genauen Zeit filtern. Ich vermute, dass actual_arrival_time mehrere Tage oder sogar Wochen oder Monate darstellen könnte; Also zurück zur vorherigen Version, die

and start_date < date '2020-02-10' + interval '2' day -- depending on allowed ranges

Würde sich auf das Maximum erstrecken, das Sie erwarten würden; oder könnte wahrscheinlich komplett weggelassen werden.

Sie müssen sich den Ausführungsplan ansehen, um zu sehen, was er tatsächlich tut, wahrscheinlich für die Gesamtabfrage und jeden Zweig der Union separat.

Wenn Sie dies häufig tun, lohnt es sich möglicherweise, den berechneten Wert start_date + actual_arrival_time * interval '1' minute als virtuelle Spalte zu tab1 hinzuzufügen und diesen zu indizieren.

1
Alex Poole 18 Feb. 2020 im 09:08

Seltsame Art, mit Datum und Uhrzeit umzugehen, denke ich. Überprüfen Sie zunächst Ihren Ausführungsplan.

Ich nehme an, mit wenigen Daten funktioniert es wie ein Zauber, aber mit vielen Daten ist die Leistung weg.

Beginnend mit der UNION ist es selbst ein Leistungsproblem.

Inline-Operationen wie in TAB_1 schließen die Verwendung von Indizes aus. Ich hoffe, Sie haben Indizes in den Feldern ACTUAL_ARRIVAL_TIME und ACTUAL_ARRIVAL_TIME, aber in Ihrem Fall werden sie nicht verwendet. Ich nehme an, diese Abfrage befindet sich mitten in einem Prozess. Sie müssen also eine kleine Problemumgehung durchführen, bevor Sie sie ausführen

1.Kennen Sie Ihre Startminute für Ihre Periode: z. param 'startMinute'.

2.Kennen Sie Ihre Endminute für Ihre Periode: z. param 'endMinute'.

3.Schreiben Sie die Abfrage neu

SELECT DISTINCT CAR_NO,START_DATE 
  FROM TAB_1 
 WHERE START_DATE >=  startMinute
   AND START_DATE <   endMinute
   AND SOURCE_POINT = END_POINT
UNION
.
.
.

Ich hoffe auch, dass Sie einen Index in TAB_2.ACTL_TIME_OF_ARRVL haben, aber auch bei Verwendung von Trunc keinen Index verwenden. Möglicherweise müssen Sie einen funktionsbasierten Index TRUNC (ACTL_TIME_OF_ARRVL) erstellen. Ich bin sicher, Sie werden den folgenden Link sehr interessant finden:

https://blog.dbi-services.com/index-on-truncdate-do-you-still-need-old-index/

1
Biggum 17 Feb. 2020 im 12:09

Ich würde vorschlagen, zunächst die Abfrage wie folgt umzuschreiben:

SELECT CAR_NO, START_DATE
FROM TAB_1
WHERE SOURCE_POINT = END_POINT AND
      TRUNC(START_DATE + ACTUAL_ARRIVAL_TIME * INTERVAL '1' MINUTE) = DATE '2020-02-10'
UNION -- ON PURPOSE TO REMOVE DUPLICATES
SELECT CAR_NO, START_DATE
FROM TAB_2
WHERE EVENT_CODE = 'TD' AND
      TRUNC(ACTL_TIME_OF_ARRVL) = DATE '2020-02-10' ;

Anschließend können Sie für diese Abfrage die folgenden funktionsbasierten Indizes definieren:

create index idx_tab_1_f1 on tab_1 (TRUNC(START_DATE + ACTUAL_ARRIVAL_TIME * INTERVAL '1' MINUTE), SOURCE_POINT, END_POINT);

create index idx_tab_2_f2 on tab_2 (EVENT_CODE, TRUNC(ACTL_TIME_OF_ARRVL));

Sie können die Abfrage auch umformulieren, wenn Sie die Zeitkomponente des Datums in den Index aufnehmen möchten. Es ist unklar, ob dies für andere Abfragen nützlich wäre.

1
Gordon Linoff 17 Feb. 2020 im 12:41