Re: Expressional Indexes

>> For example, if I want to index on a date field but only have the index 
>> keep track of the most recent 30 days (and then create a secondary
>> index for all dates) so as to improve performance on more heavily
>> loaded systems. 
>> 
>> Am I understanding this new terminology correctly?  Thanks in advance. 
> 
> No, you could do the above using "partial indexes" but it wouldn't work
> very well in this case because the "last 30 days" keeps moving and you
> would have to keep redefining the index periodically.
       For the application I will need to develop in the future, it would be 
okay for this index to hold data more than 30 days old, and then be 
redefined on a monthly basis along with regular database vacuuming (and 
other maintenance).
       Could this be done with a "partial index" as follows?
               CREATE INDEX my_index on my_table (create_date)                 WHERE (create_date > age(timestamp '30
days'));
       If I've made any mistakes here, please don't hesitate to let me know 
because the age() function is new to me.

> It also wouldn't really help performance.
       Really?  A smaller index would result in fewer comparisons behind-the-
scenes though, wouldn't it?

> Expression Indexes are just more powerful "functional indexes". In 7.3
> they could be used for indexing expressions like "lower(foo)". In 7.4
> they're more powerful and you can index expressions other than simple
> function calls. 
[sNip]
       So an "Expression Index" could, for example, be used to sort alpha-
numeric data in a case-insensitive manner?  I just want to make sure I'm 
understanding this correctly.
       Thanks.

-- 
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.