Home » SQL & PL/SQL » SQL & PL/SQL » What problem in this SQL statement? (oracle10gR2 Solaris5.9)
What problem in this SQL statement? [message #310097] Mon, 31 March 2008 05:17 Go to next message
wxfjordan
Messages: 92
Registered: December 2006
Member
The table "pt" has total 770986 rows. Execute the follow SQL statement cost 23 seconeds.

SELECT TO_CHAR(pt."tDateTime", 'YYYY-MM-DD') AS "ReportDate",
COUNT(pt."tID") AS "CounttID",
NVL(SUM(pt."S"), 0) AS "SumS",
NVL(SUM(pt."MeTotalAm"), 0) AS "SumMeTotalAm",
NVL(SUM(pt."CRAm"), 0) AS "SumCRAm",
NVL(SUM(pt."CRResultAm"), 0) AS "SumCRResultAm",
NVL(SUM(pt."CompanyCommissionAm"), 0) AS "SumCompanyCommissionAm",
NVL(SUM(pt."ShareHolderCommissionAm"), 0) AS "SumCompanyCommToGiveAmt",
NVL(SUM(pt."CompanyOddsAdjAm"), 0) AS "SumCompanyOddsAdjAm",
NVL(SUM(pt."CompanyTotalAm"), 0) AS "SumCompanyTotalAm"
FROM pt
WHERE TO_CHAR(pt."tDateTime", 'YYYY-MM') = '2008-02'
GROUP BY TO_CHAR(pt."tDateTime", 'YYYY-MM-DD')
ORDER BY TO_CHAR(pt."tDateTime", 'YYYY-MM-DD')

Do you think this time result is normal? If so, what problem in this SQL statement?

Re: What problem in this SQL statement? [message #310102 is a reply to message #310097] Mon, 31 March 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What problem in this SQL statement?

It is not formatted.

Quote:
Do you think this time result is normal?

Maybe.

Regards
Michel
Re: What problem in this SQL statement? [message #310108 is a reply to message #310102] Mon, 31 March 2008 05:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And further problems performance related problems might be revealed in the execution plan for the query.

Generally it's not a good idea to have a function on the data-side in the where clause, though, since that usually prevents possible existing indexes to be used.
Re: What problem in this SQL statement? [message #310146 is a reply to message #310097] Mon, 31 March 2008 07:18 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Is there a function based index ?
Re: What problem in this SQL statement? [message #310149 is a reply to message #310146] Mon, 31 March 2008 07:24 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You should read the oracle reference manual. It is detailed enough and answers most of your questions.

Regards

Raj
Previous Topic: Exceptions
Next Topic: How to drop the database
Goto Forum:
  


Current Time: Sat Dec 03 10:19:38 CST 2016

Total time taken to generate the page: 0.11163 seconds