Re: query performance -- revised!

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:40mBa.45638$1s1.611985_at_newsfeeds.bigpond.com...
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3ed5c393$0$19598$ed9e5944_at_reading.news.pipex.net...
> > "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
> > news:dBcBa.19$we7.173_at_news.oracle.com...
> >
> > > 7. Try capital letters in the table names -- optimiser respect those
> more.
> >
> > Ah, so you are joking right - missed that before
> >
> >
>
> Hi Niall,
>
> You may find this hard to believe but I find if I title my tables
> appropriate (eg. SELECT * FROM Mr_Emp, or SELECT count(*) FROM Mrs_Dept),
I
> find that the optimizer pays a *huge* amount of respect to such queries,
> ignores everything else and dedicates itself to ensuring it delivers the
> *perfect* execution plan.
>
> Honestly, give it a go, you'll be just amazed at the results ....

Gosh, how clever. Though you have to be careful about nls issues. For example using your code above performs only moderately on this UK machine, but if you make a small modification SELECT * FROM Sir_Emp, the query just flies.

Some simple tests suggest that NLS_CLASS_SYSTEM is the relevant parameter. A setting of UK_VICTORIAN results in the behaviour I see, but ALTER SESSION SET NLS_CLASS_SYSTEM=US_REPUBLICAN; results in the BANK_BALANCE column in DBA_TABLES driving the relative importance of queries.

I think I'll go and have a lie down now.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK