Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why the 'LIKE' operator takes so much time to run?

431844Apr 20 2006 — edited Apr 21 2006
I have a table T with 3 columns and 3 indexes:

CREATE TABLE T
(
id VARCHAR2(38) NOT NULL,
fid VARCHAR2(38) NOT NULL,
val NVARCHAR2(2000) NOT NULL
);
ALTER TABLE T ADD (CONSTRAINT pk_t PRIMARY KEY (id,fid));
CREATE INDEX t_fid ON T(fid);
CREATE INDEX t_val ON T(val);

Then I have the following two queries which differ in only one place - the 1st one uses the '=' operator whereas the 2nd uses 'LIKE'. Both queries have the identical execution plan and return one identical row. However, the 1st query takes almost 0 second to execute, and the 2nd one takes more than 12 seconds, on a pretty beefy machine. I had played with the target text, like placing '%' here and/or there, and observed the similar timing every time.

So I am wondering what I should change to make the 'LIKE' operator run as fast as the '=' operator. I know CONTEXT/CATALOG index is a viable approach, but I am just trying to find out if there is a simpler alternative, such as a better use of the index t_val.

1) Query with '=' operator

SELECT id
FROM T
WHERE fid = '{999AE6E4-1ED9-459B-9BB0-45C913668C8C}'
AND val = '3504038055275883124';

2) Query with 'LIKE' operator

SELECT id
FROM T
WHERE fid = '{999AE6E4-1ED9-459B-9BB0-45C913668C8C}'
AND val LIKE '3504038055275883124';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=99)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=99)
2 1 INDEX (RANGE SCAN) OF 'T_VAL' (NON-UNIQUE) (Cost=4 Card=12)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2006
Added on Apr 20 2006
7 comments
1,327 views