View previous topic :: View next topic
|
Author |
Message |
karthik3883
New User
Joined: 19 Oct 2007 Posts: 37 Location: chennai
|
|
|
|
Hi,
In our cobol program we are doing a join between table a and table b.
Declare d-id cursor with hold for
select deal,e.code from table a,table b where
substr(a.unit_id,5,10) = b.element_code
the table b has element_code which is 10 character and in table a the unit_id is 14 character.
our dba had reviewed and given a comment saying substr needs to be replaced.
could anyone suggest a better option for this
Regards
Karthik |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I would suggest you to first understand your DBA's suggesion |
|
Back to top |
|
|
karthik3883
New User
Joined: 19 Oct 2007 Posts: 37 Location: chennai
|
|
|
|
DBA hasnt suggested anything,he informed to check for alternative logic
Regards
Karthik |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I wanted you to understand why he had asked you to change the logic.
Did you try to understand that or asked him why he said that? |
|
Back to top |
|
|
karthik3883
New User
Joined: 19 Oct 2007 Posts: 37 Location: chennai
|
|
|
|
The number of records in table a is huge. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
How many records are there in both the tables and what are you trying to achieve after doing a join on select?
Are the fields substr(a.unit_id,5,10) = b.element_code indexed?
How many records do you expect after join? |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
Quote: |
Are the fields substr(a.unit_id,5,10) = b.element_code indexed? |
Even if unit_id was indexed, it won't be used as the substr expression is not using first few bytes of column.
@OP,
If you really need to use this SUBSTR, you might want to look into 'Expression Based Indexes'. |
|
Back to top |
|
|
|