Hi again, Rob.

I read 'Yup, that was it' as a synonym for 'thank you' and didn't even notice. I have no idea how indexes disappear, but I would assume it is possible to do a backup/restore cycle that doesn't restore the indexes (I practically never do backups!). Of course, it is also possible to do things like ALTER INDEX INACTIVE or DROP INDEX and I believe (haven't checked) that a RECREATE TABLE would also remove any indexes.

As you can see, I have no experience with indexes surprisingly being dropped (then I would have known, rather than guess in the last paragraph). Generally, I don't fear things like this can happen. If a query is exceedingly slow, I check the PLAN unless my brain alerts me of my stupidity before I get that far (WHAT did I just tell Firebird to do? is an all too common self-accusation).

I haven't heard of indexes suddenly disappearing, what would be more common is for the index selectivity to change so that the optimizer chooses a different plan. It does so believing that this new plan is better and normally it is right. But occasionally it is wrong, and then the result can become very bad.

So, in general, I wouldn't bother to check that the indexes I've defined is present, but checking that an active index exists would be amongst the first things I'd check when performance vanishes and I see a NATURAL appearing where I expected an index to be used.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
Sent: 24. oktober 2007 22:14
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?

Hey Set,

One thing I forgot to say in my last email - THANK YOU for your help!
Sorry, politeness went out of the window in the middle of a working day.

;-)

Rob Davis


Robin Davis wrote:

> Hi Set,
>
> Yup that was it. Somewhere along the line the indexes had been lost on
> that machine!!!! How the heck can that happen? And does that mean one
> should check from time to time?
>
> Rob
>
>
> Svein Erling Tysvær wrote:
>
>> What's the PLAN on remote 2.0.3? Just wondering if this is a huge table and indexes on Name or CompanyName are missing (it has to go NATURAL on C, but should use at least one index for C2).
>>
>> Set
>>
>> -----Original Message-----
>> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
>> Sent: 24. oktober 2007 16:44
>> To: firebird-support@yahoogroups.com
>> Subject: Re: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?
>>
>> Hi Set,
>>
>> Thank you for your more refined query, it works on 2.0.1 remote and
>> 2.0.3 local just fine. However, the same problem exists with remote 2.0.3.
>>
>> Very strange.
>>
>> Rib Davis
>>
>>
>> Svein Erling Tysvær wrote:
>>
>>
>>> Are you sure it works on a local machine? I wasn't aware that you could compare EXISTS with true/false (it may be possible, I've just never seen that before). Try this "simplified" statement:
>>>
>>> SELECT c.CONTACTID, c.NAME, c.COMPANYNAME, c.ADDRESS1, c.ADDRESS2,
>>> c.ADDRESS3, c.ADDRESS4, c.POSTCODE, c.WORKPHONE, c.WORKEXTENSION,
>>> c.MOBILEPHONE, c.EMAILNAME
>>> FROM Contacts AS c
>>> WHERE Exists (select * from Contacts c2
>>> where c2.Name = c.Name
>>> and c2.CompanyName = c.CompanyName
>>> and NOT (c2.ContactID = c.ContactID))
>>> ORDER BY 2, 1;
>>>
>>> HTH,
>>> Set
>>>
>>> -----Original Message-----
>>> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
>>> Sent: 24. oktober 2007 16:07
>>> To: firebird-support@yahoogroups.com
>>> Subject: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?
>>>
>>> Hi,
>>>
>>> I'm obviously losing my marbles somewhere, but I seem to have the
>>> strangest problem.
>>>
>>> Using a 2.0.1 remote server on XP, this query works fine.
>>>
>>> SELECT c.CONTACTID, c.NAME, c.COMPANYNAME, c.ADDRESS1, c.ADDRESS2,
>>> c.ADDRESS3, c.ADDRESS4, c.POSTCODE, c.WORKPHONE, c.WORKEXTENSION,
>>> c.MOBILEPHONE, c.EMAILNAME
>>> FROM Contacts AS c
>>> WHERE (((Exists (select * from Contacts c2
>>> where c2.Name = c.Name
>>> and c2.CompanyName = c.CompanyName
>>> and NOT (c2.ContactID = c.ContactID)))<>False))
>>> ORDER BY 2, 1;
>>>
>>> Using the same query on a remote 2.0.3 server, latest release, the query
>>> stalls and returns no data. The really weird thing is, it DOESN'T cauase
>>> a problem on a local 2.0.3 server, only on remote.
>>>
>>> Anybody any clues as to where I should start?
>>>
>>> Thanks in advance,
>>>
>>> Rob Davis
>>>
>>>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org 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
>
>
>
>
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org 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