Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a package called pkg_crypto.

My select looks like this:

SELECT "SEQ", pkg_crypto.decrypt("NAME", 'secret') name, pkg_crypto.decrypt("TEL", 'secret') tel
FROM MYTABLE
ORDER BY "SEQ" DESC

This takes about 3 seconds for a few thousand records. (Average of 10 runs is 3.032s.)

Now, if I change it to either of the following, the query completes in about 0.2 seconds (average of 10 runs is 0.203s):

SELECT "SEQ", "NAME", "TEL"
FROM MYTABLE
ORDER BY "SEQ" DESC

SELECT "SEQ", pkg_crypto.decrypt("NAME", 'secret') name, pkg_crypto.decrypt("TEL", 'secret') tel
FROM MYTABLE

What seems to be happening is that if I use the decrypt and order by on the same command, it will re-sort the records and ignore the index that exists on "SEQ".

I suspect this may be a problem with the implementation of the pkg_crypto package that I am using. Its code is as follows:

CREATE OR REPLACE PACKAGE PKG_CRYPTO AS 

    FUNCTION encrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := 'DEFDB'
    ) RETURN RAW;

    FUNCTION decrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := 'DEFDB'
    ) RETURN VARCHAR2;

END PKG_CRYPTO;PACKAGE BODY pkg_crypto
IS
-- .. .... error code . message . .. .. .. ...
    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;

-- ... .. .. key_data . .... .. .. default . 12345678 . ....
    FUNCTION encrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := 'DEFDB')
     RETURN RAW
    IS

        key_data_raw        RAW(2000);
        converted_raw       RAW(2000);
        encrypted_raw       RAW(2000);

    BEGIN

-- ... data . .... .. RAW . .....
        converted_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');

-- DBMS_CRYPTO.ENCRYPT . ... .. encrypted_raw . ...
        encrypted_raw :=
             DBMS_CRYPTO.ENCRYPT(
                 src => converted_raw ,
-- typ ... .... ... ..... ... . ...
--., key value bype . . ... .... ...
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);

        RETURN encrypted_raw;
    END encrypt;

    FUNCTION decrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := 'DEFDB')
     RETURN VARCHAR2
    IS
        converted_string    VARCHAR2(2000);
        key_data_raw        RAW(2000);
        decrypted_raw    VARCHAR2(2000);

    BEGIN

        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');

        decrypted_raw :=
            DBMS_CRYPTO.DECRYPT(
                 src => input_string ,
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);

-- DBMS_CRYPTO.DECRYPT .. .. .. .... raw data . varchar2 . .... .!
        converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

        RETURN converted_string;
    END decrypt ;
END pkg_crypto;

I've also attempted changing the query around a bit, but it hasn't been fruitful: the following take the same 3 seconds:

SELECT "SEQ", pkg_crypto.decrypt("NAME", 'secret') name, pkg_crypto.decrypt("TEL", 'secret') tel
FROM
(
    SELECT "SEQ", "NAME", "TEL" FROM MYTABLE ORDER BY "SEQ" DESC
)

and

SELECT "SEQ", pkg_crypto.decrypt("NAME", 'secret') name, pkg_crypto.decrypt("TEL", 'secret') tel
FROM
(
    SELECT "SEQ", "NAME", "TEL" FROM MYTABLE 
) 
ORDER BY "SEQ" DESC
share|improve this question
up vote 1 down vote accepted

I've found that the reason that decryption xor sorting is fast, but decryption and sorting is slow:

  • Decryption will only grab the first 100 or so records, decrypt them, and display them. Fast.
  • Sorting will grab the first 100 or so records (utilizing the pk index) and display them. Fast.
  • Decryption and sorting will force a table scan, resulting in decrypting several thousand records, and then sorting them all.

In other words, the "fast" queries are only fast because they're doing very little work.

share|improve this answer

have you tried this one?

SELECT MYTABLE_ALIAS."SEQ", MYTABLE_ALIAS."NAME", MYTABLE_ALIAS."TEL"
FROM (
  SELECT "SEQ", pkg_crypto.decrypt("NAME", 'secret') NAME, pkg_crypto.decrypt("TEL", 'secret') TEL
  FROM MYTABLE) MYTABLE_ALIAS
ORDER BY "SEQ" DESC
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.