Home » SQL & PL/SQL » SQL & PL/SQL » Creating Indexes
Creating Indexes [message #364468] Mon, 08 December 2008 13:04 Go to next message
Reporting
Messages: 6
Registered: December 2008
Junior Member
Hi All,

I am new to this forum and Oracle,

I need to tune one of my SQL query which is taking 40 mins to execute as its going for full table scan.I need to put index on the table but i have never used indexes.Could you please help?

Please find below my query :

select

A.TRH_DCN as DCN,

A.TRH_RECEIVERAPPLICATION as Receiver,

A.TRH_TRANSACTION as Transaction,

to_char(to_date(A.TRH_DATETIMEEXTRACTED,'yyyy/mm/ddhh24miss')+1,'dd/mm/yyyy') ||' 7:00:00 AM' as Request,

B.TRH_DATETIMEEXTRACTED as Response

FROM MERCATOR_REPOSITORY.TCN_ROUTE_HISTORY A inner join MERCATOR_REPOSITORY.TCN_ROUTE_HISTORY B on
A.TRH_DCN=B.TRH_TRNVALUE
Re: Creating Indexes [message #364469 is a reply to message #364468] Mon, 08 December 2008 13:09 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Try creating indexes on the columns that are used in the join conditions:

CREATE INDEX a_idx ON TCN_ROUTE_HISTORY (TRH_DCN);

CREATE INDEX b_idx ON TCN_ROUTE_HISTORY (TRH_TRNVALUE);

Oops, just noticed that the table names are the same, so this is a self-join. In that case, perhaps one composite index would do:

CREATE INDEX ab_idx ON TCN_ROUTE_HISTORY (TRH_DCN, TRH_TRNVALUE);

Are you sure you need the self-join? I know that you will get different results without it, but don't know what you are trying to achieve.

[Updated on: Mon, 08 December 2008 13:14]

Report message to a moderator

Previous Topic: scrambling production data
Next Topic: Save pdf file content to BLOB field in Oracle
Goto Forum:
  


Current Time: Thu Dec 08 20:16:28 CST 2016

Total time taken to generate the page: 0.39534 seconds