oracle查询执行时间太长

本文介绍了oracle查询执行时间太长的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

oracle查询要花费太多时间来执行，尽管它并没有终止，除非终止它，它在更新部分遇到了问题，并且没有进行更新或花费了很长时间，请参见此处的语法和如果有人知道什么，请帮助 创建或替换函数sp_addClient(CITIZENNAME VARCHAR2， NATIONALNUMBER VARCHAR2默认为null， ADDRESS VARCHAR2默认为空， PHONE VARCHAR2默认为null， MOBILE VARCHAR2默认为null， 传真VARCHAR2默认为null， EMAIL VARCHAR2默认为空， FATHERNAME VARCHAR2默认为null， MOTHERNAME VARCHAR2默认为null， PSURNAME VARCHAR2默认为空， REGISTERNUMBER VARCHAR2默认为空， REGISTERPLACE VARCHAR2默认为null， BIRTHDATE VARCHAR2默认为null， BIRTHPLACE VARCHAR2默认为null， COMPANYNAME varchar2默认为null， P_NATIONALITY INTEGER默认值1， P_PASSEPORT_NO VARCHAR2默认为空， P_STAY_CARD VARCHAR默认为null) 返回整数是 结果整数:= null; cr types.ref_cursor; 开始 为SELECT a.citizen_id 打开cr 来自公民a 在哪里(a.national_number = nationalNumber和 nationalNumber不为空) 或(((a.citizen_name = citizenName)和((a.father_name = fatherName))和 (a.surname = Psurname)和 (a.mother_name = motherName或motherName为null)); 将cr提取到结果中； 关闭cr; 如果result = -1或result为null，则 从Double中选择cits_seq.NEXTVAL转换为结果； 插入公民 值 (结果， CITIZENNAME， NATIONALNUMBER， 地址， 电话， 手机， 传真， 电子邮件， 父亲的名字， MOTHERNAME， PSURNAME， REGISTERNUMBER， 注册， to_date(BIRTHDATE，``dd-mm-yyyy'')， 出生地， COMPANYNAME， null， null， null， 0， null， P_NATIONALITY， P_PASSEPORT_NO， P_STAY_CARD); 其他 更新市民集 national_number = nationalNumber， birth_date = to_date(BIRTHDATE，``dd-mm-yyyy'')， birth_place =出生地， register_number = REGISTERNUMBER， register_place = REGISTERPLACE， 国籍= P_NATIONALITY， passeport_no = P_PASSEPORT_NO， stay_card = P_STAY_CARD， --address =地址， 电话= PHONE， 移动版=移动版， 传真=传真， 电子邮件= EMAIL， company_name = COMPANYNAME 其中citizen_id =结果； 结束，如果； return(Result); end sp_addClient;

oracle query is taking too much time to execute , though it doesn''t finish except by terminating it , it faces a problem in the update part , and makes no updates or takes too long time , see the syntax right here and if anyone knows anything please help create or replace function sp_addClient(CITIZENNAME VARCHAR2, NATIONALNUMBER VARCHAR2 default null, ADDRESS VARCHAR2 default null, PHONE VARCHAR2 default null, MOBILE VARCHAR2 default null, FAX VARCHAR2 default null, EMAIL VARCHAR2 default null, FATHERNAME VARCHAR2 default null, MOTHERNAME VARCHAR2 default null, PSURNAME VARCHAR2 default null, REGISTERNUMBER VARCHAR2 default null, REGISTERPLACE VARCHAR2 default null, BIRTHDATE VARCHAR2 default null, BIRTHPLACE VARCHAR2 default null, COMPANYNAME varchar2 default null, P_NATIONALITY INTEGER default 1, P_PASSEPORT_NO VARCHAR2 default null, P_STAY_CARD VARCHAR default null) return integer is Result integer := null; cr types.ref_cursor; begin open cr for SELECT a.citizen_id FROM citizens a WHERE (a.national_number = nationalNumber and nationalNumber is not null) or ((a.citizen_name = citizenName) and (a.father_name = fatherName) and (a.surname = Psurname) and (a.mother_name = motherName or motherName is null)); fetch cr into result; close cr; if result = -1 or result is null then SELECT citizens_seq.NEXTVAL INTO Result FROM DUAL; INSERT INTO citizens VALUES (Result, CITIZENNAME, NATIONALNUMBER, ADDRESS, PHONE, MOBILE, FAX, EMAIL, FATHERNAME, MOTHERNAME, PSURNAME, REGISTERNUMBER, REGISTERPLACE, to_date(BIRTHDATE, ''dd-mm-yyyy''), BIRTHPLACE, COMPANYNAME, null, null, null, 0, null, P_NATIONALITY, P_PASSEPORT_NO, P_STAY_CARD); else update citizens set national_number = nationalNumber, birth_date =to_date(BIRTHDATE, ''dd-mm-yyyy''), birth_place = BIRTHPLACE, register_number = REGISTERNUMBER, register_place = REGISTERPLACE, nationality = P_NATIONALITY, passeport_no = P_PASSEPORT_NO, stay_card = P_STAY_CARD, --address = ADDRESS, phone = PHONE, mobile = MOBILE, fax = FAX, email = EMAIL, company_name = COMPANYNAME where citizen_id = result; END if; return(Result); end sp_addClient;

推荐答案

在表上创建索引: 在CITIZEN(CITIZEN_ID)上创建索引IDX_CITIZEN; Create an index on table: CREATE INDEX IDX_CITIZEN On CITIZEN(CITIZEN_ID);

好吧，这似乎对我来说是一个愚蠢的错误，某些参数与已修改表字段的名称相同，请检查此内容 电话= PHONE， 移动版=移动版， 传真=传真， 电子邮件= EMAIL 这绝对不会起作用... 谢谢您所有4个帮助 well it seems that it was a silly mistake of me , some parameters have the same name of the modified table fields , check this phone = PHONE, mobile = MOBILE, fax = FAX, email = EMAIL this absolutely wont work ... thank u all 4 ur help