Improving performance of view

From: <rbrooks_at_amconsulting.com>
Date: 1997/10/20
Message-ID: <877391859.26933@dejanews.com>#1/1

Hi, I'm trying to improve the performance of a select run against a view and boy is it ugly. The view is built with CREATE VIEW COVP as SELECT

T1.MANDT,  T1.KOKRS,  T1.BELNR,  T1.BUZEI,  T1.PERIO,  T1.WTGBTR, 
T1.WOGBTR,  T1.WKGBTR,	T1.WKFBTR,  T1.PAGBTR,	T1.PAFBTR,  T1.MEGBTR, 
T1.MEFBTR,  T1.MBGBTR,	T1.MBFBTR,  T1.LEDNR,  T1.OBJNR,  T1.GJAHR, 
T1.WRTTP,  T1.VERSN,  T1.KSTAR,  T1.HRKFT,  T1.VRGNG,  T1.PAROB, 
T1.PAROB1,  T1.USPOB,  T1.VBUND,  T1.PARGB,  T1.BEKNZ,	T1.TWAER, 
T1.OWAER,  T1.MEINH,  T1.MEINB,  T1.SGTXT,  T1.REFBZ,  T1.ZLENR, 
T1.GKONT,  T1.GKOAR,  T1.WERKS,  T1.MATNR,  T1.RBEST,  T1.EBELN, 
T1.EBELP,  T1.ZEKKN,  T1.ERLKZ,  T1.PERNR,  T1.BTRKL,  T1.MVFLG, 
T1.OBJNR_N1,  T1.OBJNR_N2,  T1.OBJNR_N3,  T1.PAOBJNR,  T1.BELTP, 
T1.BUKRS,  T1.GSBER,  T1.FKBER,  T1.SCOPE,  T1.LOGSYSO,  T1.PKSTAR, 
T1.PBUKRS,  T1.PFKBER,	T1.PSCOPE,  T1.LOGSYSP,  T2.PERAB,  T2.PERBI, 
T2.BLDAT,  T2.BUDAT,  T2.CPUDT,  T2.USNAM,  T2.BLTXT,  T2.STFLG, 
T2.STOKZ,  T2.REFBT,  T2.REFBN,  T2.REFBK,  T2.REFGJ,  T2.BLART, 
T2.ORGVG,  T2.SUMBZ,  T2.DELBZ,  T2.WSDAT,  T2.KURST,  T2.VARNR, 
T2.KWAER,  T2.CTYP1,  T2.CTYP2,  T2.CTYP3,  T2.CTYP4,  T2.AWTYP, 
T2.AWORG,  T2.LOGSYSTEM,  T2.TIMESTMP,	T2.CPUTM,  T2.ALEBZ,  T2.ALEBN, 
T2.AWSYS,  T2.AWREF_REV,  T2.AWORG_REV	FROM  COEP T1,	COBK T2  WHERE 
T2.MANDT = T1.MANDT AND  T2.KOKRS = T1.KOKRS AND  T2.BELNR = T1.BELNR The
actual SQL run against this view is SELECT  *  FROM  "COVP"  WHERE 
"MANDT" =  :A0 AND  "LEDNR" = :A1 AND  "OBJNR" BETWEEN	:A2 AND  :A3 AND 
"VERSN" =  :A4 AND  ( "WRTTP" BETWEEN  :A5 AND	:A6 OR	"WRTTP" =  :A7 )
AND  "GJAHR" BETWEEN  :A8 AND  :A9 AND	( ( "KSTAR" BETWEEN  :A10 AND 


:A11 OR  "KSTAR" BETWEEN  :A12 AND  :A13 OR  "KSTAR" BETWEEN  :A14 AND 

:A15 OR  "KSTAR" BETWEEN  :A16 AND  :A17 OR "KSTAR" BETWEEN  :A18 AND 

:A19 OR  "KSTAR" BETWEEN  :A20 AND  :A21 OR  "KSTAR" BETWEEN  :A22 AND 

:A23 OR  "KSTAR" BETWEEN  :A24 AND  :A25 OR  "KSTAR" BETWEEN  :A26 AND 

:A27 OR "KSTAR" BETWEEN :A28 AND :A29 OR "KSTAR" BETWEEN :A30 AND
:A31 OR "KSTAR" BETWEEN :A32 AND :A33 OR "KSTAR" BETWEEN :A34 AND
:A35 ) OR "KSTAR" IN ( :A36 , :A37 , :A38 , :A39 , :A40 , :A41 ,
:A42 , :A43 , :A44 , :A45 , :A46 , :A47 , :A48 , :A49 , :A50 ,
:A51 , :A52 , :A53 , :A54 , :A55 , :A56 , :A57 ) ) AND "BUDAT"
BETWEEN :A58 AND :A59 ORDER BY "MANDT" , "OBJNR" The indices involved

are: ------------------------------ Field name | Posi | Desc  |
------------------------------ COEP  Primary key  |
------------------------------ MANDT  | 0001 | KOKRS  | 0002 |	| BELNR 
| 0003 |  | BUZEI  | 0004 |  | ------------------------------ COEP  1  |
------------------------------ MANDT  | 0001 |	| LEDNR  | 0002 |  |
OBJNR  | 0003 |  | GJAHR  | 0004 |  | WRTTP  | 0005 |  | VERSN	| 0006 | 

| KSTAR | 0007 | | HRKFT | 0008 | | PERIO | 0009 | | VRGNG | 0010 | | PAROB | 0011 | | USPOB | 0012 | | VBUND | 0013 | | PARGB | 0014 | | BEKNZ | 0015 | | TWAER | 0016 | |

------------------------------ COBK  Primary key  |
------------------------------ MANDT  | 0001 |	| KOKRS  | 0002 |  |
BELNR  | 0003 |  | ----------------------------- COBK  O  |
----------------------------- REFBT  | 0001 |  | REFBN	| 0002 |  | KOKRS

 | 0003 | | REFBK | 0004 | | REFGJ | 0005 | |

---

The explain plan I'm getting is:
Execution Plan

---

 SELECT STATEMENT ( Estimated Costs = 0 )   |

• SORT ORDER BY |
  • NESTED LOOPS | |-- TABLE ACCESS BY ROWID COEP | | | ------INDEX RANGE SCAN COEP______1 |
    • TABLE ACCESS BY ROWID COBK | ------INDEX UNIQUE SCAN COBK______0

The tables are big (COBK has 130K records, COEP has 2.9M records) and expected to grow much larger. Due to the origin of the view, and so forth, embedded hints are probably not a realistic option. Does anyone have any suggestions or ideas?

Regards,
Russ

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Mon Oct 20 1997 - 00:00:00 CDT