| Help SQL Tuning [message #515431] |
Mon, 11 July 2011 09:25  |
nqtrung
Messages: 25
Registered: April 2007
|
Junior Member |
|
|
I have 2 tables: table_A & table_B. Table_B have CODE column that containt MOSEQ column of table_A. And I execute SQL statement:
select * from table_A A
where exists (select * from table_B B where A.moseq=substr(B.code,length(B.code)-length(A.moseq)+1,length(A.moseq)));
This statement run very slowly (45 minutes). Could you help me, pls
Thanks
|
|
|
|
|
|
| Re: Help SQL Tuning [message #515434 is a reply to message #515432] |
Mon, 11 July 2011 09:32  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Highly likely that this is the main problem:
where A.moseq=substr(B.code,length(B.code)-length(A.moseq)+1,length(A.moseq)));
You need to explain that linkage with sample data.
|
|
|
|
| Re: Help SQL Tuning [message #515443 is a reply to message #515431] |
Mon, 11 July 2011 10:22 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I should say, if I understand your condition:
where B.code like '%'||A.moseq
but this does not solve the performances problem, just tell me if my condition is correct (it is better to explain the query with words then to let us reverse engineer it with the errors and waste of time this implies).
In addition: "what are the types of both columns?"
Regards
Michel
[Updated on: Mon, 11 July 2011 10:23] Report message to a moderator |
|
|
|
|
|
| Re: Help SQL Tuning [message #515500 is a reply to message #515434] |
Mon, 11 July 2011 23:29 |
nqtrung
Messages: 25
Registered: April 2007
|
Junior Member |
|
|
cookiemonster wrote on Mon, 11 July 2011 09:32Highly likely that this is the main problem:
where A.moseq=substr(B.code,length(B.code)-length(A.moseq)+1,length(A.moseq)));
You need to explain that linkage with sample data.
I have this condition because B.code='abcxyz'||A.moseq but both 'abcxyz' and A.moseq column don't fix length.
|
|
|
|
|
|
|
|
|
|
| Re: Help SQL Tuning [message #515634 is a reply to message #515538] |
Tue, 12 July 2011 10:00 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
If the columns are VARCHAR2, they are *not* right-padded with blanks. He doesn't have to use substr functions to compare the two values.
And, obviously, they are not NUMBER datatypes.
Hemant K Chitale
|
|
|
|
|
|
| Re: Help SQL Tuning [message #515686 is a reply to message #515635] |
Tue, 12 July 2011 22:35 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
This might work. Note that it uses an undocumented function REVERSE(), so you use it at your own risk knowing that Oracle may cease to support it - or change its functionality - sometime in the future.
create index table_b_code_rev on table_b(reverse(code));
select *
from table_A A
where exists (
select *
from table_B B
where reverse(b.code) like reverse(a.code) || '%'
);
|
|
|
|
|
|
| Re: Help SQL Tuning [message #515732 is a reply to message #515686] |
Wed, 13 July 2011 02:32 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
rleishman wrote on Wed, 13 July 2011 05:35This might work. Note that it uses an undocumented function REVERSE(), so you use it at your own risk knowing that Oracle may cease to support it - or change its functionality - sometime in the future.
create index table_b_code_rev on table_b(reverse(code));
select *
from table_A A
where exists (
select *
from table_B B
where reverse(b.code) like reverse(a.code) || '%'
);
This was what I thought but it works ONLY if the columns are strings which we still don't know as OP refuse to answer.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
