Subject RE: [firebird-support] Is there something like an Indexed View or indexed Computed Column?
Author Svein Erling Tysvær
Seeing the query, view and index definitions (so that we know which fields are involved) and the plan normally helps quite a bit. Also, it would be good to know which version of Firebird you're using and how selective the indexes in the fields are. And how many rows does your query return?


-----Original Message-----
From: [] On Behalf Of Jorge Martin
Sent: 3. november 2009 15:25
Subject: [firebird-support] Is there something like an Indexed View or indexed Computed Column?

I have an issue where I'm trying to sort a massive result set and it's very slow even though everything is indexed. The issue is that the sort is based on a value that a foreign key points to and it looks like Firebird needs to pull a lot of data into a temporary area so it can do the index + sort on the result set. This is very slow and uses a lot of temporary space. The query runs very fast if I don't sort it so this is totally due to the ORDER BY clause.

What I think I need to do is create a phantom column in one table that is indexed by the values in another table. Basically I want to index the foreign key column based on the *value* in the foreign table instead of the foreign key ID. I hope that makes sense.

MS SQL Server supports what they call "Indexed Views" which I think would do what I'm talking about. Or if I could index a Computed Column that might work as well (maybe not though because I'm not sure of the efficiency computing a column based on a foreign table).

I can post a simplified version of my schema if that would help in understanding what my issue is. Otherwise, any ideas?


Hotmail: Trusted email with Microsoft's powerful SPAM protection.

[Non-text portions of this message have been removed]



Visit and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at

Yahoo! Groups Links