Zac Spitzer wrote: > Jochem van Dieten wrote: > | > | That is just a limitation of your specific dbms. Others can use much > | fancier indexing schemes. (If your database supports it, look into > | partial indexes for this type of problem.) > > interesting.... i think oracle (at least in the 8i world) & ms-sql don't > support it, which dbms *DO* support it?
PostgreSQL has them. I would expect DB2 to have them as wll, since IBM funded some research into this: http://simon.cs.cornell.edu/home/praveen/papers/partindex.de95.ps.Z (If you already know a little about indexing concepts it is interesting, but it is not a beginners guide.) Typical indexing would have a unique index over the primary key field without any predicate, and all other indexes only over the current records. In this case that would be: CREATE INDEX currentRecords_idx ON table (field) WHERE dtDeleted IS NOT NULL; Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
