CODEWITHSUNDEEP
sqloracle-databaseoptimization
Jeff Bootsholz
Jeff Bootsholz

Reputation: 3068

SQL optimization distinct

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?

Upvotes: 3

Views: 1301

Answers (3)

Vikdor
Vikdor

Reputation: 24134

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);

Upvotes: 4

Petr Pribyl
Petr Pribyl

Reputation: 3575

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);

Upvotes: 1

Dennis
Dennis

Reputation: 4017

If you plan to run the query often create a bind variable , this will DRASTICALLY increase performance.

Example:

create or replace procedure dsal(p_empno in number)
as
  begin
    update emp
    set sal=sal*2
    where empno = p_empno;
    commit;
  end;
/

In addition create an index on the columns you intend to query. 

CREATE INDEX 
   ix_emp_01 
ON 
   emp (deptno) 
TABLESPACE 
   index_tbs;

Note the TABLESPACE clause is optional

Upvotes: 0

Related Questions