Re: any sql tuning experts?

I'd start with analyzing tables and indexes. You can also check value of always_anti_join initialization parameter. Changing it to HASH might improve performance. Actually hash antijoin is the most efficient, and you have one. You can also convert second subquery into antijoin subquery (from not in into not exists). To give better advice, I would need table sizes, index selectivity and some more information.

In article <915n7v$n9c$1_at_nnrp1.deja.com>,   seanldus_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 12 2000 - 23:03:59 CST