Database Indexing

Question

I'm trying to figure out on how to create an Index for below query such that the SELECT statement only traverse the leaf level of the index horizontally and it does not access the relational table. I'm working on a relational database in Oracle.

SELECT SUM(SUM(qty))
FROM PlaceOrder
GROUP BY OrderNumber
HAVING COUNT(LineNumber) > 10;

Am I correct to create the below index?

CREATE INDEX IDX_PO 
    ON PlaceOrder(qty, OrderNumber, LineNumber);

Thank you.

Answer 1

As commented by astentx, not null constraint is needed. In fact, as long as any of qty, OrderNumber, LineNumber has not null, Oracle should be able to use the index.

Also, note that unless you specially want to exclude lines with null LineNumber, you can replace COUNT(LineNumber) with COUNT(OrderNumber) or even COUNT(*).