Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL performance on index?

Re: SQL performance on index?

From: Frank Langelage <frank_at_lafr.de>
Date: Sun, 11 Jun 2006 18:35:48 +0200
Message-ID: <4f2v35F1hdblkU1@individual.net>


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 Sun Jun 11 2006 - 11:35:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US