Re: Help me to tune my sql

"joe bayer" <joebayerii(no-spam)@hotmail.com> wrote in message news:<TYSMa.13744$JY1.37_at_nwrddc01.gnilink.net>...
> We have a sql seems to be bery slow (9.2.3)
>
> UPDATE sensor_station_dir_info ssdi
> SET hpms_num_of_lanes_traffic_vol =
> (SELECT SUM (aggregate_lanes)
> FROM device d, device_dir_info ddi
> WHERE ddi.sensor_station_id = :b2
> AND ddi.direction_abbr = :b1
> AND ddi.sensor_station_id = ssdi.sensor_station_id
> AND ddi.direction_abbr = ssdi.direction_abbr
> AND d.sensor_id = ddi.sensor_id
> AND d.device_number = ddi.device_number
> GROUP BY ddi.sensor_station_id, ddi.direction_abbr)
>
> explain plan
>
> ROWES ROW_SOURCE_PLAN
> ------- --------------------------------------------------------------------
> ------------------------
> 0 UPDATE (query=10023703 disk=0 pw=0 time=988.25)
> 93913 .TABLE ACCESS FULL SENSOR_STATION_DIR_INFO (query=2547 disk=0
> pw=0 time=0.78)
> 20 .SORT GROUP BY (query=10048644 disk=0 pw=0 time=973.81)
> 20 .NESTED LOOPS (query=10048644 disk=0 pw=0 time=972.27)
> 20 .TABLE ACCESS FULL DEVICE_DIR_INFO (query=10048584 disk=0
> pw=0 time=971.98)
> 20 .TABLE ACCESS BY INDEX ROWID DEVICE (query=60 disk=0 pw=0
> time=0.00)
> 20 .INDEX UNIQUE SCAN PK_DEVICE (query=40 disk=0 pw=0
> time=0.00)
>
> Any suggestions?
>
> Thanks

Do you have an index on device_dir_info.direction_abbr? What probably happens is that subquery is run for every row of sensor_station_dir_info. You might consider:
1. Adding an index on direction_abbr in all tables. 2. Adding where clause in update statement itself:

where ssdi.direction_abbr = :b1.

Last solution looks more logical, you restrict number of rows updated. BTW, statement as written should completely screw up updated table, setting value to null in all rows where direction_abbr != :b1 Received on Thu Jul 03 2003 - 11:40:57 CDT