Home » SQL & PL/SQL » SQL & PL/SQL » Querry taking a lot of time
Querry taking a lot of time [message #241400] Tue, 29 May 2007 08:11 Go to next message
nivinishant
Messages: 2
Registered: May 2007
Junior Member
Hi,

A report needs to be generated which querries from two tables. Both tables contains more than 20,000 rows.
The querries look for the longer/shorter match for every entry in first table in the second table.
The querry I have written is taking a lot of time(more than 2 hrs)
Can anyone suggest a better way out so that it takes alittle less time?
The querry i wrote is written below:

SELECT TNCNS.CNS_OLO_CODE,
TNCNS.CNS_IDENT,
TOLO.OLO_DESCRIPTION
FROM T_CNS TNCNS, --table with more than 20000 entries
T_OPERATOR TOLO
WHERE TNCNS.CNS_OLO_CODE IS NOT NULL
AND TNCNS.CNS_REPORT_FLAG ='Y'
AND TNCNS.CNS_OLO_CODE = TOLO.OLO_CODE
AND (TRUNC(SYSDATE) >= TRUNC(TNCNS.CNS_VALID_FROM)
AND TRUNC(SYSDATE) <= TRUNC(TNCNS.CNS_VALID_TO))
AND NOT EXISTS
(
SELECT/*+INDEX(RNS_PK)*/ 1 FROM
T_ROUTING_NUMBER_STRING TRNS, --table with more than 20000 entries
T_ROUTING_PLAN_CHANGE TRPC,
T_PROCESS_STAGE TPS
WHERE TRNS.RNS_RPC_CODE = TRPC.RPC_CODE
AND TRNS.RNS_RPC_RTP_CODE = TRPC.RPC_RTP_CODE
AND TRPC.RPC_PRC_CODE = TPS.PRC_CODE
AND TPS.PRC_IDENT IN ('IM','RA'
AND TRNS.RNS_STATUS NOT IN ('ID','RD'
AND TRUNC(SYSDATE) >= TRUNC(TRNS.RNS_VALID_FROM)
AND TRUNC(SYSDATE) <= TRUNC(TRNS.RNS_VALID_TO)
AND
(
(TNCNS.CNS_IDENT LIKE TRNS.RNS_NS_CODE||'%')
OR
(TRNS.RNS_NS_CODE LIKE TNCNS.CNS_IDENT||'%')
)
)
Re: Querry taking a lot of time [message #241506 is a reply to message #241400] Tue, 29 May 2007 16:31 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Explain will be nice addition.
Re: Querry taking a lot of time [message #241509 is a reply to message #241506] Tue, 29 May 2007 16:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And formatting.
And statistics on objects.
And descriptions.
And indexes.
And Oracle version.

Regards
Michel
Re: Querry taking a lot of time [message #241638 is a reply to message #241400] Wed, 30 May 2007 04:30 Go to previous messageGo to next message
nivinishant
Messages: 2
Registered: May 2007
Junior Member
Sorry for the vague question.
I'm reframing my question again.

There are two tables T_CNS and T_ROUTING_NUMBER_STRING.These tables have a common column CNS_IDENT and RNS_NS_CODE respectively.T_ROUTING_NUMBER_STRING has a primary key on 4 columns and RNS_NS_CODE is one of them.T_CNS table has a primary key on 2 columns and CNS_IDENT is one of them.The two tables have no common columns other than what stated above.
An index is build on primary keys on both tables.

Now a querry picks all records from T_CNS table which dont have any entry in T_ROUTING_NUMBER_STRING or its longer/shorter match for CNS_IDENT column of T_CNS table.
Since the two tables have more than 20000 rows and each entry in one table is being matched for every enrty of second table,the querry is taking more than 2 hrs to execute.
I want to know if there is any other way to check for the shorter/longer match to minimise the execution time.

Thanks and Regards,
Nivi

Re: Querry taking a lot of time [message #241703 is a reply to message #241400] Wed, 30 May 2007 08:01 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Did you read our requests for info?

Michael
Previous Topic: Ora-04052 error plz help!!!
Next Topic: funtion to count characters
Goto Forum:
  


Current Time: Wed Dec 07 16:17:28 CST 2016

Total time taken to generate the page: 0.12147 seconds