| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: [oracle-l] Re: query plan is bad when it is run inside a pl/sql stored procedure
I agree. MINUS version looks more readable and easier to optimize.
That, precisely, was the reason why I left the intermediate version,
the one with the minus in the post. Of course, setting the transaction
read only will not really matter, if he has properly sized rollback segments
or undo tablespace.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hemant K Chitale
Sent: Wednesday, January 28, 2004 9:42 AM
To: oracle-l_at_freelists.org
Subject: Re: [oracle-l] Re: query plan is bad when it is run inside a
pl/sql stored procedure
A risk with writing two seperate SQLs {as in the NUM1 and NUM2 example}
is that Oracle will not guarantee a read-consistent image across both
SQLs *unless* you SET TRANSACTION READ ONLY.
In other words, if someone has issued an update after the first SELECT
started and committed
before the second one started, then the updated data would not be seen by
the first SELECT
but would be seen by the second SELECT .
A single SELECT statement, whether using NOT EXISTS or MINUS would be
read-consistent in itself.
Hemant
At 10:45 PM 27-01-04 -0500, you wrote:
>Query
>******************************************************
>SELECT count(1)
> FROM ats.emktg_members t1
> WHERE NOT EXISTS ( SELECT 'x'
> FROM gcd_data_source_details t2
> WHERE t2.universal_id = t1.universal_id
> AND t2.data_source_id = 13 )
> AND upper(t1.email) NOT LIKE '%TATA.COM'
>*****************************************************
>
>can be re-written like this:
>
>*****************************************************
>select count(*) from (
>select * from ats.emktg_members
>minus
>select * from ats.emktg_members t1,gcd_data_source_details t2
>WHERE t2.universal_id = t1.universal_id
> AND t2.data_source_id = 13
> AND upper(t1.email) NOT LIKE '%TATA.COM')
>*****************************************************
>
>That can further be reduced to :
>******************************************************************
>NUM1=select /*+ parallel(m,6) */ count(*) from ats.emktg_member m;
>
>NUM2=select * from ats.emktg_members t1,gcd_data_source_details t2
> WHERE t2.universal_id = t1.universal_id
> AND t2.data_source_id = 13
> AND upper(t1.email) NOT LIKE '%TATA.COM';
>*******************************************************************
>
>The desired count would then be NUM1 - NUM2.
>
>This way, you can execute 2 completely unrelated queries, without
>any correlation and then simply subtract the numbers. The first query
>can be optimized using the "brute force" approach (parallel query,
>choose the degree that your HW can tolerate) and a highly indexed query
>which is likely to complete in seconds. I've also sent the query
>to the oracle list, if someone else has anything to add, subtract,
>divide or multiply.
>
>
>On 01/27/2004 07:25:32 PM, S.Sarkar wrote:
> > could u please tell me how i can improve the query ?
> > the hash_aj hint was ignored by oracle.
> > moreover, the query works fine from sql*plus.
> > it chooses a bad plan when run from a stored procedure.
> >
> > sumant
> >
> > --- Mladen Gogala <mgogala_at_adelphia.net> wrote:
> > > It's a bad query that could probably be resolved throuh
> > > an analytic function but I don't normally delve into things
> > > like that before having finished my 2nd coffee. You can
> > > use hints, in particular, there is a hint to force hash join.
> > > On 01/27/2004 06:44:25 AM, S.Sarkar wrote:
> > > > All,
> > > >
> > > > i have this query:
> > > >
> > > > SELECT count(1)
> > > > FROM ats.emktg_members t1
> > > > WHERE NOT EXISTS ( SELECT 'x'
> > > > FROM gcd_data_source_details t2
> > > > WHERE t2.universal_id = t1.universal_id
> > > > AND t2.data_source_id = 13 )
> > > > AND upper(t1.email) NOT LIKE '%TATA.COM';
> > > >
> > > > This query finishes in about 5 minutes. The plan is:
> > > >
> > > > Operation Object Name Rows Bytes Cost Object Node
> > > > SELECT STATEMENT Hint=CHOOSE 1 14919
> > > > SORT AGGREGATE 1 75
> > > > HASH JOIN ANTI 272 K 19 M 14919
> > > > TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 14444
> > > > TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21
> > > K
> > > > 391
> > > > INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27
> > > >
> > > > However, when the same query is run from a stored
> > > procedure, it
> > > >
> > > > picks up a bad plan (with nested loops join) and does not
> > > > complete even after 6 hours ! Giving HASH_AJ hint did not
> > > > change
> > > > the plan.
> > > >
> > > > Any ideas how we can fix this (without using stored
> > > outlines) ?
> > > >
> > > >
> > > > The database is 9204 on sun solaris.
> > > >
> > > > regards,
> > > > Sumant
> > > >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! SiteBuilder - Free web site building tool. Try it!
> > http://webhosting.yahoo.com/ps/sb/
> >
>
>--
>Mladen Gogala
>Oracle DBA
>-------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>-------------------------------------------------------------
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://hkchital.tripod.com {last updated 24-Jan-04}
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jan 28 2004 - 08:57:19 CST
 |
 |