iBase.ru Forum

Код: Выделить всё

CREATE TABLE CUSTOMER (      /*КРЕДИТЫ*/
    NUMBER INTEGER NOT NULL, /* номер договора*/
    DATE0 DATE,              /* дата договора */
    SUM0 FLOAT);             /* сумма кредита */
ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (NUMBER);
CREATE INDEX IDX_CUSTOMER_DATE ON CUSTOMER (DATE0);
 
CREATE TABLE PAYMENT (       /* ПОГАШЕНИЯ КРЕДИТОВ*/
    ID INTEGER NOT NULL,
    NUMBER INTEGER NOT NULL, /* номер договора */
    DATE1 DATE,              /* дата погашения */
    SUM1 FLOAT);             /* сумма погашения */
ALTER TABLE PAYMENT ADD CONSTRAINT PK_PAYMENT PRIMARY KEY (ID);

Код: Выделить всё

ALTER TABLE PAYMENT ADD  CONSTRAINT FK_PAYMENT FOREIGN KEY (NUMBER) REFERENCES CUSTOMER (NUMBER) ON DELETE CASCADE ON UPDATE CASCADE;

Код: Выделить всё

select C.NUMBER, /* номер договора */
       C.SUM0, /* сумма кредита */
       sum(P.SUM1), /* сумма погашений по кредиту */
       max(P.DATE1) /* дата последнего погашения */
  from CUSTOMER C left outer join PAYMENT P on (C.NUMBER=P.NUMBER) and
                                               (P.DATE1<='01.01.2005')
  where C.DATE0<='01.01.2005'
  group by C.NUMBER, C.SUM0
  having (C.SUM0>sum(P.SUM1)) or (sum(P.SUM1) is NULL)

Код: Выделить всё

Query Time
------------------------------------------------
Prepare       : 10
Execute       : 1 222
Avg fetch time: 101,83 ms

Plan:
------------------------------------------------
PLAN SORT (JOIN (C INDEX (IDX_CUSTOMER_DATE),P INDEX (RDB$FOREIGN3)))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
|        Table Name        | Index | Non-Index | Updated | Deleted | Inserted |
|                          | reads |   reads   |         |         |          |
+--------------------------+-------+-----------+---------+---------+----------+
|               RDB$INDICES|     2 |         0 |       0 |       0 |        0 |
|             RDB$RELATIONS|     2 |         0 |       0 |       0 |        0 |
|                  CUSTOMER| 6 681 |         0 |       0 |       0 |        0 |
|                   PAYMENT| 40 92 |         0 |       0 |       0 |        0 |
|  RDB$RELATION_CONSTRAINTS|     0 |        12 |       0 |       0 |        0 |
+--------------------------+-------+-----------+---------+---------+----------+

Код: Выделить всё

CREATE INDEX IDX_PAYMENT_DATE ON PAYMENT (DATE1);

Код: Выделить всё

Query Time
------------------------------------------------
Prepare       : 20
Execute       : 38 456
Avg fetch time: 3 204,67 ms

Plan:
------------------------------------------------
PLAN SORT (JOIN (C INDEX (IDX_CUSTOMER_DATE),P INDEX (RDB$FOREIGN3,IDX_PAYMENT_DATE)))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
|        Table Name        | Index | Non-Index | Updated | Deleted | Inserted |
|                          | reads |   reads   |         |         |          |
+--------------------------+-------+-----------+---------+---------+----------+
|                  CUSTOMER| 6 681 |         0 |       0 |       0 |        0 |
|                   PAYMENT| 26 52 |         0 |       0 |       0 |        0 |
|  RDB$RELATION_CONSTRAINTS|     0 |        12 |       0 |       0 |        0 |
+--------------------------+-------+-----------+---------+---------+----------+