Re: SQL

Could you post execution plan, statistics? Concatenated index on (TEST_DATE,TEST_VAL) might help, as well as increasing the size of sort_area (if you find, that it uses disk for sorting).

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, February 06, 2003 10:39 AM

> Lots of emails were sent yesterday, so in case if any kind soul had missed
my request (which didn't bear any result), I am posting this again. Please help if you can.
>
> Thanks.
> --
>
> On Wed, 05 Feb 2003 09:59:29
> waseem khan wrote:
> >Folks,
> >
> >Is there a much better way of writing the following SQL statement,
bearing in mind the performace hits due to the use of functions in the GROUP BY and ORDER BY clauses?
> >
> >SELECT TO_CHAR(TEST_DATE, 'MON RRRR') XXX, count(*)
> >FROM TBL_XYZ
> >WHERE TEST_VAL = DECODE(:P_VALUE, 'BEGIN', 'B', 'END', 'E')
> >AND TEST_DATE BETWEEN :P_START_DATE AND :P_END_DATE
> >GROUP BY TO_CHAR(TEST_DATE, 'MON RRRR')
> >ORDER BY TO_DATE('01 '||XXX);
> >
> >Thanks.
> >
> >
> >
> >_____________________________________________________________
> >Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year.
> >http://login.mail.lycos.com/brandPage.shtml?pageId=plus&ref=lmtplus
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: waseem khan
> > INET: waseem.khan_at_lycos.com
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
> >
>
>
> _____________________________________________________________
> Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year.
> http://login.mail.lycos.com/brandPage.shtml?pageId=plus&ref=lmtplus
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: waseem khan
> INET: waseem.khan_at_lycos.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).