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