3
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
0
\$\begingroup\$

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
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy