Sorry, this is the query PLAN

PLAN (OC2 INDEX (IDX_OPPORTUNITY_CALLS))
PLAN (OC INDEX (IDX_OPPORTUNITY_CALLS))

Still the same query PLAN if I add 2=0 (it is already using the index)

The table has 5279698 rows and 1937943 distinct MSISDN

Diego



--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> Hi Diego!
>
> Your description lacks a few important things: The query PLAN and a
> description of the selectivity of the indexes.
>
> Myself, I often do something similar to
>
> SELECT COUNT(*)
> FROM OPPORTUNITY_CALLS OC
> WHERE OC.OPP_DATE BETWEEN '2005-10-01' AND '2005-10-10'
> AND OC.MSISDN STARTING '6'
> AND OC.LEVEL = 1
> AND NOT EXISTS(
> SELECT 1
> FROM OPPORTUNITY_CALLS OC2
> WHERE OC.MSISDN=OC2.MSISDN
> AND (2=0
> OR (OC2.OPP_DATE BETWEEN '2005-10-01' AND '2005-10-10'
> AND OC.INTERACT_ID < OC2.INTERACT_ID)))
>
> But that may be because I'm used to MSISDN being very selective

(well,

> I don't work with anything remotely similar to ISDN), whereas the
> other requirements rarely are (I'm used to there being 800000
> different MSISDNs in a table with 1000000 records). If you have the
> opposite (e.g. 50 different MSISDNs in your 5000000 records table),
> then you would definitely not want to do this. The 2=0 part is

simply

> to prevent the optimizer from using less selective indexes than what
> would be optimal.
>
> HTH,
> Set
>
> --- In firebird-support@yahoogroups.com, "Diego Rodriguez" wrote:
> >
> > Hi all!
> >
> > I have a query performance problem. The query only uses one

table.

> I have to make a count on MSISDN that called during a period and

have

> a value of field LEVEL of 1. One MSISDN could call more than once
> during that period (and could have another value of LEVEL), and I

have

> to count only the last one (surrogate key field INTERACT_ID the
> greater of all MSISDN for that period) if the last one is LEVEL 1.

The

> table has now 5 million rows and will have at most 12 millions.
> >
> > My first approach was to check if EXISTS another MSISDN for that
> period with an INTERACT_ID greater. I have read in the list and in
> Helen book that is better to join the table to itself than use the
> EXIST clause, but I don't know how to do it. This works, but as the
> table grows performace is getting worse and with 5 millions is

almost

> non acceptable, so when it reaches 12 millions in some weeks it will
> hang my app.
> >
> >
> > Is there a way to do it better?
> >
> > Using Firebird classic server 1.5.2 in a Windows XP plattform

with

> 1Gb RAM
> >
> > This is the query
> >
> > 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
> > )
> >
> > And this is the table
> >
> > CREATE TABLE CALLS (
> > INTERACT_ID INTEGER NOT NULL,
> > MSISDN VARCHAR(9),
> > OPP_DATE DATE,
> > OPP_TIME TIME,
> > LEVEL SMALLINT);
> >
> > ALTER TABLE OPPORTUNITY_CALLS ADD CONSTRAINT PK_OPPORTUNITY_CALLS
> PRIMARY KEY (INTERACT_ID);
> >
> >
> > Many thanks
> >
> > Diego