CODEWITHSUNDEEP
sqloracle10g
user1466466
user1466466

Reputation: 1348

how to minimize the usage of conversion functions in SQL queries?

My SQL Queries contain lots of ltrim() , rtrim() , substring() etc conversion functions...They are significantly impacting query performance... Please answer my following queries :

...please guide me

Upvotes: 0

Views: 106

Answers (3)

Jon Heller
Jon Heller

Reputation: 36922

You can save a little amount of time by replacing ltrim(rtrim( with trim(

Example:

create table test1(a varchar2(4000));

--Create 1 million rows
--This is hopefully a "large" amount of rows, but small enough to fit
--in memory.  This way we can test the CPU time to process the rows
--instead of the IO time to read the data.
begin
    for i in 1 .. 10 loop
        insert into test1 select '          '||level||'          '
        from dual connect by level <= 100000;
    end loop;
    commit;
end;
/

--Note: You'll want to run the statements several times first to
--"warm up" the database, and get everything in memory.

--0.33 seconds
select count(*)
from test1
where ltrim(rtrim(a)) = '5';

--0.20 seconds
select count(*)
from test1
where trim(a) = '5';

But this is a very small amount of time to save on processing 1 million rows.

How long does it take your system to process the 2 million rows? Is there something unusual about your data? For example, are you using large CLOBs instead of small VARCHAR2s? Are you sure that the problem isn't caused by the predicates creating a different explain plan? For example, Oracle might think that the functions return significantly fewer rows than they really do, and is therefore using a nested loop instead of a hash join.

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19235

How do you know these functions are consuning time in execution? have you run it without these functions and noticed that it is quicker?

Are all the columns in the where clause indexed?

If you look at the query plan can you see table scans?

How many rows are in this table?

You could create a new column in your table that contains cleaned up data out of the emp_info column. i.e. 

UPDATE YourTable SET NewColumn = ltrim(rtrim(substr(emp_info, 1, 9)))

Then add a trigger to your table to ensure it is always up to date

Then add an index to the column.

Then alter your select to use the new column

You might see some performance improvement. It's impossible to say based on the information you've given.

Upvotes: 1

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5300

You can handle these conversion functions like ltrim,Rtrim,Substring can be handled in the front-end while getting inputs. These types of string function is availiable in the front end too. For eg: Trim(),Substring()

Upvotes: 0

Related Questions