запрос вида
select p.name, p.Price_Retail, p.FullName, p.ID_Product,
p.Flag, g.name, g.discount
from products p join groups g on p.id_group=g.id_group
order by g.name, p.name
выдает план
PLAN SORT (JOIN (G NATURAL, P INDEX (FK_PRODUCTS__ID_GROUP)))
где natural приводит к тормозам. Что мне нужно еще сделать, чтобы запрос
работал быстро?
Вот DDL
CREATE TABLE PRODUCTS (
ID_PRODUCT INTEGER NOT NULL,
NAME VARCHAR(30) NOT NULL COLLATE PXW_CYRL,
SHORTCODE VARCHAR(25) COLLATE PXW_CYRL,
FULLNAME VARCHAR(40) COLLATE PXW_CYRL,
DESCRIPTION VARCHAR(90) COLLATE PXW_CYRL,
ID_GROUP INTEGER NOT NULL,
ID_UNIT INTEGER,
PRICE_RETAIL T_MONEY NOT NULL /* T_MONEY = NUMERIC(11,4) DEFAULT 0 NOT NULL */,
WARNING T_BOOLEAN NOT NULL /* T_BOOLEAN = SMALLINT DEFAULT 0 NOT NULL CHECK
(VALUE IN (0,1)) */,
FLAG SMALLINT NOT NULL,
ID_ANALOG INTEGER
);
ALTER TABLE PRODUCTS ADD CONSTRAINT PK_PRODUCTS PRIMARY KEY (ID_PRODUCT);
ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS__ID_GROUP FOREIGN KEY
(ID_GROUP) REFERENCES GROUPS (ID_GROUP) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS__ID_UNIT FOREIGN KEY (ID_UNIT)
REFERENCES UNITS (ID_UNIT) ON UPDATE CASCADE;
CREATE INDEX IDX_PRODUCTS__ID_ANALOG ON PRODUCTS (ID_ANALOG);
CREATE INDEX IDX_PRODUCTS__NAME ON PRODUCTS (NAME);
CREATE TABLE GROUPS (
ID_GROUP INTEGER NOT NULL,
ID_PARENT INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL COLLATE PXW_CYRL,
DISCOUNT T_MONEY NOT NULL /* T_MONEY = NUMERIC(11,4) DEFAULT 0 NOT NULL */
);
ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY (ID_GROUP);
CREATE UNIQUE INDEX IDX_GROUPS__1 ON GROUPS (ID_PARENT, NAME);
CREATE INDEX IDX_GROUPS__NAME ON GROUPS (NAME);
