SQL optimization distinct

Question

Suppose I have an SQL query using Oracle SQo Server to get the data from the database as below

select  distinct ORDER_TYPE_name as ORDER_TYPE from 
PRODUCT_MASTER_LIST where PROJECT_ID = 99999
order by  ORDER_TYPE ASC 

I now have 5000 records with the following order Types:

Red
Yellow
Green
Black 
null
Unclassified

How to optimise the performance by shortening the query execution time?

Note When I see the execution plan, there are many full access through the table?

Answer 1

You can define an index on those two columns to prevent table scans. That should bring down the execution time by a significant extent.

CREATE INDEX IX_ProductMasterList_OrderType 
ON PRODUCT_MASTER_LIST(PROJECT_ID, ORDER_TYPE);

Answer 2

I think index on PROJECT_ID could be the right solution. It depends on selectivity of this column.

CREATE INDEX PRODUCT_ML_PRODUCT_ID_IDX ON PRODUCT_MASTER_LIST(PRODUCT_ID);