"ORDER BY"-clause enforces full table table scan
"JUNG, Christian" <[email protected]>
| Newsgroups | gmane.comp.db.maxdb |
|---|---|
| Message-ID | <[email protected]> |
Hi!
I've discovered a rather strange behaviour of SAP DB version 7.4.3.27 on
Linux
(SLES 8, Kernel 2.4.21):
EXPLAIN SELECT * FROM "T1" WHERE "COL_037" = '318048655605'
OWNER|TABLENAME|COLUMN_OR_INDEX|STRATEGY
|PAGECOUNT|O|D|T|M
TEST |T1 |I9 |RANGE CONDITION FOR INDEX |
9115| | | |
| |COL_037 | (USED INDEX COLUMN) |
| | | |
TEST | | | RESULT IS NOT COPIED , COSTVALUE IS|
97| | | |
This is a very good result and pretty fast, because the DBMS can use the
defined index I9.
Now the strange stuff:
If an "ORDER BY"-clause is added to the same statement, a full table scan is
done.
SELECT * FROM "T1" WHERE "COL_037" = '318048655605' ORDER BY
"COL_023"
This statement results in a full table scan, if the table contains approx.
1.400.000 rows. If the table contains only about 1.000.000 rows, the index
I9
is used as well. The result set contains around 270 rows.
Is there any possibility to avoid the full table scan if an "ORDER BY"
clause
is defined? The DBMS could use the index I9 for sorting purposes. Or is this
maybe a bug in this SAP DB version?
The statistics are updated at regular basis.
We will migrate soon to version 7.6, so this isn't a problem if the
behaviour
of the optimizer has changed (hope it has :-)).
Thanks a lot for your help!
Christian Jung
PS: Here's the table and index definition:
-- TABLE : T1, ID: 0x0000000000004037
-- OWNER : TEST
-- COMMENT :
-- PRIVILEGES : SEL+UPD+DEL+INS+REF+IND+ALT+
-- CREATED : 2006-11-16 12:56:33
-- LAST ALTER : 2006-11-17 11:44:05
-- LAST UPDSTAT: 2006-11-20 21:36:52
-- UNLOADED : NO
CREATE TABLE "T1" (
"ID" FIXED(28,0) NOT NULL,
"COL_001" VARCHAR(3) ASCII,
"COL_002" VARCHAR(5) ASCII,
"COL_003" VARCHAR(5) ASCII,
"COL_004" VARCHAR(35) ASCII,
"COL_005" VARCHAR(35) ASCII,
"COL_006" VARCHAR(18) ASCII,
"COL_007" VARCHAR(35) ASCII,
"COL_008" INTEGER,
"COL_009" VARCHAR(35) ASCII,
"COL_010" VARCHAR(70) ASCII,
"COL_011" VARCHAR(3) ASCII,
"COL_012" VARCHAR(20) ASCII,
"COL_013" VARCHAR(70) ASCII,
"COL_014" INTEGER,
"COL_015" VARCHAR(70) ASCII,
"COL_016" VARCHAR(70) ASCII,
"COL_017" VARCHAR(18) ASCII,
"COL_018" VARCHAR(70) ASCII,
"COL_019" VARCHAR(70) ASCII,
"COL_020" VARCHAR(3) ASCII,
"COL_021" VARCHAR(18) ASCII,
"COL_022" VARCHAR(17) ASCII,
"COL_023" VARCHAR(14) ASCII,
"COL_024" VARCHAR(18) ASCII,
"COL_025" VARCHAR(20) ASCII,
"COL_026" VARCHAR(5) ASCII,
"COL_027" VARCHAR(5) ASCII,
"COL_028" VARCHAR(35) ASCII,
"COL_029" VARCHAR(35) ASCII,
"COL_030" VARCHAR(70) ASCII,
"COL_031" VARCHAR(25) ASCII,
"COL_032" VARCHAR(35) ASCII,
"COL_033" VARCHAR(35) ASCII,
"COL_034" VARCHAR(70) ASCII,
"COL_035" VARCHAR(25) ASCII,
"COL_036" VARCHAR(70) ASCII,
"COL_037" VARCHAR(17) ASCII,
"COL_038" INTEGER,
"COL_039" VARCHAR(17) ASCII,
"COL_040" VARCHAR(3) ASCII,
"COL_041" VARCHAR(3) ASCII,
"COL_042" VARCHAR(4) ASCII,
"COL_043" VARCHAR(7) ASCII,
"COL_044" VARCHAR(4) ASCII,
"COL_045" VARCHAR(20) ASCII,
"COL_046" VARCHAR(18) ASCII,
"COL_047" VARCHAR(25) ASCII,
"COL_048" VARCHAR(3) ASCII,
"COL_049" VARCHAR(17) ASCII,
"COL_050" VARCHAR(70) ASCII,
"COL_051" VARCHAR(70) ASCII,
"COL_052" VARCHAR(18) ASCII,
"COL_053" VARCHAR(18) ASCII,
"COL_054" VARCHAR(255) ASCII,
"COL_055" FLOAT(16),
"COL_056" INTEGER,
"COL_057" VARCHAR(17) ASCII,
"COL_058" VARCHAR(70) ASCII,
"COL_059" VARCHAR(15) ASCII,
"COL_060" FLOAT(16),
"COL_061" VARCHAR(70) ASCII,
"COL_062" VARCHAR(20) ASCII,
"COL_063" INTEGER,
"COL_064" VARCHAR(4) ASCII,
"COL_065" VARCHAR(640) ASCII,
"COL_066" INTEGER,
"COL_067" INTEGER,
"COL_068" VARCHAR(70) ASCII,
"COL_069" VARCHAR(3) ASCII,
"COL_070" VARCHAR(8) ASCII,
"COL_071" INTEGER,
"COL_072" VARCHAR(3) ASCII,
"COL_073" VARCHAR(256) ASCII,
"COL_074" VARCHAR(35) ASCII,
"COL_075" VARCHAR(1) ASCII,
"COL_076" VARCHAR(20) ASCII,
"COL_077" VARCHAR(35) ASCII,
"COL_078" VARCHAR(3) ASCII,
"COL_079" VARCHAR(3) ASCII,
"COL_080" VARCHAR(25) ASCII,
"COL_081" VARCHAR(7) ASCII,
"COL_082" VARCHAR(70) ASCII,
"COL_083" VARCHAR(35) ASCII,
"COL_084" VARCHAR(35) ASCII,
"COL_085" VARCHAR(35) ASCII,
"COL_086" VARCHAR(20) ASCII,
"COL_087" VARCHAR(4) ASCII,
"COL_088" VARCHAR(1) ASCII,
"COL_089" VARCHAR(70) ASCII,
"COL_090" VARCHAR(3) ASCII,
"COL_091" VARCHAR(512) ASCII,
"COL_092" VARCHAR(512) ASCII,
"COL_093" VARCHAR(512) ASCII,
"COL_094" VARCHAR(70) ASCII,
"COL_095" VARCHAR(25) ASCII,
"COL_096" VARCHAR(35) ASCII,
"COL_097" VARCHAR(35) ASCII,
"COL_098" VARCHAR(17) ASCII,
"COL_099" VARCHAR(25) ASCII,
"COL_100" VARCHAR(3) ASCII,
"COL_101" FIXED(28,0),
"COL_102" INTEGER,
"COL_103" VARCHAR(35) ASCII,
"COL_104" VARCHAR(35) ASCII,
"COL_105" FIXED(28,0),
"COL_106" VARCHAR(20) ASCII,
"COL_107" VARCHAR(7) ASCII,
"COL_108" VARCHAR(10) ASCII,
"COL_109" VARCHAR(4) ASCII,
"COL_110" VARCHAR(5) ASCII,
"COL_111" VARCHAR(10) ASCII,
"COL_112" CHAR(1) ASCII,
"COL_113" CHAR(1) ASCII,
"COL_114" CHAR(1) ASCII,
"COL_115" VARCHAR(40) ASCII,
"COL_116" CHAR(1) ASCII,
"COL_117" CHAR(1) ASCII,
"COL_118" VARCHAR(3) ASCII,
"COL_119" VARCHAR(70) ASCII,
"COL_120" VARCHAR(70) ASCII,
"COL_121" VARCHAR(20) ASCII,
"COL_122" VARCHAR(20) ASCII,
"COL_123" CHAR(1) ASCII,
"COL_124" VARCHAR(50) ASCII,
"COL_125" INTEGER,
"COL_126" VARCHAR(25) ASCII,
"COL_127" VARCHAR(5) ASCII,
"COL_128" FIXED(28,0),
"COL_129" VARCHAR(14) ASCII,
"COL_130" VARCHAR(1) ASCII,
"COL_131" FLOAT(16),
"COL_132" VARCHAR(50) ASCII,
PRIMARY KEY ("ID")
) SAMPLE 20000 ROWS
--
CREATE INDEX "I1" ON "T1" ("COL_037" ASC)
--
CREATE INDEX "I2" ON "T1" ("COL_023" ASC)
--
CREATE INDEX "I3" ON "T1" ("COL_086" ASC)
--
CREATE INDEX "I4" ON "T1" ("COL_105" ASC)
--
CREATE INDEX "I5" ON "T1" ("COL_101" ASC)
--
CREATE INDEX "I6" ON "T1" ("COL_023" ASC,"COL_105" ASC)
--
CREATE INDEX "I7" ON "T1" ("COL_111" ASC)
--
CREATE INDEX "I8" ON "T1" ("COL_130" ASC)
--
CREATE INDEX "I9" ON "T1" ("COL_037" ASC,"COL_023" ASC)
--
phone: +49 6898/10-4987
fax: +49 6898/10-54987
http://www.saarstahl.de
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[email protected]