o**y
发帖数: 1466 | 1 I have written a procedure in a package but the performance is very slow
which takes forever to execute. After more than 1 hour now it is still
spinning. however if I run the exactly the same code within developer
without using package, the performance is totally different, and it just
takes only about 3 minutes. The pseudo code is:
insert into tab3 (.......)
with subtab1 as
(select about 27000 rows from tab1, tab2 where join_sth),
subtab2 as
(select *, function_in_the_package_deal_with_Geomtry(col1,col2)) from
subtab1),
subtab3 as
(select * from subtab2 where resultofFunctionCol = sth)
select * from subtab3;
The tab1 and tab2 both have about 1 million records and each table has a
spatial geometry column and the user-defined function is to deal with the
Geometry columns.
Where is the issue? I am just a normal db user without any high privileges
in dba. I just cannot understand why the performance is so different. Again,
it is the exactly identical script. The only difference is using package or
not.
Any input will be highly appreciated! Thank you very much!! |
d****n
发帖数: 12461 | 2 你这个subtab2没用啊？去掉应该可以加快很多。
sql developer只返回前50行记录。 |
o**y
发帖数: 1466 | 3 Thank you! subtab2 is used. I just changed the pseudo code. Thank you!! |
o**y
发帖数: 1466 | 4 I found the reason after the proc call was completed.
It is a very stupid mistake. One of the parameters in the procedure is the
same name as one of the column names which caused the rows were not filtered!
So here is my stupid:
procedure xyz(p1, p2) as
begin
select sth from tab t where t.p1 = p1;
end;
all of the rows are returned!! Shame on me!
Thank you dynkin!
and Thank you every one! |
c*****d
发帖数: 6045 | 5 这个错误太不应该了
另外那3个临时表都可以去掉
用一个sql语句应该可以搞定
filtered!
【在 o**y 的大作中提到】
: I found the reason after the proc call was completed.
: It is a very stupid mistake. One of the parameters in the procedure is the
: same name as one of the column names which caused the rows were not filtered!
: So here is my stupid:
: procedure xyz(p1, p2) as
: begin
: select sth from tab t where t.p1 = p1;
: end;
: all of the rows are returned!! Shame on me!
: Thank you dynkin!
|
