SQLite Skripts für „Die Where-Klausel“

Der Gleicheheitsoperator

Primärschlüssel

EMPLOYEES-Tabelle mit 1000 Einträgen anlegen.

CREATE TABLE employees (
   employee_id   NUMERIC      NOT NULL,
   first_name    VARCHAR(255) NOT NULL,
   last_name     VARCHAR(255) NOT NULL,
   date_of_birth DATE                 ,
   phone_number  VARCHAR(255) NOT NULL,
   junk          CHAR(255)            ,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, junk)
SELECT gen.n +1,
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
       ABS(RANDOM())%9000+1000,
       printf('%.1000c','x')
  FROM generator_4k gen
 WHERE gen.n < 1000;

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123;

ANALYZE employees;

Hinweise:

Die GENERATOR_X-Views erzeugen Zeilen wie in diesem Artikel beschrieben. SQLite unterstützt seit Version 3.8.3 auch die rekursive WITH-Klausel, die hier verwendete Syntax funktioniert jedoch auch in älteren Versionen.
Die JUNK-Spalte wird verwendet, um eine realistische Zeilenlänge zu erhalten. Ohne diese Spalte wäre die Tabelle unrealistisch klein und viele Beispiele würden nicht funktionieren.
Die Tabelle wird mit Zufallsdaten gefüllt. Danach wird mein Eintrag durch ein update geändert.
Die Tabellen und Index Statistiken werden erstellt, sodass der Optimizer einen Überblick über den Tabelleninhalt hat.

Zusammengesetzte Schlüssel

DROP TABLE employees;

CREATE TABLE employees (
   employee_id   NUMERIC      NOT NULL,
   first_name    VARCHAR(255) NOT NULL,
   last_name     VARCHAR(255) NOT NULL,
   date_of_birth DATE                 ,
   phone_number  VARCHAR(255) NOT NULL,
   junk          CHAR(255)            ,
   subsidiary_id NUMERIC      NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id, subsidiary_id)
);

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, junk, subsidiary_id)
SELECT gen.n +1,
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
       ABS(RANDOM())%9000+1000,
       printf('%.1000c','x'),
       30
  FROM generator_4k gen
 WHERE gen.n < 1000;

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123
   AND subsidiary_id=30;

-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name, 
                       last_name,    date_of_birth, 
                       phone_number, subsidiary_id, junk)
SELECT gen.n + 1,
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
       ABS(RANDOM())%9000+1000,
       CAST(ABS(RANDOM())%(gen.n/9000.0 * 29 + 1) + 1 AS INTEGER),
       printf('%.10c','x')
  FROM generator_64k gen
 WHERE gen.n < 9000;

ANALYZE employees;

Hinweise:

SQLite kann Primärschlüssel nicht ändern. Daher wird die ganze Tabelle neu angelegt.
Die neuen Einträge werden zufällig auf die Töchter 1 bis 29 aufgeteilt.
Die Tabelle und Indizes werden neu analysiert, damit der Optimizer das neue Mengengerüst kennt.

Das nächste Skript erstellt den Index auf SUBSIDIARY_ID, um Abfragen nach allen Angestellten eines bestimmten Subunternehmens zu unterstützen:

CREATE INDEX emp_sub_id ON employees(subsidiary_id)

Besser ist es jedoch, einen Index für beide Anforderungen zu verwenden.

DROP TABLE employees;

CREATE TABLE employees (
   employee_id   NUMERIC      NOT NULL,
   first_name    VARCHAR(255) NOT NULL,
   last_name     VARCHAR(255) NOT NULL,
   date_of_birth DATE                 ,
   phone_number  VARCHAR(255) NOT NULL,
   junk          CHAR(255)            ,
   subsidiary_id NUMERIC      NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (subsidiary_id, employee_id)
);

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, junk, subsidiary_id)
SELECT gen.n +1,
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
       ABS(RANDOM())%9000+1000,
       printf('%.1000c','x'),
       30
  FROM generator_4k gen
 WHERE gen.n < 1000;

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123
   AND subsidiary_id=30;

-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name, 
                       last_name,    date_of_birth, 
                       phone_number, subsidiary_id, junk)
SELECT gen.n + 1,
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       CHAR( ABS(random()) % 26 + 65
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           , ABS(random()) % 26 + 97
           ),
       DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
       ABS(RANDOM())%9000+1000,
       CAST(ABS(RANDOM())%(gen.n/9000.0 * 29 + 1) + 1 AS INTEGER),
       printf('%.10c','x')
  FROM generator_64k gen
 WHERE gen.n < 9000;

ANALYZE employees;

Hinweise:

Die Tabelle wird wieder neu angelegt, da SQLite den Primärschlüssel nicht ändern kann.

Funktionen

SQLite bietet keine Create function-Syntax an, sodass die entsprechende Ausdruck direkt in die Abfrage geschrieben werden muss.

SELECT first_name, last_name
     , CAST(STRFTIME('%Y.%m%d', 'now') - STRFTIME('%Y.%m%d', date_of_birth) AS INT)
  FROM employees
 WHERE CAST(STRFTIME('%Y.%m%d', 'now') - STRFTIME('%Y.%m%d', date_of_birth) AS INT) = 42

Das Alter wird ermittelt, indem Daten als Jahreszahlen mit Komma formatiert werden.

Ausdrücke können indiziert werden, wenn sie deterministisch sind.

CREATE INDEX emp_age ON employees
     ( CAST(STRFTIME('%Y.%m%d', 'now') - STRFTIME('%Y.%m%d', date_of_birth) AS INT) )

Error: non-deterministic use of strftime() in an index

Über den Autor

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

SQLite Skripts für „Die Where-Klausel“

Der Gleicheheitsoperator

Primärschlüssel

Zusammengesetzte Schlüssel

Funktionen

Über den Autor

Der 3-Minuten Test

Inhaltsverzeichnis

Aktuelle Blog Posts

Geek-Ware

Mit Markus Winand verbinden

SQLite Skripts für „Die Where-Klausel“

Der Gleicheheitsoperator

Primärschlüssel

Zusammengesetzte Schlüssel

Funktionen

Über den Autor

Sein Buch kaufen

Sein Training

Der 3-Minuten Test

Inhaltsverzeichnis

Aktuelle Blog Posts

Geek-Ware

Mit Markus Winand verbinden