Oracle SQL性能调优及加密函数如BASE64、MD5

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea
order BY disk_reads DESC )where ROWNUM<50 ;

select '1',
utl_raw.cast_to_varchar2(utl_encode.base64_encode(rawtohex('1'))
from dual

select 'vNPD3LrztcTOxNfW',utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.CAST_TO_RAW('vNPD3LrztcTOxNfW')))
from dual

select '中文字符串',fn_md5 ('中文字符串')
from t_abs_metadata_payer

CREATE OR REPLACE FUNCTION fn_md5 (input_string IN VARCHAR2)
RETURN VARCHAR2
IS
raw_input RAW (128)
:= UTL_RAW.cast_to_raw (input_string);
decrypted_raw RAW (2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
DBMS_OBFUSCATION_TOOLKIT.md5 (input => raw_input,
checksum => decrypted_raw
);
RETURN LOWER (RAWTOHEX (decrypted_raw));
END;

select '中文字符串',substr(fn_md5 ('中文字符串'),1,99)
from t_abs_metadata_payer



查看多余索引
select /*+ ordered */
||'.'|| redundant_index,
||'.'|| sufficient_index
from
(
select
obj#,
bo#,
count(*) cols,
max(decode(pos#, 1, intcol#)) leadcol#
from
sys.icol$
group by
obj#,
bo#
) ic1,
sys.icol$ ic2,
sys.ind$ i1,
sys.obj$ n1,
sys.obj$ n2,
er$ o1,
er$ o2
where
ic2.obj# != ic1.obj# and
ic2.bo# = ic1.bo# and
ic2.pos# = 1 and
ic2.intcol# = ic1.leadcol# and
i1.obj# = ic1.obj# and
bitand(i1.property, 1) = 0 and
ic1.cols * (ic1.cols + 1) / 2 =
( select
sum(xc1.pos#)
from
sys.icol$ xc1,
sys.icol$ xc2
where
xc1.obj# = ic1.obj# and
xc2.obj# = ic2.obj# and
xc1.pos# = xc2.pos# and
xc1.intcol# = xc2.intcol#
) and
n1.obj# = ic1.obj# and
n2.obj# = ic2.obj# and
er# = n1.owner# and
er# = n2.owner#