Grundsätzlich - kann DB mehrere Indizes kombinieren?

[IBExpert]

Mehrere Indizes pro Tabelle in einer Abfrage kombinieren ist bei Firebird kein Problem. Wenn es passende Multicolumn Indizes gibt, dann werden meistens die vom Optimierer bevorzugt, ansonsten werden aber auch mehrere Indizes pro Tabelle kombiniert benutzt. Wenn mehrere Indizes kombiniert werden, entscheidet die Selektivität über die Reihenfolge

Beispiel auf der IBExpert Demo DB, Tabelle Product mit 10000 Datensätzen, Firebird 2.5.4

Code:

select * from product where category_id=1 and special=1

Code:

PLAN (PRODUCT INDEX (IX_PROD_CATEGORY, IX_PROD_SPECIAL))

Datenmengen:
category_id=1 -> 327 Datensätze (insgesamt gibt es 17 unterschiedliche Werte, daher ist die Selektivität 1/17)
special=1 -> 184 Datensätze (es gibt relativ wenige Specials mit 1, der Rest ist 0, Selektivität 1/2)

Der Optimierer beginnt mit dem selektiveren Index, nutzt aber weitere Indizes sofern sinnvoll trotzdem.

Wenn man o.a. SQL ausführt bekommt man die Ergebnismenge von 9 Datensätzen mit 9 indizierten Reads

Code:

Query
------------------------------------------------
select * from product
where category_id=1 and
special=1

Plan
------------------------------------------------
PLAN (PRODUCT INDEX (IX_PROD_CATEGORY, IX_PROD_SPECIAL))

Query Time
------------------------------------------------
Prepare       : 15,00 ms
Execute       : 31,00 ms
Avg fetch time: 3,44 ms

Memory
------------------------------------------------
Current: 34.728.216
Max    : 35.150.104
Buffers: 2.048

Operations
------------------------------------------------
Read   : 0
Writes : 0
Fetches: 27
Marks  : 0


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT                        |         0 |         9 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Wichtig: Die Feldreihenfolge im SQL ist für den Plan unwichtig, sofern alle den gleichen Operator (in diesem Falle = ) benutzen


Metadaten

Code:

/******************************************************************************/
/****              Generated by IBExpert 08.09.2015 23:46:34               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE PRODUCT (
    ID           NUMERIC(18,0) NOT NULL,
    CATEGORY_ID  NUMERIC(18,0) NOT NULL,
    TITLE        VARCHAR(50) NOT NULL,
    ACTOR        VARCHAR(50) NOT NULL,
    PRICE        NUMERIC(12,2) NOT NULL,
    SPECIAL      SMALLINT
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (ID);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE PRODUCT ADD CONSTRAINT FK_PRODUCT FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX IX_PROD_ACTOR ON PRODUCT (ACTOR);
CREATE INDEX IX_PROD_CATEGORY ON PRODUCT (CATEGORY_ID);
CREATE INDEX IX_PROD_SPECIAL ON PRODUCT (SPECIAL);
CREATE INDEX IX_PROD_TITLE ON PRODUCT (TITLE);
CREATE DESCENDING INDEX PRODUCT_IDX2 ON PRODUCT (ID);


/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/