Question
-
Topic
-
Optimizing a SQL Query
LockedOne 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;
