Home » SQL & PL/SQL » SQL & PL/SQL » improving performance of a query
improving performance of a query [message #449] Wed, 13 February 2002 06:11 Go to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Hi there,

I have a very basic SQL query that selects usage records from a table based on a users login name. Then sums their usage. The problem is that the table contains over 10 million records. Is there any way to speed up the process? As you can see by my query I only want records that are of a type '2'. This considerably drops the amount of records to query to about one million. Perhaps there's some way to use PL/SQL to speed things up.

SELECT A.LOGIN_NAME, ROUND(SUM(A.DELTA)/3600,2) AS USAGE
FROM ACCT_BACKUP A
WHERE A.TYPE = 2
AND TRUNC(A.CREATED) >= '01-JAN-02'
AND TRUNC(A.CREATED) <= '31-JAN-02'
GROUP BY A.LOGIN_NAME

Any help would be greatly appreciated.
Re: improving performance of a query [message #450 is a reply to message #449] Wed, 13 February 2002 06:45 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
have u got right indexes. can u tell what index have u got. have u analyzed the table.
Re: improving performance of a query [message #455 is a reply to message #449] Wed, 13 February 2002 09:20 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
Normally an index is not used for functions. Makes sense, if you sum a column it has to read the whole table to get the sum. In 8i Function Based Indexes became available. Take a look in Meta link or your documentation on how to set it up.
Re: improving performance of a query [message #458 is a reply to message #449] Wed, 13 February 2002 13:11 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
As far as I know plsql is never faster as sql.

First of all have you analyzed your table? If not do it.
ANALYZE TABLE ACCT_BACKUP COMPUTE STATISTICS

One think which may help is to remove the trunc function (a function always uses ressources).
AND A.CREATED >= TO_DATE('01-JAN-02','DD-MON-YY')
AND A.CREATED < TO_DATE('01-FEB-02','DD-MON-YY')
But it will probably not help a lot.

Something else which may help is to create an index on the ACCT_BACKUP.TYPE column. But because about 10% of your rows have TYPE=2 this will not help a lot either and I'm not sure that the optimiser will use the index.

Something that will speed up a lot your query is to range partition your table over the CREATED key (if you have data from a longer periode of time). If this should still be to slow you can also subpartition it over the TYPE key or LOGIN_NAME key (I don't know in which of both case it will speed up more).

HTH
Mike
Re: improving performance of a query [message #480 is a reply to message #449] Thu, 14 February 2002 23:35 Go to previous message
Abul Fazal
Messages: 39
Registered: February 2002
Member
An additional Comment would be create a composite Primary Key on (Login_name, Type).
Previous Topic: Finding Records 60 days back from sysdate
Next Topic: Opinions on using distinct / unique
Goto Forum:
  


Current Time: Thu Sep 23 12:42:52 CDT 2021