CODEWITHSUNDEEP
sqloracle-database
Hommee
Hommee

Reputation: 33

Using Functions for Easy writing makes SQL really slow

create or replace FUNCTION FUNCTION_X
(
  N_STRING IN VARCHAR2 
) RETURN VARCHAR2 AS 
BEGIN
  RETURN UPPER(translate(N_STRING, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'));
END FUNCTION_X;

SELECT takes around 5 seconds (80k + Lines)

SELECT TABLE_A.STRING_X
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.ID = TABLE_A.IDTB
WHERE
   UPPER(UPPER(translate(TABLEB.STRING_X, 
   'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'))
   =
   UPPER(translate(TABLEB.N_STRING, 
   'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')

Using function takes over 3 minutes (80k + lines)

SELECT TABLE_A.STRING_X
    FROM TABLE_A
    INNER JOIN TABLE_B ON TABLE_B.ID = TABLE_A.IDTB
    WHERE
       FUNCTION_X(TABLE_A.STRING_X) = FUNCTION_X(TABLE_B.N_STRING)

I dont know whats makes it so heavy.

Upvotes: 1

Views: 515

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17944

If your first query, with the UPPER(UPPER(translate(...))) inline in the query takes only 5 seconds and the tables are big, I would look to see if you have a function based index having those functions on either or both tables.

An index, as you probably know, stores a sorted version of the data so that rows can be found quickly. But they're only useful if you are searching on the data that is sorted in the index. (Think of an index in a book, in which keywords sorted alphabetically -- useful for searching for a particular word, not so useful for finding references to words ending in the letter "r").

If there is a function based index on UPPER(UPPER(translate(...))) that is helping your original query, you are losing the benefit when your query specifies FUNCTION_X(...) instead. Oracle is not smart enough to realize they are the same function. You would need to create function based indexes on the expression you actually use in the query -- i.e, on FUNCTION_X(...).

Also, you can help performance by telling Oracle that your function is deterministic (i.e., always returns the same value for the same input) and intended to be used in SQL queries. So, in addition to the function based indexes, a better definition of your function would be:

create or replace FUNCTION FUNCTION_X
(
  N_STRING IN VARCHAR2 
) RETURN VARCHAR2 
DETERMINISTIC -- add this
AS 
PRAGMA UDF;   -- add this too
BEGIN
  RETURN UPPER(translate(N_STRING, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'));
END FUNCTION_X;

Upvotes: 2

Mike Robinson
Mike Robinson

Reputation: 8955

JOINS, of course, intend to exploit index values. The problem with your second query is that you are demanding that the SQL engine must execute this function-call for each and every line. It therefore cannot do anything better than a "full table scan," evaluating the function over each and every row ... actually over a Cartesian Product of the two tables taken together!!

You must find an alternative way to do that.

Upvotes: 0

Related Questions