Hi,

Many thanks for your help. The query that took 3 hours now runs on 10
seconds. It was very useful your hint about indexes in the PLAN, because as
only appeared one index, I didn't understand the use of declaring more than
one index in a table. Adding one more index has improved all queries, not
only this one, about 25%

I have one more question about indexes, but I will start a new thread,
because is not exactly related to this query

Diego

----- Original Message -----
From: "Ali Gökçen" <alig@...>
To: <firebird-support@yahoogroups.com>
Sent: Thursday, October 06, 2005 8:43 PM
Subject: [firebird-support] Re: Query optimization


> Hi Diego,
>
> Now, after a shower i understand your query perfectly.(whether?) :))
>
> you need all rows if their level=1 and inretact_id is minimum for
> same MSISDN. ok?
>
>>SELECT COUNT (OC.MSISDN)
>> FROM OPPORTUNITY_CALLS OC
>> WHERE OC.OPP_DATE >= '2005-10-01' AND OC.OPP_DATE <= '2005-10-10'
>> AND OC.MSISDN LIKE '6________'
>> AND OC.LEVEL = 1
>> AND NOT EXISTS(
>> SELECT 1
>> FROM OPPORTUNITY_CALLS OC2
>> WHERE OC.MSISDN=OC2.MSISDN
>> AND OC2.OPP_DATE >= '2005-10-01' AND OC2.OPP_DATE <= '2005-10-10'
>> AND OC.INTERACT_ID < OC2.INTERACT_ID
>> )
>
>
>
> create procedure level1_counter(datemin date, datemax date)
> returns(numberofrows integer)
> as
>
> declare prev_msisdn varchar(9);
> declare msisdn varchar(9);
> declare level smallint;
> declare interact_id integer;
> declare min_interact_id integer;
>
> begin
>
> prev_msisdn='';
> min_interact_id=999999999;
> numberofrows=0;
>
> FOR
> SELECT MSISDN,LEVEL,interact_id
> FROM OPPORTUNITY_CALLS
> WHERE OPP_DATE BETWEEN :DATEMIN AND :DATEMAX
> AND MSISDN STARTING WITH '6'
> AND substring(MSISDN,9,1)<>'' -- if all 9 chars used
> -- AND substring(MSISDN,10,1)='' no need, msisdn allready 9 chars
> ORDER BY MSISDN -- msisdn index will help to this order, there is
> no grouping, sorting etc..
> INTO :msisdn,:level,:interact_id
> do
> begin
>
> -- count your rows here with some compare and assigning
> -- if the min_interact_id is at level1 for the same msisdn then
> numberofrows=numberofrows+1
>
> end
>
> -- check again for last row
>
> suspend; -- return numberofrows
>
> end
>
>
> select * from level1_counter('2005-10-01','2005-10-10')
>
> Regards.
>
> Ali
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>