Very helpful, I learnt something. Thanks to you all!!
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<3f1c438b$0$15030$cc9e4d1f_at_news.dial.pipex.com>...
> "rohit" <rohitk1973_at_yahoo.com> wrote in message
> news:74353ecc.0307161027.59d8c62c_at_posting.google.com...
> > One of the things I try while tuning is to do a count(*) over the
> > tuned SQL, this saves me the full result dump, and doesnt lose the
> > query execution timing while doing IO. But I am not very sure if
> > thats a right thing to do.
> > For example to get timing on a query like--
> >
> > select /*+ USE_HASH(t2) */
> > from t1,t2
> > where t1.a = t2.a
> > and t2.b = 'xyz';
> >
> > I would run a SQL like:
> >
> > select count(*) from
> > (select /*+ USE_HASH(t2) */
> > from t1,t2
> > where t1.a = t2.a
> > and t2.b = 'xyz');
> >
> > and I may try different combinations of hints that look good in
> > explain plan outputs.
>
> I'd definitely look at the explain, count(*) often will favour index range
> scans over he actual access plan for the query.
>
> Another thing you can try is to replace
>
> select col1,col2.....
> from
> where......
>
>
> with
>
> select AGGREGATE_FUNCTION(col2)
> from
> ....
>
> where AGGREGATE_FUNCTION is an aggregate function (doh!) other than count(*)
> on an unindexed colum AVG is quite good because it rarely changes execution
> plans.
>
> This will eliminate returning the large resultset (thus eliminating display
> time) but usually give the same access path as the actual query.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
Received on Tue Jul 22 2003 - 15:36:50 CDT
