I have what I call a "dictionary" table which supports a "master table".
This dictionary table is designed to hold generic data :
CREATE TABLE sup_data (link_id uniqueidentifier, field_name varchar(255),
field_data text) WITH OIDS; ...
It works well when you're digging into it to pull the supplementary information
for a small number of rows in the master table. It uses an index on the
link_id, and can jump right to the few pages for the supplemental data. That
was the design.
Now "the powers that be" want to do some aggreate inquiries on subsets of the
generic data, based on many rows from the master table. This doesn't work so
well... Its having to pull many pages to create the result set to aggreate on.
If I could cluster the generic data to match the clustering on the "master
table" it would reduce the number of pulled pages considerably and the speedup
would make it work well.
I'm trying to avoid replicating the column and index used to cluster the main
table in this dictionary table.
Is it even possible to cluster a table based on the clustering scheme (which is
not the link_id ...) from the master table?
Can you gurus think of a better strategy? :) (Please??) :)
CG
____________________________________________________
Sell on Yahoo! Auctions no fees. Bid on great items.
http://auctions.yahoo.com/