From: Date: October 15 2003 2:09pm
Subject: RE: Optimization Question
List-Archive: http://lists.mysql.com/maxdb/18620
Message-Id: <B9A2DDA03044D311BBD40008C75D69680EC6C6FF@dewdfx24.wdf.sap.corp>
MIME-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"

Robert Meier wrote:

> We have a SAPDB 7.3.0 BUILD 043-000, database,
>  
> I am having performance problems on this database, I have noticed the
> following, when I have for instance a table like the 
> following with the
> first five columns as part of the primary key.
>  
> Table : Test
>  
> Columns
> Test1  - Primary Key
> Test2  - Primary Key
> Test3  - Primary Key
> Test4  - Primary Key
> Test5  - Primary Key
> Test6
> Test7
> Test8
> .......
>  
> If I have a SQL as follows,
>  
> Select * 
> From Test
> Where  Test1 = 'A' and Test2 = 'B' and Test3 = 'C' and Test4 = 'D' and
> Test5 = 'E'
>  
> Then the optimizer will use all the key columns and the select will be
> quite fast, if I change the SQL to the following
>  
> Select * 
> From Test
> Where  Test1 = 'A' and Test2 = 'B' and Test3 IN  ('C','D') and Test4 =
> 'E' and Test5 = 'F'
>  
> Then the optimizer will only use Test1 and Test2, and scan through the
> rest of the table!
> This does not seem right to me, can't the optimizer still use the rest
> of the key columns?
> The same happens when the IN statement is changed to a ( OR ) 
> statement!
> Is there perhaps a parameter I can change to allow the optimizer to
> function across a IN statement?

The usage of the IN predicate will result in costs of 2 pages because 
every IN value will be handled as a separate access.

So if we assume the range given by key columns test1 and test2 is smaller 
or equal 2 pages this will be a better or at least as good strategy.

If this is not the case I'm very interested in an strategy vtrace.
For a strategy vtrace follow the instructions described in 
http://sapdb.2scale.net/moin.cgi/VTrace and expand the command
in step 1 with the keyword optimize, i.e. 

dbmcli -d <dbname> -u <dbm,dbm> util_execute diagnose vtrace default optimize on 

Best regards,
Holger
SAP Labs Berlin