| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Making use of an Index with the rtrim function in the where clause
A copy of this was sent to Prabhakar <srinivasharan_at_firsthealth.com>
(if that email address didn't require changing)
On Tue, 02 Mar 1999 18:29:06 GMT, you wrote:
>Hi!
> I am trying to use a column inside the Rtrim function in sql query's
>where clause,I have an index for that column,I read from sql tuning
>books that Oracle will not use the index if the column is embedded
>within a function call like upper,is null etc.
>How do I force Oracle to make use of that Index?...
>
until oracle8i (when you can index a function on a column, so you could create index myindex on emp(rtrim(ename))) this isn't really possible.
A trick you could consider with LIKE might be:
SQL> create table emp as select * from scott.emp
2 ;
SQL> create index emp_idx on emp(ename);
SQL> set autotrace on explain;
SQL> select ename, empno from emp where rtrim(ename,'G') = 'KIN';
ENAME EMPNO
---------- ----------
KING 7839
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' so, that one cannot use an index at all since rtrim(ename,'G') was not indexed but...
SQL> select ename, empno
2 from emp where ename like 'KIN%' AND rtrim(ename,'G') = 'KIN';
ENAME EMPNO
---------- ----------
KING 7839
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE) that gives the same answer, the LIKE can use the index (for a LIKE without a leading % it can) and the like brings back at least all of the records matching the rtrim() and the rtrim will remove any 'extras' the like might have brought back (like 'KINGS' in the above example)...
>Please do give me your valuable suggesstions on this.
>
>Thanks in advance.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |