Oracle PL/SQL decryption function breaking index? 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 =&gt; converted_raw ,
-- typ ... .... ... ..... ... . ...
--., key value bype . . ... .... ...
                 typ =&gt; DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key =&gt; key_data_raw ,
                 iv =&gt;  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 =&gt; input_string ,
                 typ =&gt; DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key =&gt; key_data_raw ,
                 iv =&gt;  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
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.