Hi.
I need some help.
In my database exists table:
CREATE TABLE "TABEL"."TABEL"
(
"KODPODR" Varchar (10) ASCII,
"GOD" Varchar (4) ASCII,
"MES" Varchar (2) ASCII,
"DEN" Varchar (2) ASCII,
"TABN" Varchar (5) ASCII,
"KRM" Varchar (2) ASCII,
"CHAS" Fixed (5,2),
"NOCHN" Fixed (5,2),
"PRAZDN" Fixed (5,2),
"SV" Varchar (10) ASCII,
"GR" Varchar (2) ASCII
)
In this table 1.5 million records.
Field DEN (not unique) takes values within the range of '1'..'31'
Field TABN is not unique.
TABN and DEN is not unique.
I has done following:
1.
CREATE INDEX "IND8" ON "TABEL"."TABEL"("TABN" ASC)
2.
CREATE VIEW "PEREKR_INNER1" AS
SELECT TABEL.*
, CASE WHEN CHAS=0 OR CHAS IS NULL THEN '' ELSE RTRIM(RTRIM(CHAS,'0'),'.')
END || CASE WHEN NOCHN=0 OR NOCHN IS NULL THEN '' ELSE '/' ||
RTRIM(RTRIM(NOCHN,'0'),'.') END || CASE WHEN PRAZDN=0 OR PRAZDN IS NULL THEN
'' ELSE CASE WHEN NOCHN=0 OR NOCHN IS NULL THEN '/' ELSE '' END || '/' ||
RTRIM(RTRIM(PRAZDN,'0'),'.') END || CASE WHEN SV IS NULL THEN '' ELSE CASE
WHEN (CHAS=0 OR CHAS IS NULL) AND (NOCHN=0 OR NOCHN IS NULL) AND (PRAZDN=0
OR PRAZDN IS NULL) THEN '' ELSE '/' END || SV END
AS CELL
FROM TABEL
3.
explain select * from perekr_inner1 where tabn='6024'
with result
TABEL PEREKR_INNER1 IND8 EQUAL CONDITION FOR INDEX
4887
TABEL RESULT IS NOT COPIED , COSTVALUE IS
13
4.
CREATE VIEW "PEREKR"
AS SELECT KODPODR, GOD, MES, TABN, KRM,GR
,MAX(CASE WHEN DEN='1' THEN CELL ELSE '' END) AS "d1"
......
,MAX(CASE WHEN DEN='31' THEN CELL ELSE '' END) AS "d31"
FROM PEREKR_INNER1
5.
explain select * from perekr where tabn='6024'
TABEL PEREKR_INNER1 TABLE SCAN
11373
INTERNAL TEMPORARY RESULT TABLE SCAN
500
TABEL RESULT IS COPIED , COSTVALUE IS
471534
I don't understand, why select by view perekr do not use index ind8?
Have SAP DB any hints for select?
Thanks.
Valery.
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
