Das Produktionsschema enthielt u.a. Tabellen für die Aufträge (ORDERS), die Geräte (DEVICES), die Tester-Stationen (TEST_STATION), die Gerätetests (DEVICE_TESTS), die Resultate der einzelnen Messungen (RESULTS), die Parameter, die gemessen wurden (PARAMETERS), und die Verpackungsdaten (PACKAGING) (siehe Abbildung unter "Zugehörige Dateien" am Seitenende).
Die Datenbank wurde über eine Applikation mit Daten gefüttert, auf die der DBA (wie üblich) keinen Einfluss oder Zugriff hatte, aber Datenbank und Produktion liefen weitgehend problemlos und alle waren zufrieden.
Während der Tests wurde gleich an der ersten Station auch ein Wapperl (bayerisch für Label) mit der Batchnummer eines Zubehörteils eingescannt und als VALUE in der Tabelle RESULTS verstaut.
Ein etwas verzottelter Werkstudent, der an der Uni gerade mit Schema-Design-Optimierung zu tun gehabt hatte, meinte, dass solche Details in der Devices-Tabelle vielleicht besser aufgehoben wären, wurde aber überstimmt.
Die Jahre zogen ins Land, neue Standorte (und dementsprechend weitere Datenbanken) kamen hinzu und alle Daten wurden in eine zentrale Datenbank repliziert, die langsam, aber stetig wuchs. Am schnellsten nahm natürlich die Größe der RESULTS-Tabelle zu. Die anfangs blitzschnellen Selects auf der CENTER-DB wurden allmählich langsamer, aber das störte noch keinen.
Dunkle Wolken
Eines Tages zogen dunkle Wolken auf, weil einer der Chefs sich eine Auswertung der Fehlerursachen auf Basis der Batchnummern der Zubehörteile wünschte und das Ganze nicht nur für die letzten Wochen, sondern am besten für den Zeitraum der letzten 12 Jahre.
Ein Projektleiter machte sich gleich ans Werk und ging auf die Suche nach jemandem, der dafür eine Lösung finden könnte.
In einer dunklen Server-Raumecke fand er eine alte Datenbank-Administratorin, die sich gelegentlich auch mit SQL, PL/SQL und ähnlichem beschäftigte und fragte sie um Rat.
Diese war durchaus angetan von der neuen „Challenge“ und verschaffte sich erstmal einen Überblick mit einem Select der Messungen eines bekanntermaßen ausgefallenen Geräts.
Die Fehlerursache hatte die Parameter_id 100, die Batchnummer die 400.
SELECT CASE dt.parameter_id WHEN 100 THEN r.value END fail_cause,
CASE dt.parameter_id WHEN 400 THEN r.value END batch_no,
dt.pass test_pass,
dt_test_date,
t.description test_station
FROM results r JOIN device_tests dt
ON dt.id = r. device_test_id
JOIN devices d
ON d.id = dt.device_id
JOIN test_station t
ON dt.test_station_id = t.id
WHERE d.serial_no = 'ABC0001000'
AND dt.parameter_id IN (100,400);
FAIL_CAUSE BATCH_NO PASS TEST_DATE TEST_STATION
----------------------- ----------------- ------ ----------------- --------------------
Z47110815 1 04.12.2022 07:42:39 STATION1
leakage current high 0 04.12.2022 10:43:35 STATION2
Fehlerursachen
Schöner Mist! Da die Fehlerursache (fail_cause) auch ein Parameter war, der bei den Tests in die Tabelle RESULTS geschrieben wurde, konnte die Batch-Nummer des Zubehörteils und die Fehlerursache wegen des Inner Joins nur gleichzeitig selektiert werden, wenn der Fehler zufällig an der gleichen Station auftrat (was so gut wie nie der Fall war). Ansonsten musste man sich erst die Batchnummer suchen und damit dann die Fehlerursachen ermitteln.
SELECT g.serial_no,
o.order_no
r.value fail_cause,
dt.pass test_pass,
dt_test_date,
t.description test_station
FROM results r JOIN device_tests dt
ON dt.id = r. device_test_id
JOIN devices d
ON d.id = dt.device_id
JOIN test_station t
ON dt. test_station_id = t.id
JOIN orders o
ON o.id = d.order_id
WHERE r.parameter_id = 100
AND r.pass = 0
AND EXISTS (SELECT 1 FROM results r2 JOIN device_tests dt2
ON dt2.id = r2.device_test_id
JOIN devices d2
ON d2.id = dt2.device_id
AND r2.value = 'Z47110815');
Schon der Ausführungsplan (hier ohne Byte, cost und time) für diesen Select mit nur einer Batchnummer sah nicht wirklich gut aus, weil die riesige Tabelle RESULTS zweimal durchforstet werden musste.
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | VM_NWVW_2 |
| 2 | HASH UNIQUE | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | NESTED LOOPS | |
| 6 | NESTED LOOPS | |
| 7 | NESTED LOOPS | |
| 8 | NESTED LOOPS | |
| 9 | NESTED LOOPS | |
|* 10 | TABLE ACCESS FULL | RESULTS |
| 11 | TABLE ACCESS BY INDEX ROWID | DEVICE_TESTS |
|* 12 | INDEX UNIQUE SCAN | PK_DEVICE_TESTS |
| 13 | TABLE ACCESS BY INDEX ROWID | DEVICE_TESTS |
|* 14 | INDEX RANGE SCAN |FK_DEVICE_TEST_DEVICES |
| 15 | TABLE ACCESS BY INDEX ROWID | TEST_STATION |
|* 16 | INDEX UNIQUE SCAN | PK_TEST_STATION |
| 17 | TABLE ACCESS BY INDEX ROWID | DEVICES |
|* 18 | INDEX UNIQUE SCAN | PK_DEVICES |
| 19 | TABLE ACCESS BY INDEX ROWID | ORDERS |
|* 20 | INDEX UNIQUE SCAN | PK_ORDERS |
|* 21 | INDEX RANGE SCAN | FK_RESULTS_DEVICE_TESTS |
|* 22 | TABLE ACCESS BY INDEX ROWID | RESULTS |
----------------------------------------------------------------------------------------------------------
Ein DBA ist bei solchen Aktionen klar im Vorteil, er kann sich schnell mal selber einen Index erstellen.
CREATE INDEX results_batch_nr_idx
ON results(CASE parameter_id WHEN 400 THEN value END);
Dieser Index beschleunigte die Abfrage nach einzelnen Batchnummern, wenn man die letzte Zeile der Abfrage folgendermaßen anpasste:
WHERE CASE r2.parameter_id WHEN 400 THEN value END = 'Z47110815'
Wenn man aber so gut wie alle Batchnummern seit Produktionsstart suchte, brachte dieser funktionsbasierte Index natürlich nichts.
Die IT-lerin erzeugte sich für die Gesamtübersicht erstmal eine Hilfstabelle, die die Gerätedetails zusammen mit der Batchnummer speicherte.
CREATE TABLE devices_per_batch_nos
AS
SELECT DISTINCT d.id device_id, d.serial_no, d.order_id,
d.prod_type_id, r.value batch_no
FROM device d JOIN device_tests dt
ON d.id = dt. device_id
JOIN results r
ON gt.id = m.geraettest_id
WHERE r.parameter_id = 100
AND r.value IS NOT NULL;
Mit dieser konnten Batchnummern und Fehlerursachen dann auf einer Ebene dargestellt werden.
SELECT d.prod_type_id,
d.serial_no,
o.order_no,
r.fail_no,
d.batch_no,
dt.test_date,
dt.pass dev_test_result,
t.description
FROM results r JOIN device_tests dt
ON dt.id = r.dev_test_id
JOIN devices_per_batch_nos d
ON d.device_id = dt.device_id
JOIN test_station t
ON dt.test_station_id = t.id
JOIN orders o
ON d.order_id = o.id
WHERE r.parameter_id = 301
AND r.pass = 0
ORDER BY d.prod_type_id, d.batch_no, d.serial_no, dt.test_date;
Der Chef bekam die Ausgabe als Excel-File, war sehr zufrieden und lobte sowohl Projektleiter als auch IT, aber die Testerapplikation blieb natürlich unverändert.
Bei der Einführung eines neuen Produkts durfte sich die IT-Mitarbeiterin dann aber nicht nur um die Datenbank kümmern, sondern sogar beim Schema-Design mitreden und das Tester-Interface auf Datenbankseite programmieren. Dort landeten dann die gerätbezogenen Infos auch gleich da, wo sie hingehörten, was die Auswertungen deutlich erleichterte, aber das ist eine andere Geschichte.
Fazit:
Bei der Sammlung von Produktionsdaten gilt das gleiche wie bei der Sammlung von Steuerbelegen. Alles in die gleiche Schublade (Tabelle) zu stopfen, führt im Ernstfall zu längeren Suchaktionen. Beim Applikationsdesign auch Leute zu Rate zu ziehen, die sich mit Datenbanken und SQL auskennen, lohnt sich auf jeden Fall.
Dr. Elke Fritsch
DOAG Themenverantwortliche Tuning
-------
Bild von NoName_13 auf Pixabay
