I still need to speed this up even more if possible. The query now looks like this:
SELECT i.parent_id, r.relation_tag, rev.item_id, rev.revision_id, i.name, department, ended, institution, institution_city, institution_company, institution_position, institution_state, location, priv, relation, span_type, started, text_date
FROM phb_span t, cr_items i, cr_child_rels r, cr_revisions rev
WHERE rev.item_id in (<list of ids>)
and rev.revision_id = i.latest_revision
and t.span_id = rev.revision_id
and r.parent_id(+) = i.parent_id
and r.child_id(+) = i.item_id
And the execution plan is
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
23 NESTED LOOPS (OUTER)
24 NESTED LOOPS
65 NESTED LOOPS
65 INLIST ITERATOR
87 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_REVISIONS'
87 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'CR_REVISIONS_ITEM_ID_IDX' (NON-UNIQUE)
128 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PHB_SPAN'
128 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C0081933'
(UNIQUE)
87 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_ITEMS'
87 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'CR_ITEMS_BY_LATEST_REVISION' (UNIQUE)
23 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_CHILD_RELS'
46 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'CR_CHILD_RELS_UNQ_ID' (UNIQUE)
I don't have any of my Oracle books with me today and I've already covered the obvious; there are no more full table scans, at least.
Any suggestions on what else I can do here? TIA!
