Oracle, inner join and substr function
-
Hi everybody. My problem: I have a query and it takes too much time to fetch, when I use substr funtion to create an inner join between a table and a view... my code:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
where D.NUM='1344628596434'The problem is:
on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
The query doesn't take too long time, when I do this:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= '134' and T.CODE_TWO='4628596434'
where D.NUM='1344628596434'But I cannot use static values... thanks for the help
-
Hi everybody. My problem: I have a query and it takes too much time to fetch, when I use substr funtion to create an inner join between a table and a view... my code:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
where D.NUM='1344628596434'The problem is:
on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
The query doesn't take too long time, when I do this:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= '134' and T.CODE_TWO='4628596434'
where D.NUM='1344628596434'But I cannot use static values... thanks for the help
Ludwing RS wrote:
I have a query and it takes too much time to fetch
How much time would be allowed? There's no faster alternative to splitting a string, but it does count as a design-mistake. Each attribute in the tupel should be atomic. You should not need to separate the facts from a single field. They should have been two separate columns. You "could" try to create an view that does the splitting, and making sure that those calculated columns are materialized. It would effectively move the extra processing required to when the view is created, rather then when the data is requested.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Hi everybody. My problem: I have a query and it takes too much time to fetch, when I use substr funtion to create an inner join between a table and a view... my code:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
where D.NUM='1344628596434'The problem is:
on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
The query doesn't take too long time, when I do this:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= '134' and T.CODE_TWO='4628596434'
where D.NUM='1344628596434'But I cannot use static values... thanks for the help
Function based index to the rescue.
create index myTable_num_fnix
on myTable (substr(NUM,0,3),substr(NUM,3,15));Yes, both conditions needs to be in the same index. Oracle is using only one index per table and query. <edit>Fixed typo</edit>
Wrong is evil and must be defeated. - Jeff Ello
-
Function based index to the rescue.
create index myTable_num_fnix
on myTable (substr(NUM,0,3),substr(NUM,3,15));Yes, both conditions needs to be in the same index. Oracle is using only one index per table and query. <edit>Fixed typo</edit>
Wrong is evil and must be defeated. - Jeff Ello
Thanks for the answers... I don't have permissions to modify the tables, creating indexes, etc