Home » SQL & PL/SQL » SQL & PL/SQL » Tune This Simple SQL
Tune This Simple SQL [message #258] Fri, 01 February 2002 06:59 Go to next message
Jay
Messages: 127
Registered: October 1999
Senior Member
Hi, can anyone give some tips on tuning this SQL code to run a little faster?

select cycle_code, cycle_run_month, cycle_run_year, count(distinct(ban))
from us0801
where cycle_code = '8'
and cycle_run_month = '01'
and cycle_run_year = '2002'
group by cycle_code, cycle_run_month, cycle_run_year
order by cycle_code, cycle_run_month, cycle_run_year;
Re: Tune This Simple SQL [message #266 is a reply to message #258] Fri, 01 February 2002 18:01 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
are those 3 columns in where clause indexed?
Re: Tune This Simple SQL [message #269 is a reply to message #258] Sat, 02 February 2002 07:08 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
The are several ways to improve a perfomance of a query but for this more infos are needed.

How may rows does the table contain?
Is the table partitionned?
Are there any indexes?
Is the table frequently updated?
Have you analyzed the table (ANALYZE TABLE us0801 COMPUTE STATISTICS)?
Re: Tune This Simple SQL [message #306 is a reply to message #258] Mon, 04 February 2002 10:52 Go to previous messageGo to next message
Jay
Messages: 127
Registered: October 1999
Senior Member
The only column being selected that is indexed is BAN. The table in question/example has a 1,943,792 row count. I don't think that the table is partitioned, is there an easy way for me to tell? The table has frequent inserts, not as may updates, but does have daily updates, it's a usage
table. The analyze command, what's the exact syntax of that so I can run it, I could get the sql below to run. Thanks, Jay
Re: Tune This Simple SQL [message #318 is a reply to message #258] Tue, 05 February 2002 09:26 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
An easy way to (probably) improve your query is to create an index over the columns cycle_code, cycle_run_month and cycle_run_year.
CREATE INDEX i_us0801_y_m_c ON us0801(cycle_run_year, cycle_run_month, cycle_code);

Once the index is created execute:
ANALYZE TABLE us0801 COMPUTE STATISTICS;

HTH
Mike
Previous Topic: sql statement help
Next Topic: Unique ID for a server
Goto Forum:
  


Current Time: Sat Sep 25 00:52:55 CDT 2021