假设以下存储过程在逻辑上没问题，且like 查询都是有必要的，请问个位高手是否还有优化方案！谢谢！

[dicklee1214]

PROCEDURE SearchDocument(
ps_Status IN VARCHAR2,
ps_Code IN VARCHAR2,
ps_Name IN VARCHAR2,
ps_Title IN VARCHAR2,
ps_Version IN VARCHAR2,
ps_Remark IN VARCHAR2,
ps_ZTEcode IN VARCHAR2,
ps_ZTEver IN VARCHAR2,
ps_Dept IN VARCHAR2,
ps_Prod IN VARCHAR2,
ps_IsNewVersion IN VARCHAR2,
ps_IsSearchAll IN VARCHAR2,
ps_IsUnchecked IN VARCHAR2,
ps_CreateFrom IN VARCHAR2,
ps_CreateTo IN VARCHAR2,
ps_UpdateFrom IN VARCHAR2,
ps_UpdateTo IN VARCHAR2,
ps_Type1 IN VARCHAR2,
ps_Type2 IN VARCHAR2,
ps_Type3 IN VARCHAR2,
ps_Org IN VARCHAR2,
ps_Team1 IN VARCHAR2,
ps_Team2 IN VARCHAR2,
ps_Format IN VARCHAR2,
ps_Language IN VARCHAR2,
ps_Stage IN VARCHAR2,
ps_CreateBy IN VARCHAR2,
ps_UpdateBy IN VARCHAR2,
ps_CreaterOrg IN VARCHAR2,
ps_UserID IN VARCHAR2,
ps_ReturnCur OUT Sys_Base.BaseCursor )

AS
strsql VARCHAR2(4000);
BEGIN
--需要优化程序
--先解析用户信息：将用户和用户所在的组解析出来成一个字符串
--将用户信息，状态，domain_id，EntrySet传入权限判断函数中比较权限。
strsql := '
SELECT (SERIAL_ID) SERIAL_ID,(DOC_NO) DOC_NO,(DOC_NAME) DOC_NAME,(DOC_TYPE) DOC_TYPE,(TITLE) TITLE
,(nvl(Doc_Baseoper_Pkg.get_TypeName(TYPE1),TYPE1)) AS TYPE1
,(nvl(Doc_Baseoper_Pkg.get_TypeName(TYPE2),TYPE2)) AS TYPE2
,(nvl(Doc_Baseoper_Pkg.get_TypeName(TYPE3),TYPE3)) AS TYPE3
,(nvl(Doc_Baseoper_Pkg.get_OrgName(BUSINESS_UNIT),BUSINESS_UNIT)) AS BUSINESS_UNIT
,(nvl(Doc_Baseoper_Pkg.get_OrgName(TEAM1),TEAM1)) AS TEAM1
,(nvl(Doc_Baseoper_Pkg.get_OrgName(TEAM2),TEAM2)) AS TEAM2
,(nvl(Doc_Baseoper_Pkg.get_DeptName(DEPT_NO),DEPT_NO)) as DEPT_NO
,(ZTE_NO) ZTE_NO,(ZTE_VERSION) ZTE_VERSION,(nvl(Doc_Baseoper_Pkg.get_LanguageName(LANGUAGE),LANGUAGE)) LANGUAGE,(PRODUCT) PRODUCT,
(STAGE) STAGE,(REMARK) REMARK,(VERSION) VERSION,(ITERATION) ITERATION,(decode(DOC_STATUS,2,''检出'',1,''检入'',0,''未受控'')) DOC_STATUS,
(LIFECYCLE_NO) LIFECYCLE_NO,(LIFECYCLE_STATUS) LIFECYCLE_STATUS,(STORE_TYPE) STORE_TYPE,(ENTRYSET) ENTRYSET,
(DOMAIN_ID) DOMAIN_ID,(IS_LATEST) IS_LATEST,(LAST_UPDATE_DATE) LAST_UPDATE_DATE,
(CREATION_DATE) CREATION_DATE,
(ENABLED_FLAG) ENABLED_FLAG,(CONTENT_TYPE) CONTENT_TYPE,
VERSION_SERIAL_ID ,(nvl(Doc_Baseoper_Pkg.get_OrgName(CREATER_BUSINESS_UNIT),CREATER_BUSINESS_UNIT)) CREATER_BUSINESS_UNIT
,(creater) CREATED_BY,(updater) LAST_UPDATED_BY,1 rightflag
FROM DOC_INFO WHERE 1=1
';
IF LENGTH(ps_Code)>0 THEN
strsql := strsql || ' and UPPER(DOC_NO) like '''|| '%' || UPPER(ps_Code) || '%' || '''';
END IF;
IF LENGTH(ps_Name)>0 THEN
strsql := strsql || ' and UPPER(DOC_NAME) like '''|| '%' || UPPER(ps_Name) || '%' || '''';
END IF;
IF LENGTH(ps_Title)>0 THEN
strsql := strsql || ' and UPPER(TITLE) like '''|| '%' || UPPER(ps_Title) || '%' || '''';
END IF;
IF LENGTH(ps_ZTEver)>0 THEN
strsql := strsql || ' and UPPER(ZTE_VERSION) like '''|| '%' || UPPER(ps_ZTEver) || '%' || '''';
END IF;
IF LENGTH(ps_Remark)>0 THEN
strsql := strsql || ' and UPPER(REMARK) like '''|| '%' || UPPER(ps_Remark) || '%' || '''';
END IF;
IF LENGTH(ps_ZTEcode)>0 THEN
strsql := strsql || ' and UPPER(ZTE_NO) like '''|| '%' || UPPER(ps_ZTEcode) || '%' || '''';
END IF;

IF LENGTH(ps_Dept)>0 THEN
strsql := strsql || ' and UPPER(DEPT_NO)=''' || UPPER(ps_Dept) ||'''';
END IF;
IF LENGTH(ps_Prod)>0 THEN
strsql := strsql || ' and UPPER(PRODUCT)=''' || UPPER(ps_Prod) ||'''';
END IF;
IF ps_IsNewVersion='1' THEN
strsql := strsql || ' and version_serial_id in (select max(version_serial_id) from doc_info group by doc_no)';
ELSE
IF LENGTH(ps_Version)>0 THEN
strsql := strsql || ' and UPPER(VERSION) like '''|| '%' || UPPER(ps_Version) || '%' || '''';
END IF;
END IF;
IF ps_IsSearchAll = 'true' THEN
strsql := strsql || '';
ELSE
strsql := strsql || '';
END IF;

IF length(ps_IsUnchecked) > 0 THEN --"未审批完成"是指除"拟制"、"作废"、"删除"外所有的生命周期状态
strsql := strsql || ' and UPPER(LIFECYCLE_STATUS) not in (''拟制'',''作废'',''删除'',''审批完成'')';
ELSE
--strsql := strsql || '';
IF LENGTH(ps_Status)>0 THEN
--strsql := strsql || ' and DOC_STATUS=''' || ps_Status||'''';
strsql := strsql || ' and UPPER(LIFECYCLE_STATUS)=''' || UPPER(ps_Status)||'''';
END IF;
END IF;
IF LENGTH(ps_CreateFrom)>0 THEN
strsql := strsql || ' and TRUNC(CREATION_DATE)>=TO_DATE('''||ps_CreateFrom||''',''YYYY-MM-DD'')';
END IF;
IF LENGTH(ps_CreateTo)>0 THEN
strsql := strsql || ' and TRUNC(CREATION_DATE)<=TO_DATE('''||ps_CreateTo||''',''YYYY-MM-DD'')';
END IF;
IF LENGTH(ps_UpdateFrom)>0 THEN
strsql := strsql || ' and TRUNC(LAST_UPDATE_DATE)>=TO_DATE('''||ps_UpdateFrom||''',''YYYY-MM-DD'')';
END IF;
IF LENGTH(ps_UpdateTo)>0 THEN
strsql := strsql || ' and TRUNC(LAST_UPDATE_DATE)<=TO_DATE('''||ps_UpdateTo||''',''YYYY-MM-DD'')';
END IF;
IF LENGTH(ps_Type1)>0 THEN
strsql := strsql || ' and UPPER(TYPE1)=''' || UPPER(ps_Type1)||'''';
END IF;
IF LENGTH(ps_Type2)>0 THEN
strsql := strsql || ' and UPPER(TYPE2)=''' || UPPER(ps_Type2)||'''';
END IF;
IF LENGTH(ps_Type3)>0 THEN
strsql := strsql || ' and UPPER(TYPE3)=''' || UPPER(ps_Type3)||'''';
END IF;
IF LENGTH(ps_Org)>0 THEN
strsql := strsql || ' and UPPER(BUSINESS_UNIT)=''' || UPPER(ps_Org)||'''';
END IF;
IF LENGTH(ps_Team1)>0 THEN
strsql := strsql || ' and UPPER(TEAM1)=''' || UPPER(ps_Team1)||'''';
END IF;
IF LENGTH(ps_Team2)>0 THEN
strsql := strsql || ' and UPPER(TEAM2)=''' || UPPER(ps_Team2)||'''';
END IF;
IF LENGTH(ps_Format)>0 THEN
strsql := strsql || ' and SERIAL_ID in';
strsql := strsql || ' (select DOC_SERIAL_ID from DOC_ATTACH_REF where ATTACH_SERIAL_ID in';
strsql := strsql || ' (select SERIAL_ID from DOC_ATTACH where UPPER(FORMAT) = ''' || UPPER(ps_Format)||''')';
--strsql := strsql || ' select PARAM_NAME from SYS_OTHERPARAMS where PARAM_TYPE_ID = 8 and SERIAL_ID = '''|| ps_Format ||'''))';
strsql := strsql || ' and enabled_flag = 1)';
END IF;
IF LENGTH(ps_Language)>0 THEN
strsql := strsql || ' and UPPER(LANGUAGE)=''' || UPPER(ps_Language)||'''';
END IF;
IF LENGTH(ps_Stage)>0 THEN
strsql := strsql || ' and UPPER(STAGE)=''' || UPPER(ps_Stage)||'''';
END IF;
IF LENGTH(ps_CreateBy)>0 THEN
strsql := strsql || ' and UPPER(CREATED_BY)=''' || UPPER(ps_CreateBy)||'''';
END IF;
IF LENGTH(ps_UpdateBy)>0 THEN
strsql := strsql || ' and UPPER(LAST_UPDATED_BY)=''' || UPPER(ps_UpdateBy)||'''';
END IF;
IF LENGTH(ps_CreaterOrg)>0 THEN
strsql := strsql || ' and UPPER(CREATER_BUSINESS_UNIT)=''' || UPPER(ps_CreaterOrg)||'''';
END IF;
/*
IF LENGTH(ps_UserID)>0 THEN
strsql := strsql || ' and Doc_Baseoper_Pkg.HasAccess(SERIAL_ID,'''||ps_UserID||''')= 1';
END IF;
*/
strsql := strsql || ' and is_latest = 1';

--DBMS_OUTPUT.put(strsql);

OPEN ps_ReturnCur FOR strsql;
--return;


END;

[LGQDUCKY]

UPPER(DEPT_NO)=。。。 ,TRUNC(LAST_UPDATE_DATE)=。。。


向这些给字段加上处理，会用不上索引，还有两边加上%也是用不上索引。

这样根据这个你自己考虑下。

[zealot_zk]

建议你把拼接形成的sql语句进行一下输出（每次执行存储过程都保存），这样你可以在向TOAD这样的工具中进行分析