From: Alex Filonov <afilonov@pro-ns.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: any sql tuning experts?
Date: Wed, 13 Dec 2000 05:03:59 GMT
Organization: Deja.com
Lines: 92
Message-ID: <916vvt$q6k$1@nnrp1.deja.com>
References: <915n7v$n9c$1@nnrp1.deja.com>
NNTP-Posting-Host: 208.210.150.1
X-Article-Creation-Date: Wed Dec 13 05:03:59 2000 GMT
X-Http-User-Agent: Mozilla/4.72 [en] (X11; U; Linux 2.2.14-5.0 i586)
X-Http-Proxy: 1.0 x71.deja.com:80 (Squid/1.1.22) for client 208.210.150.1
X-MyDeja-Info: XMYDJUIDafilonov


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@nnrp1.deja.com>,
  seanldus@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/