Why this PL/SQL very slow?

[xuewei]

Hi,

I have a simple question about a pl/sql as following:

update mail_out pmo
set pmo.subdivision = (
select gd.subdivision
from gis_data gd
where pmo.apn_9 = gd.apn);


When I run it from SQL*Plus, it seems run forever and I can't get any results. Can someone give me some suggestions?


Thanks


Xuewei

 

[Turkbear]

Need more info...

How many records in gis_data gd,mail_out ?

Are pmo.apn_9 and gd.apn indexed?

Have you run an Explain Plan on the query...

Instead of 'I can't get any results' can you post what exactly happens when you run it?

Also, some info on your OS would be nice...

 

[xuewei]

Hi Turkbear,


Thank you for the response.

There are 68650 records in gis_data and 66000 records in mail_out. They are not indexed. The database is Oracle9i running on Windows2000. For that statement, it run more than 8 hours and I still can't get results.

I try to run Explain Plan on the query, After I "set autotrace traceonly explain", I run that statement, it seems keep running and running again.

Any ideas?


Thanks

Xuewei

 

[revnice]

Dude:

With 60,000 + records and no indexes your query forces full a full table scan to occur. It must first perform the inner select statement and then perform the update on that result set.

The system may need to dynamically allocate additional extents to the temporary sort segment assuming you have one set up - this will incur a further performance overhead.

If you have PCTFREE set to 0 you MIGHT also be causing migration of every single updated row - that would take some time to perform! - that's less likely though.

rev

 

[jimbopalmer]

I would be tempted to index gpn and subdivision in gis_data, if the optimizer catches this, it may get all the info from the index and not need the table. if an explain plan shows it still uses the table you only need to use gpn

Create index gpnsub on gis_data (gpn,subdivision) tablespace gis_indexes;

if you do not have a seperate index tablespace, skip the last two words.

Create index apn9 on mail_out (apn_9) tablespace gis_indexes;
I tried to remain child-like, all I acheived was childish.