Hard time optimizing query
From: David Deutsch <pizza2pizza_at_hotmail.com>
Date: 14 Aug 2002 15:03:05 -0700
Message-ID: <812a50f5.0208141403.fbe1a4d_at_posting.google.com>
Date: 14 Aug 2002 15:03:05 -0700
Message-ID: <812a50f5.0208141403.fbe1a4d_at_posting.google.com>
Hello,
This SQL takes about a minute, though I can run each individual clause (i.e. with "SMITH" or "JONES") in under a second each. The plan for this SQL is:
SELECT STATEMENT Cost = 4183
2.1 SORT UNIQUE
3.1 FILTER
4.1 TABLE ACCESS FULL MP_PEOPLE 4.2 TABLE ACCESS BY INDEX ROWID MP_PEOPLE_NAME_SEARCH 5.1 INDEX RANGE SCAN MP_PEOPLE_NAME_SEARCH_IDX NON-UNIQUE 4.3 TABLE ACCESS BY INDEX ROWID MP_PEOPLE_NAME_SEARCH 5.1 INDEX RANGE SCAN MP_PEOPLE_NAME_SEARCH_IDX NON-UNIQUE
....So, what is Oracle doing here, and why is it doing it? More importantly, does anyone know how (perhaps via SQL hints?) I can force Oracle to execute all these statements the "right" way?
Thanks in advance, DaveReceived on Thu Aug 15 2002 - 00:03:05 CEST