| Creating Indexes [message #364468] |
Mon, 08 December 2008 13:04  |
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
