Make the index on arbid, mandt, "AUFPL" , "OBJNR" , "APLFL".
This way you will have to scan index with 5 fileds vs. scanning table
with 20+ fields.
The index should be b-tree, because is the combination of 5 fileds is
changing too much for a bitmap index.
Frank Langelage wrote:
> James Yang wrote:
> > HP rx4640 runs on HPUX (64 bits) for IntelŪ ItaniumŪ 2 , 4*1.5G CPUs,
> > 4G memory, 2.4G data buffer
> >
> >
> > SELECT
> > "AUFPL" , "OBJNR" , "APLFL"
> > FROM
> > "AFVC"
> > WHERE
> > "MANDT" = :A0 AND "ARBID" IN ( :A1 , :A2 )#
> >
> >
> > This SQL executes 11 times and get 1,215,460 blocks from disk.
> >
> > Executions Disk reads Reads/Exec Buffer gets Bgets/exec Proc.
> > rows Rproc/Exe Bgets/row
> >
> > 11 1,215,460 110,496.40 1,532,611 139,328.30 2,218,008 201,637.10 0.7
> >
> > So far, we can not move this query into BW system.
> >
> >
> > So, my question is, is there anyway to tune it?????
> >
> >
> >
> > SQL> select count(*) from sapccp.afvc;
> >
> > COUNT(*)
> > ----------
> > 4013896
> >
> > SQL> select count(distinct(arbid)) from sapccp.afvc;
> >
> > COUNT(DISTINCT(ARBID))
> > ----------------------
> > 338
> >
> >
> > SQL> select count(distinct(aufpl)) from sapccp.afvc;
> >
> > COUNT(DISTINCT(AUFPL))
> > ----------------------
> > 774009
> >
> > SQL> select count(distinct(aplfl)) from sapccp.afvc;
> >
> > COUNT(DISTINCT(APLFL))
> > ----------------------
> > 1
> >
> > In SAP world, filed MANDT is mandatory for every application data,
> > actually, you can say ONLY ONE VALUE for MANDT in the production
> > system.
> >
> snip
> >
> > Index definition, SAP standard delivered, SAP only use B-tree index for
> > OLTP (SAP R/3) system.
> >
> > index AFVC~0(key): MANDT,AUFPL,APLZL
> > index AFVC~1: MANDT,RUECK
> > index AFVC~2: MANDT,ARBID
> > index AFVC~3: MANDT,PROJN
>
> Since MANDT is the first field in every index and the column has only
> one distinct value, the indexes are not very helpful. The system will
> probably do a full table scan.
> For our app (not SAP) we would decide to switch the fields of the 2nd
> index in this case (ARBID, MANDT) because ARBID has 338 distinct values.
> But also with this you get ~20.000 rows per query (4013896/338*2).
> Adding the selected fields to the index will lead to "key only" access.
> But if this is a measurable benefit or not is hard to say without
> testing it.
Received on Tue Jun 13 2006 - 00:19:29 CDT
