I have a question on requirement w.r.t need for a new secondary index. The requirement is as below:
We have a root segment- Order segment - having key as "Order No". The other fields on the root segment are "customer-code", "order-date", and "ship-indicator" alongwith some other fields.
We need to display all the orders for a given "customer-code" (customer code is input by the user on the inquiry screen) on to the screen in sequence by "order-date" - order with the oldest date first. Only those orders should be displayed for which "order-date" < current-date AND for which "ship-indicator" field is blank.
The database already has secondary index on the root segment based on customer code.
However, since the requirement is to display the orders in sequence by "order-date", I am thinking of defining an index on "order-date" that should point to the root segment.
Should I define:
(1) a single index on "Order-date". OR
(2) a single index on "Order-date" AND "ship-indicator".OR
(3) a single index on "Order-date" AND "ship-indicator AND cusotmer-code".
The order for fields in the index is as given above.