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>


Hello,

        I am having some strange performance problems with my SQL. In a nutshell, a statement that takes less than one second when I use the DISTINCT clause takes about 40 seconds without it. The statement (which returns 3 rows, whether or not DISTINCT is used) is:

Select DISTINCT MP_PEOPLE.people_id
from MP_PEOPLE
where EXISTS

	(Select WORD 
	from mp_people_name_search 
	where MP_PEOPLE.people_id = mp_people_name_search.ID and WORD =

'SMITH'
);

Using EXPLAIN PLAN, the plan when DISTINCT is used is: SELECT STATEMENT Cost = 2139
 2.1 SORT UNIQUE
   3.1 NESTED LOOPS

     4.1 TABLE ACCESS BY INDEX ROWID MP_PEOPLE_NAME_SEARCH
       5.1 INDEX RANGE SCAN MP_PEOPLE_NAME_SEARCHWORD_IDX NON-UNIQUE
     4.2 INDEX RANGE SCAN MP_PEOPLE_PK_PEOPLE_ID NON-UNIQUE


The plan without DISTINCT is:
SELECT STATEMENT Cost = 2587
  2.1 FILTER
    3.1 TABLE ACCESS FULL MP_PEOPLE
    3.2 TABLE ACCESS BY INDEX ROWID MP_PEOPLE_NAME_SEARCH       4.1 INDEX RANGE SCAN MP_PEOPLE_NAME_SEARCH_IDX NON-UNIQUE Another (perhaps related) problem comes from using "OR" in my where clause. For example, the SQL:
Select DISTINCT MP_PEOPLE.people_id
from MP_PEOPLE
where EXISTS

	(Select WORD 
	from mp_people_name_search 
	where MP_PEOPLE.people_id = mp_people_name_search.ID and WORD =

'SMITH'
) OR EXISTS (Select WORD from mp_people_name_search where MP_PEOPLE.people_id = mp_people_name_search.ID and WORD =
'JONES'
);

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,
		   Dave
Received on Thu Aug 15 2002 - 00:03:05 CEST

Original text of this message