Hello Everybody,

On one of our custom report we were facing some problem and we found out that it was due to the fact that in our select statement we were using index1 in the where clause but in the order by clause we were using index2. By making them both the same our report worked just fine.

I am curious get your feed back on if we can use

a) different index in where clause and a different in order by?

b) multiple different indices in our select statement?

c) multiple different indices in our select statment with another index in order by?

In my opinion if we use an index in a where clause then our order by should use the same index or just field name(s) for sorting.

What do you guys think?

We do it all the time. _indexA in the where part and _indexB in the order by - Never casued a problem. However using two index on the same table in the where part of the clause is not recommended. Can't remember the exact reason but you should use the most efficient index and complete the where part by using fields. for example


select tdinv150.*
from tdinv150 for update
where tdinv150._index1 >= {tckoor.pcs.sfc,:curr.orno,:curr.kotr,
:curr.pono,:curr.ponb }
and tdinv150._index1 <= {tckoor.pcs.wrh }
and tdinv150.cprj inrange :cprj.f and :cprj.t
order by tdinv150._index1


Why not use index5 in tdinv150 (and tdind150._index5 inrange {:cprj.f and :cprj.t}? The actual field tdinv150.cprj was used instead. Probably someone remembers the reason why you don't.

Refer to the link for more info - Index Hint (http://www.baanboard.com/programmers_manual_baanerp_help_functions_query_hints_index_hint)

Table tccom100 must be accessed using index 2 in descending order. With one index hint you can hint more than 1 index for the table. There is a maximum of 10 indexes you can specify per index hint. If you specify more than one index hint the query processor may use one of these indexes to scan the table or it may use a combination of the indexes. Consider for example the following query:

select iscn, bpid
from tccom100
where (iscn = 570 or cadr = 'J10000001') and nama >= 'A'
hint use index 1,2 on tccom100

Assume index 1 is on column iscn and index 2 is on column cadr, furthermore assume there is an index 3 on column nama. By hinting on both index 1 and index 2 this query can be solved by firing two index scans.

Found more info in the SSA_ERP_LN-6.1 manual -

SSA ERP LN 6.1 Programmer's Guide

42I45 - Multiple index hints specified on table
--------------------------------------------------------------------------------
An SQL statement contains a HINT clause that contains more than one index hint on the same table.

Example
select *
from dbtst120
where empno > 10
and workdept = "A01"
hint use index 1 on dbtst120
and use index 2 on dbtst120
You can only specify one index per table. This query will cause the errror:

SQLState 42I45: Multiple index hints specified on table 'dbtst120'

You can use one index hint to specify multiple indexes.

select *
from dbtst120
where empno > 10
and workdept = "A01"
hint use index 1,2 on dbtst120

What u observed is correct. It's because the way the bshell interprets the queries
communicates with database driver . When it's order by indexA , it's always preferred to
use indexA only in where clause.

Also the style of writing the indices in where clause makes drastic difference. e.g.
ppmmmAAA._index1 = ppmmmBBB._index2 may be much more efficient than ppmmmBBB._index2 =
ppmmmAAA._index1 . The table with smaller number of total records in table and records to
be retruned for query should appear on left side.

More Info -
BaanERP Application Software Engineering Guides

Nested queries
--------------------------------------------------------------------------------
Problem
Nesting queries can reduce performance dramatically.

Example
Nested query:

select orders.ordernumber, orders.fld1, ...
from orders
where orders.ordernumber inrange :order.f and :order.t
and orders.status = released
selectdo
select orderlines.fld1, orderlines.fld2, ...
from orderlines
where orderlines.ordernumber = :orders.ordernumber
selectdo

Solution: Join nested queries into one -more complex- query.

select orderlines.fld1, orderlines.fld2, ...,
orders.ordernumber, orders.fld1, ...
from orderlines, orders
where orderlines.ordernumber inrange :order.f and :order.t
and orders.ordernumber = orderlines.ordernumber
and orders.status = released
selectdo
Note:
There are some attention points.

The higher the number of records at a high level in relation to the number of records at lower level, the better is the joined way of implementation.
The higher the number of selected fields (and its length is related) at a high level in relation to the number at lower level, the better the nested way of implementation.
Using the above example:

Number of orders is X, number of fields in orders is A.
Number of order lines is Y, number of fields in order lines is B.

If A is much higher than B, it is preferable to implement the nested way.
If B is much higher than A, it is preferable to implement the joined way.

If X is much higher than Y, it is preferable to implement the joined way.
If Y is much higher than X, the joined as well the nested way can be implemented.

As can be concluded, one cannot always say for sure which construction is the best.