any sql tuning experts?

From: <seanldus_at_my-deja.com>
Date: Tue, 12 Dec 2000 17:28:43 GMT
Message-ID: <915n7v$n9c$1@nnrp1.deja.com>

I have a query here. I wonder if anyone has any hints on speeding it up. I don't quite understand explain_plan. I have sent that as well.

select f1.food_id, f1.qualified_name
from food f1, food_category fc1
where f1.account_id = 272
and ((fc1.name like '%candy%') or (f1.qualified_name like '% candy%') )
and f1.food_category_id = fc1.food_category_id and not exists ( select 'x'

                 from hidden_food h1
				 where h1.food_id = f1.food_id
				 and h1.account_id = f1.account_id )

UNION
select f2.food_id, f2.qualified_name
from food f2, food_category fc2
where f2.food_category_id = fc2.food_category_id and ( (fc2.name like '%candy%') or (f2.qualified_name like '%candy%') ) and f2.account_id = 1
and f2.food_id not in ( select f3.food_id from food f3, food_category fc3

                           where  f3.account_id = 272
                           and    ((fc3.name like '%candy%')or
(f3.qualified_name like '%candy%') )
                           and    f3.food_category_id =

fc3.food_category_id)

TIMESTAMP	OPERATION	OPTIONS	OBJECT_OWNER	OBJECT_NAME
	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS
	ID	PARENT_ID	POSITION
12/11/2000 15:49	SELECT STATEMENT
		CHOOSE		0
12/11/2000 15:49	SORT	UNIQUE
		1	0	1
12/11/2000 15:49	UNION-ALL
			2	1	1
12/11/2000 15:49	FILTER
		3	2	1
12/11/2000 15:49	NESTED LOOPS
			4	3	1
12/11/2000 15:49	TABLE ACCESS	BY INDEX ROWID	EJBTEST	FOOD
	1				5	4	1
12/11/2000 15:49	INDEX	RANGE SCAN	EJBTEST	FOO_ACC_FK_I
	NON-UNIQUE			6	5	1
12/11/2000 15:49	TABLE ACCESS	BY INDEX ROWID	EJBTEST
	FOOD_CATEGORY	2				7	4
	2
12/11/2000 15:49	INDEX	UNIQUE SCAN	EJBTEST	FC_PK
	UNIQUE			8	7	1
12/11/2000 15:49	INDEX	RANGE SCAN	EJBTEST	HFD_PK
	UNIQUE			9	3	2
12/11/2000 15:49	FILTER
		10	2	2
12/11/2000 15:49	NESTED LOOPS
			11	10	1
12/11/2000 15:49	TABLE ACCESS	BY INDEX ROWID	EJBTEST	FOOD
	4				12	11	1
12/11/2000 15:49	INDEX	RANGE SCAN	EJBTEST	FOO_ACC_FK_I
	NON-UNIQUE			13	12	1
12/11/2000 15:49	TABLE ACCESS	BY INDEX ROWID	EJBTEST
	FOOD_CATEGORY	5				14	11
	2
12/11/2000 15:49	INDEX	UNIQUE SCAN	EJBTEST	FC_PK
	UNIQUE			15	14	1
12/11/2000 15:49	NESTED LOOPS
			16	10	2
12/11/2000 15:49	TABLE ACCESS	BY INDEX ROWID	EJBTEST	FOOD
	6				17	16	1
12/11/2000 15:49	INDEX	RANGE SCAN	EJBTEST	FOO_ACC_FK_I
	NON-UNIQUE			18	17	1
12/11/2000 15:49	TABLE ACCESS	BY INDEX ROWID	EJBTEST
	FOOD_CATEGORY	7				19	16
	2

ANY IDEAS? Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 12 2000 - 11:28:43 CST