oracle查询执行时间太长 [英] oracle query takes too long to execute

问题描述

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

这篇关于oracle查询执行时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！