Questions

Optimizing a SQL Query

+
0 Votes
Locked

Optimizing a SQL Query

miriam.bryan
One of our developers wrote this query, it runs, but very slow. He asked if I could help him troubleshoot it. I'm working on it but I'm really pressed for free time. Could someone help me with it? Here's the query.

-- @TODO: FORCE USE OF HINTS FOR INDEXES ? Dunno.

SELECT getOwnerDetailsAsXml(

CURSOR( SELECT /*+ FIRST_ROWS(1)*/ o.*,



CURSOR( SELECT op.*

FROM icc_dev.owner_phones op

WHERE op.owner_id = :oid

) owner_phones,



CURSOR( SELECT l.*,

CURSOR( SELECT lp.*

FROM icc_dev.location_phones lp

WHERE lp.location_id = l.location_id ) location_phones

FROM icc_dev.location l

WHERE l.owner_id = :oid

) location,



CURSOR( SELECT u.*,

CURSOR( SELECT up.*

FROM icc_dev.user_phones up

WHERE up.user_id = u.user_id ) user_phones

FROM icc_dev.users u

WHERE u.owner_id = :oid) users,



CURSOR( SELECT /*+ INDEX(icc_dev.inventory inventory_pk)*/ i.item_id,

-- @TODO: find a way to look at all item_x_data tables.

CURSOR( SELECT /*+ FIRST_ROWS(1)*/ ird.*

FROM icc_dev.item_rv_data ird

WHERE ird.item_id = i.item_id ) item_rv_data,

CURSOR( SELECT /*+ FIRST_ROWS(1)*/ ip.*

FROM icc_dev.inventory_price ip

WHERE ip.item_id = i.item_id ) inventory_price,

CURSOR( SELECT /*+ FIRST_ROWS(1)*/ ia.*

FROM icc_dev.item_addresses ia

WHERE ia.item_id = i.item_id ) item_addresses,

CURSOR( SELECT ips.*

FROM icc_dev.item_photos ips

WHERE ips.item_id = i.item_id ) item_photos

FROM icc_dev.inventory i

WHERE i.owner_id = :oid

AND i.online_status = 1

AND i.purchase_status != 'S'

) inventory

FROM icc_dev.owner o

WHERE o.owner_id = :oid)

) as XML

FROM dual;
+
0 Votes
thisisfutile

My experience has been that to get forum help with SQL code, specially when it's long code as this is, it's best to include CREATE and INSERT commands to give a would-be-helper the table structure and test data. I don't have any more time than to offer this helpful hint, but if someone did have the time, it would be very difficult for them to interpret your needs without tables and data to work with.

Hope this helps,

Gabe