 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
HanslH
Guest
|
 Posted: Sat Feb 03, 2007 5:39 am Post subject: indexing tables on field calculations |
 |
|
In dbfcdx i can create indices like this
'padr(trim(net_nr) + trim(tel_nr),12)'
so i can find the telephone number '0301234567' if it is stored in 2
seperate fields in the table like this '030 ' and '1234567 '
or
'abs(payed-amount)' to get open invoices
Playing with SQL manager lite for interbase I get the feeling I can't
put anyting in indices but whole fields. So I gues what I have to do
is create calculated fields and calculate on them.
I can create a calculated field on trim(v1) + trim(v2) by doing
'trim(v1) || trim(v2)' : column is filled with right data
But I then cannot index on this field:
Unsuccessful metadata update.
Attempt to index COMPUTED BY column in INDEX IDX_KLANTEN.
With the absolute calculation i have even less luck as the whole
function is not recognized. Isn't abs() some basic function supposed
to be in any top kitchen drawer? |
|
| Back to top |
|
 |
Bill Todd
Guest
|
| Posted: Sat Feb 03, 2007 6:00 am Post subject: Re: indexing tables on field calculations |
|
|
InterBase, like most databases that are not xbase descendents, does not
support expression indices. Just create an index on the two fields that
contain the telephone number using the SQL CREATE INDEX statement.
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Sat Feb 03, 2007 7:36 am Post subject: Re: indexing tables on field calculations |
|
|
HanslH wrote:
| Quote: | In dbfcdx i can create indices like this
'padr(trim(net_nr) + trim(tel_nr),12)'
so i can find the telephone number '0301234567' if it is stored in 2
seperate fields in the table like this '030 ' and '1234567 '
Playing with SQL manager lite for interbase I get the feeling I can't
put anyting in indices but whole fields. So I gues what I have to do
is create calculated fields and calculate on them.
|
The difference is this: expression indexes, such as your above example,
allow more flexibility in what was indexed, but this is combined with the
limitation that you can only search using *one* index at a time. SQL
databases generally do not support expression indexes, but you can perform
searches on any combination of fields whether you have indexes on them or
not (but the engine will employ whatever indexes it can).
So in InterBase, just create an index on each of these two fields (or a
single compound index on both fields if you would never search on the second
field separately). In the Where clause of your Select statement you can
write:
where net_nr = '030' and tel_nr = '1234567'
or
where net_nr = '030' and tel_nr like '1234%'
Of course you can add any number of other conditions as well:
where net_nr = '030' and tel_nr like '1234%' and age > 40
If there's an index on age, it will be used, if not you will still get your
answer.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The legitimate powers of government extend to such acts only as are
injurious to others. But it does me no injury for my neighbor to say
there are twenty gods, or no God. It neither picks my pocket nor breaks
my leg." - Thomas Jefferson |
|
| Back to top |
|
|
HanslH
Guest
|
| Posted: Sat Feb 03, 2007 6:47 pm Post subject: Re: indexing tables on field calculations |
|
|
Okay, that's all clear with the string field. But how about the
calculated value floatfield2-floatfield1.How do I get instant
(indexed) results on floatfield2-floatfield1>0
Also there exist indices that can let you do instant searches on is
mydate between datefield1 and datefield2.
It isn't so much that I'm too impatient to have some searches go a bit
slower because there are no applicable indices, it's that I have
screens with lots of information from different sources that need to
be updated almost instantly for the total picture not to become
unusably slow.
The only thing i can come up with right now is to have record update
events check for floatfield1/floafliedt2 changes and update my own
calculated fields for which I then may have an index.
On Fri, 2 Feb 2007 20:36:19 -0500, "Wayne Niddery [TeamB]"
<wniddery (AT) chaffaci (DOT) on.ca> wrote:
| Quote: | HanslH wrote:
In dbfcdx i can create indices like this
'padr(trim(net_nr) + trim(tel_nr),12)'
so i can find the telephone number '0301234567' if it is stored in 2
seperate fields in the table like this '030 ' and '1234567 '
Playing with SQL manager lite for interbase I get the feeling I can't
put anyting in indices but whole fields. So I gues what I have to do
is create calculated fields and calculate on them.
The difference is this: expression indexes, such as your above example,
allow more flexibility in what was indexed, but this is combined with the
limitation that you can only search using *one* index at a time. SQL
databases generally do not support expression indexes, but you can perform
searches on any combination of fields whether you have indexes on them or
not (but the engine will employ whatever indexes it can).
So in InterBase, just create an index on each of these two fields (or a
single compound index on both fields if you would never search on the second
field separately). In the Where clause of your Select statement you can
write:
where net_nr = '030' and tel_nr = '1234567'
or
where net_nr = '030' and tel_nr like '1234%'
Of course you can add any number of other conditions as well:
where net_nr = '030' and tel_nr like '1234%' and age > 40
If there's an index on age, it will be used, if not you will still get your
answer. |
|
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Sat Feb 03, 2007 8:34 pm Post subject: Re: indexing tables on field calculations |
|
|
HanslH wrote:
| Quote: | Okay, that's all clear with the string field. But how about the
calculated value floatfield2-floatfield1.How do I get instant
(indexed) results on floatfield2-floatfield1>0
|
Add a column to the table to store the value. Index the column. Create
a before update trigger and a before insert trigger to compute the
value and assign it to the column.
| Quote: |
Also there exist indices that can let you do instant searches on is
mydate between datefield1 and datefield2.
|
Create an index on each of the date fields.
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
