Re: update statement with unacceptable performance

From: joel garry <joel-garry_at_home.com>
Date: Tue, 11 Jun 2013 10:16:23 -0700 (PDT)
Message-ID: <af01ec7a-57f2-4559-b536-1016e6b691be_at_jr6g2000pbb.googlegroups.com>

On Jun 11, 7:50 am, Juerg <s..._at_databaar.ch> wrote:
> hello
>
> I have to update (snap) the coordinates of a first table (geometry) to the cordinates of a second table (point) if the difference is > 0 and < 0.0007 meter. I wrote the following statements:
>
> SQL> select count(*) from geometry
>
> COUNT(*)
> --------
> 218037
> 1 Zeilen ausgewählt.
>
> SQL> select count(*) from point
>
> COUNT(*)
> --------
> 30664
> 1 Zeilen ausgewählt.
>
> update geometry g
> set
>    y1 = (
>       select y1 from point p
>       where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0
>         and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
>    ),
>    x1 = (
>       select x1 from point p
>       where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0
>         and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
>    )
> where exists (
>    select 1 from point p
>    where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0
>      and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
> );
>
> Well, the update took 9 hours (Oracle Enterprise Edition 10.2.0.3). In this data model oracle spatial option is not avaiable, I have to do it without this extension. There are indexes on y1 and x1 on both tables, but I suppose they are not used.
>
> Any idea to solve this slowness? Thank in advance
>
> kind regards
>
> Juerg

No real idea, except to wonder if you can make a function based index on sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)).

This OTN related blog post shows how to ask these kinds of questions, in a way where figuring what to ask might answer it: http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

jg

--
_at_home.com is bogus.
http://allthingsd.com/20130610/oracles-mark-hurd-still-has-no-interest-in-being-ceo-of-dell/

Received on Tue Jun 11 2013 - 19:16:23 CEST