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
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
