| Joins / substr / LIKE [message #21290] |
Wed, 24 July 2002 12:36  |
Ron Hagan
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Part of my sql looks like this:
select a.field1, a.field2, b.field3
from table1 a, table2 b
where substr(a.field1,1,15) = b.field1
How do I make this more efficient? I have to do similar comparisons often. If I am using a variable, I can concatenate a ‘%’ to the end of the variable and use a LIKE condition. The LIKE condition works much faster. How (or can I?) use the same idea when joining 2 tables? Besides the substr is there another way to say where the first 15 characters of the fields are the same. Thanks for the help.
|
|
|
|
| Re: Joins / substr / LIKE [message #21291 is a reply to message #21290] |
Wed, 24 July 2002 12:55  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
If you are always comparing on the first 15 characters (and if you are on 8i or later), I would recommend a function-based index:
sql>create table t as select * from all_objects;
Table created.
sql>create index t_ndx on t(substr(object_name, 1, 15));
Index created.
sql>analyze table t compute statistics;
Table analyzed.
sql>set autotrace on
sql>select count(*)
2 from t
3 where substr(object_name, 1, 15) = 'DR$MRIT_PRODUCT';
COUNT(*)
---------
15
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=25)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T_NDX' (NON-UNIQUE) (Cost=1 Card=3 Bytes=75) |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
