Hi,

I am with a problem that makes impracticable the continuity of our project to use the Firebird as Database in the ERP developed for our company, the situation is this:

In one table that we are using for tests with 500,000 rows approximately is necessary to return orderly ascending and descending from determined combination of 3 columns, the tests are being independent of components or access way, I use Firebird 1.0 Wi-v6.2.908 and I execute the querys from IBConsole.

Data for query:
TT_SIG = Company =´DEJ´
TT_PED = Order, Asked for =´0052652´
TT_LOT = Lot = 749

---------------------------------------------------------------------------------------------------------
Example 1: To return rows with orders less than '0052652', or equal to '0052652' and Lot less than 749. Execution time: (2.15s):
SELECT TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU
FROM TTALAO
WHERE
(TT_SIG = 'DEJ' AND TT_PED < '0052652') OR
(TT_SIG = 'DEJ' AND TT_PED = '0052652' AND TT_LOT < 749)
ORDER BY TT_SIG, TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU

---------------------------------------------------------------------------------------------------------
Example 2: To return rows with orders less than '0052652', or equal to '0052652' and Lot less than 749, with forced plan. Execution time: (47ms) GOOD!!!:
SELECT TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU
FROM TTALAO
WHERE
(TT_SIG = 'DEJ' AND TT_PED < '0052652') OR
(TT_SIG = 'DEJ' AND TT_PED = '0052652' AND TT_LOT < 749)
PLAN (TTALAO INDEX (TT_CHA))
ORDER BY TT_SIG, TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU

---------------------------------------------------------------------------------------------------------
Example 3: To return rows with orders less than ´0052652´, or equal to ´0052652´and Lot less than 749 - in descending order (this is really necessary for the application)
Execution time: (4.10S)
SELECT TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU
FROM TTALAO
WHERE
(TT_SIG = 'DEJ' AND TT_PED < '0052652') OR
(TT_SIG = 'DEJ' AND TT_PED = '0052652' AND TT_LOT < 749)
ORDER BY TT_SIG DESC, TT_PED DESC, TT_LOT DESC, TT_PRG DESC, TT_TAL DESC, TT_CCU DESC

---------------------------------------------------------------------------------------------------------
Example 4: To return rows with orders less than ´0052652´, or equal to ´0052652´ e less than 749 - in descending order (this is really necessary for the application!!!), in this case that plan is forced, but the time increased very!
Execution time: (7.00 Sec.)
SELECT TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU
FROM TTALAO
WHERE
(TT_SIG = 'DEJ' AND TT_PED < '0052652') OR
(TT_SIG = 'DEJ' AND TT_PED = '0052652' AND TT_LOT < 749)
PLAN (TTALAO INDEX (TT_CHA))
ORDER BY TT_SIG DESC, TT_PED DESC, TT_LOT DESC, TT_PRG DESC, TT_TAL DESC, TT_CCU DESC

---------------------------------------------------------------------------------------------------------


I ask: because the time increased in such a way, it must not have less in function of I to be indicating the correct index that must be made the order, as well as in Example 2? Exists another way to make what I want with good performance? therefore in the Oracle I obtain to make this only indicating the execution plan without order by, in Firebird if to put the plan without order by is not return in desired order.


In oracle i do this:

SELECT /*+ INDEX_ASC (TTALAO TT_CHA) */ <- This is a modificator of plan!
TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU
FROM TTALAO
HERE
(TT_SIG = 'DEJ' AND TT_PED < '0052652') OR
(TT_SIG = 'DEJ' AND TT_PED = '0052652' AND TT_LOT < 749)

OR:

SELECT /*+ INDEX_DESC (TTALAO TT_CHA) */ <- This is a modificator of plan!
TT_PED, TT_LOT, TT_PRG, TT_TAL, TT_CCU
FROM TTALAO
HERE
(TT_SIG = 'DEJ' AND TT_PED < '0052652') OR
(TT_SIG = 'DEJ' AND TT_PED = '0052652' AND TT_LOT < 749)

And this is very quick....





Below the definition of the table and indices:

SET SQL DIALECT 3;



/* CREATE DATABASE 'c:\tstbco\siger.fdb' PAGE_SIZE 8192



DEFAULT CHARACTER SET WIN1252 */



/* Table: TTALAO, Owner: SYSDBA */



CREATE TABLE "TTALAO"

(

"TT_SIG" VARCHAR(3) CHARACTER SET WIN1252 NOT NULL,

"TT_PED" VARCHAR(7) CHARACTER SET WIN1252 NOT NULL,

"TT_LOT" INTEGER NOT NULL,

"TT_PRG" INTEGER NOT NULL,

"TT_TAL" INTEGER NOT NULL,

"TT_CCU" SMALLINT NOT NULL,

"TT_DTP" DATE NOT NULL,

"TT_HMP" SMALLINT NOT NULL,

"TT_DTR" DATE,

"TT_HMR" SMALLINT NOT NULL,

"TT_CSP" SMALLINT NOT NULL,

"TT_PRO" INTEGER NOT NULL,

"TT_PTA" INTEGER NOT NULL,

"TT_OCT" SMALLINT NOT NULL,

"TT_FBP" SMALLINT NOT NULL,

"TT_FBR" SMALLINT NOT NULL,

"TT_IPR" SMALLINT NOT NULL,

"TT_JIT" SMALLINT NOT NULL,

"TT_OCG" SMALLINT NOT NULL,

"TT_OCO" SMALLINT NOT NULL,

"TT_PTT_1" INTEGER NOT NULL,

"TT_PTT_2" INTEGER NOT NULL,

"TT_PTT_3" INTEGER NOT NULL,

"TT_PTT_4" INTEGER NOT NULL,

"TT_PTT_5" INTEGER NOT NULL,

"TT_PTT_6" INTEGER NOT NULL,

"TT_PTT_7" INTEGER NOT NULL,

"TT_PTT_8" INTEGER NOT NULL,

"TT_PTT_9" INTEGER NOT NULL,

"TT_PTT_10" INTEGER NOT NULL,

"TT_PTT_11" INTEGER NOT NULL,

"TT_PTT_12" INTEGER NOT NULL,

"TT_PTT_13" INTEGER NOT NULL,

"TT_PTT_14" INTEGER NOT NULL,

"TT_PTT_15" INTEGER NOT NULL,

"TT_PTT_16" INTEGER NOT NULL,

"TT_PTT_17" INTEGER NOT NULL,

"TT_PTT_18" INTEGER NOT NULL,

"TT_PTT_19" INTEGER NOT NULL

);



/* Index definitions for all user tables */



CREATE INDEX "TT_CDP" ON "TTALAO"("TT_SIG", "TT_DTP", "TT_CCU");

CREATE INDEX "TT_CDR" ON "TTALAO"("TT_SIG", "TT_DTR", "TT_CCU");

CREATE UNIQUE INDEX "TT_CFR" ON "TTALAO"("TT_SIG", "TT_FBR", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CHA" ON "TTALAO"("TT_SIG", "TT_PED", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE DESCENDING INDEX "TT_CHA_DESC" ON "TTALAO"("TT_SIG", "TT_PED", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CHL" ON "TTALAO"("TT_SIG", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CHP" ON "TTALAO"("TT_SIG", "TT_PRG", "TT_LOT", "TT_TAL", "TT_CCU");

CREATE INDEX "TT_CHT" ON "TTALAO"("TT_SIG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CPE" ON "TTALAO"("TT_SIG", "TT_LOT", "TT_PED", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CPF" ON "TTALAO"("TT_SIG", "TT_FBP", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CPL" ON "TTALAO"("TT_SIG", "TT_DTP", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CPR" ON "TTALAO"("TT_SIG", "TT_PRO", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");

CREATE UNIQUE INDEX "TT_CRL" ON "TTALAO"("TT_SIG", "TT_DTR", "TT_LOT", "TT_PRG", "TT_TAL", "TT_CCU");





/* Grant Roles for this database */





/* Grant permissions for this database */




Thanks for all,


Sandro Silveira



[Non-text portions of this message have been removed]