on 12-22-2008 6:13 AM
Hi
I have an SQL that has poor performance when querying a 3 table view.
I have tried to tune the view by adding an additional index but the EXPLAIN plan does not change. In fact, the plan is ONLY using fields specified in the the views join conditions and not the WHERE clause.
I have created an index EKET~Z1 with the column for table (T003)
EINDT
and I have also tried another index EKKO~Z2 with the columns for table T001
BSART
BSTYP
EKORG
The query is as follows:
SQL Statement
SELECT
"MANDT" , "EBELN" , "EBELP" , "ETENR" , "EKKO_LOEKZ" , "BSTYP" ,
"BSART" , "BUKRS" , "EKORG" , "EKGRP" , "STATU" , "AEDAT" , "ERNAM" ,
"BEDAT" , "LIFNR" , "ADRNR" , "WAERS" , "EKPO_LOEKZ" , "EKPO_BSTYP" ,
"TXZ01" , "MATNR" , "EMATN" , "WERKS" , "MATKL" , "IDNLF" , "MENGE" ,
"MEINS" , "NETPR" , "NETWR" , "PSTYP" , "KNTTP" , "WEPOS" , "ANFNR" ,
"PRDAT" , "KONNR" , "KTPNR" , "ELIKZ" , "EREKZ" , "EINDT" , "SLFDT" ,
"LPEIN" , "EKET_MENGE" , "EKET_WEMNG" , "BANFN" , "BNFPO" , "MBDAT" ,
"WADAT" , "WEBRE" , "FRGRL" , "ZZKONNR" , "ZZKTPNR" , "MAHNZ" ,
"MAHN1" , "MAHN2" , "MAHN3" , "INFNR"
FROM
"ZPO_DETAIL"
WHERE
"MANDT" = ? AND "EKORG" = ? AND "BSTYP" = ? AND "BSART" = ? AND
"WERKS" = ? AND "EINDT" < ? AND "EKPO_LOEKZ" <> ? AND "ELIKZ" <> ?
ORDER BY
"EBELN" , "EBELP" , "ETENR"
VIEW DETAILS
CREATE VIEW ZPO_DETAIL
(MANDT,
EBELN,
EBELP,
ETENR,
EKKO_LOEKZ,
BSTYP,
BSART,
BUKRS,
EKORG,
EKGRP,
STATU,
AEDAT,
ERNAM,
BEDAT,
LIFNR,
ADRNR,
WAERS,
EKPO_LOEKZ,
EKPO_BSTYP,
TXZ01,
MATNR,
EMATN,
WERKS,
MATKL,
IDNLF,
MENGE,
MEINS,
NETPR,
NETWR,
PSTYP,
KNTTP,
WEPOS,
ANFNR,
PRDAT,
KONNR,
KTPNR,
ELIKZ,
EREKZ,
EINDT,
SLFDT,
LPEIN,
EKET_MENGE,
EKET_WEMNG,
BANFN,
BNFPO,
MBDAT,
WADAT,
WEBRE,
FRGRL,
ZZKONNR,
ZZKTPNR,
MAHNZ,
MAHN1,
MAHN2,
MAHN3 )
AS SELECT
T0003."MANDT",
T0003."EBELN",
T0003."EBELP",
T0001."ETENR",
T0002."LOEKZ",
T0002."BSTYP",
T0002."BSART",
T0002."BUKRS",
T0002."EKORG",
T0002."EKGRP",
T0002."STATU",
T0002."AEDAT",
T0002."ERNAM",
T0002."BEDAT",
T0002."LIFNR",
T0002."ADRNR",
T0002."WAERS",
T0003."LOEKZ",
T0003."BSTYP",
T0003."TXZ01",
T0003."MATNR",
T0003."EMATN",
T0003."WERKS",
T0003."MATKL",
T0003."IDNLF",
T0003."MENGE",
T0003."MEINS",
T0003."NETPR",
T0003."NETWR",
T0003."PSTYP",
T0003."KNTTP",
T0003."WEPOS",
T0003."ANFNR",
T0003."PRDAT",
T0003."KONNR",
T0003."KTPNR",
T0003."ELIKZ",
T0003."EREKZ",
T0001."EINDT",
T0001."SLFDT",
T0001."LPEIN",
T0001."MENGE",
T0001."WEMNG",
T0001."BANFN",
T0001."BNFPO",
T0001."MBDAT",
T0001."WADAT",
T0003."WEBRE",
T0002."FRGRL",
T0003."ZZKONNR",
T0003."ZZKTPNR",
T0003."MAHNZ",
T0003."MAHN1",
T0003."MAHN2",
T0003."MAHN3"
FROM
SAPCEP."EKET" T0001,
SAPCEP."EKKO" T0002,
SAPCEP."EKPO" T0003
WHERE
T0002."MANDT" = T0003."MANDT" AND
T0002."EBELN" = T0003."EBELN" AND
T0003."MANDT" = T0001."MANDT" AND
T0003."EBELN" = T0001."EBELN" AND
T0003."EBELP" = T0001."EBELP"
EXPLAIN PLAN
-
Execution Plan for SQL Optimizer
TABLENAME COLUMN OR INDEX STRATEGY PAGECOUNT
T0003 RANGE CONDITION FOR KEY 15758
MANDT (USED KEY COLUMN)
T0001 JOIN VIA MULTIPLE KEY COLUMNS 8632
MANDT (USED KEY COLUMN)
EBELN (USED KEY COLUMN)
T0002 JOIN VIA MULTIPLE KEY COLUMNS 40109
MANDT (USED KEY COLUMN)
EBELN (USED KEY COLUMN)
EBELP (USED KEY COLUMN)
NO TEMPORARY RESULTS CREATED
SHOW RESULT IS COPIED , COSTVALUE IS 31186
SHOW QUERYREWRITE - APPLIED RULES:
SHOW MergeFromSelectOrView 1
The statistics are up-to-date.
Any tips would be welcome.
Thanks
Doug
> I have created an index EKET~Z1 with the column for table (T003)
> EINDT
>
> and I have also tried another index EKKO~Z2 with the columns for table T001
> BSART
> BSTYP
> EKORG
>
SQL Statement
>
> SELECT
> "MANDT" , "EBELN" , "EBELP" , "ETENR" , "EKKO_LOEKZ" , "BSTYP" ,
> FROM
> "ZPO_DETAIL"
> WHERE
> "MANDT" = ? AND "EKORG" = ? AND "BSTYP" = ? AND "BSART" = ? AND
> "WERKS" = ? AND "EINDT" < ? AND "EKPO_LOEKZ" <> ? AND "ELIKZ" <> ?
> ORDER BY
> "EBELN" , "EBELP" , "ETENR"
>
> EXPLAIN PLAN
> -
>
> Execution Plan for SQL Optimizer
>
> TABLENAME COLUMN OR INDEX STRATEGY PAGECOUNT
>
> T0003 RANGE CONDITION FOR KEY 15758
> MANDT (USED KEY COLUMN)
>
> T0001 JOIN VIA MULTIPLE KEY COLUMNS 8632
> MANDT (USED KEY COLUMN)
> EBELN (USED KEY COLUMN)
>
> T0002 JOIN VIA MULTIPLE KEY COLUMNS 40109
> MANDT (USED KEY COLUMN)
> EBELN (USED KEY COLUMN)
> EBELP (USED KEY COLUMN)
>
> NO TEMPORARY RESULTS CREATED
> SHOW RESULT IS COPIED , COSTVALUE IS 31186
> SHOW QUERYREWRITE - APPLIED RULES:
> SHOW MergeFromSelectOrView 1
>
>
> The statistics are up-to-date.
> Any tips would be welcome.
Hi Doug,
here we go...
What MaxDB version are you using? Did you check whether the DB parameters all comply to the recommendations?
If so, what are the cardinalities of the columns you indexed (a.k.a. how many different values do each of them contain?)
If the specification of BSART, BSTYP and EKORG does not reduce the number of rows to be retrieved to a large extent, then the indexaccess is likely to produce additional work.
Anyhow, the join order looks a bit odd here - this might be the problem caused by having set OPTIMIZE_OPERATOR_JOIN_COSTFUNC = YES in a MaxDB version between 7.6.04 and 7.6.06.
So make sure it's set to NO (see note 814704 MaxDB Version 7.6 parameter settings for OLTP/BW).
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.