Database Indexing-CodePudding

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.

CodePudding user response：

No - as far as I can see, your query is incorrect and your index won't achieve your desired outcome:

query

sum(sum(qty))

should should be replaced by a single sum as follows:

sum(qty)

the additional wrapper sum() I would expect to cause your query to error.

index

Using just the following you would achieve a significantly better performance outcome than your suggested index:

  CREATE INDEX IDX_PO ON PlaceOrder(OrderNumber);

I say this because:

you are grouping by order number so that should be your top level indexed column
individual line number ordering is irrelevant for your query so is not needed in your index
because you have placed qty as the top level indexed column the OrderNumber index grouping component is rendered useless - so I would expect Oracle to ignore your index and do a full table scan.

However to additionally exclude table access requirement entirely for your specific query you should include qty in your index as follows:

CREATE INDEX IDX_PO ON PlaceOrder(OrderNumber, qty);

As holding additional information in the index of course duplicates the information in the base table one of course should always weigh up the performance benefit vs the additional space requirement.

I hope this proves useful.

CodePudding user response：

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(*).