С полным фетчем?
Вот дома эксперимент провёл:
CREATE TABLE "Test" (
"Id" INTEGER NOT NULL,
"Name" VARCHAR(1000) NOT NULL
);
ALTER TABLE "Test" ADD CONSTRAINT "PK_Test" PRIMARY KEY ("Id");
CREATE INDEX "Test_IDX1" ON "Test" COMPUTED BY (CAST(SUBSTRING("Name"
FROM 1 FOR 50) AS VARCHAR(50)));
20 тысяч записей:
SELECT * FROM "Test"
ORDER BY CAST(SUBSTRING("Name" FROM 1 FOR 50) AS VARCHAR(50))
PLAN (Test ORDER Test_IDX1)
------ Performance info ------
Prepare time = 0ms
Execute time = 704ms
Avg fetch time = 0,04 ms
Current memory = 34 645 576
Max memory = 95 473 164
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 60 102
SELECT * FROM "Test"
ORDER BY SUBSTRING("Name" FROM 1 FOR 50)
PLAN SORT ((Test NATURAL))
------ Performance info ------
Prepare time = 0ms
Execute time = 4s 812ms
Avg fetch time = 0,24 ms
Current memory = 34 645 532
Max memory = 95 473 164
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 41 534
SELECT * FROM "Test"
ORDER BY "Name"
------ Performance info ------
Prepare time = 0ms
Execute time = 2s 766ms
Avg fetch time = 0,14 ms
Current memory = 34 644 548
Max memory = 95 473 164
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 41 534
