Re: eliminating redundant data

"gordy" <gordy_at_dynamicsdirect.com> wrote in message news:40bf9c49$1_1_at_Usenet.com...
> > Well, I don't know SQL Server, but in Oracle, you create an index using
the
> > CREATE INDEX statement. I suspect it works the same or similar in SQL
> > Server.
> >
> > Here's an Oracle example that creates an index called
> > "asearch_client_id_idx" on the client_id field in a table called
> > "alphasearch" owned by user "alphasearch":
> >
> > CREATE INDEX ALPHASEARCH.ASEARCH_CLIENT_ID_IDX
> > ON ALPHASEARCH.ALPHASEARCH(CLIENT_ID);
>
> wow, what a concept ;)
>
> I appreciate the criticism.. after all that's the intent of my original
> post, however, I would prefer it to be of the constructive variety.

It wasn't criticism. I thought you really didn't know about RDBMS indexes, being that you essentially created your own.

>
> In my system, there are 'fields' and there are 'variables'. the user
> creates the relationships between them whenever they send a message. in
> order to search for messages by 'variable' values, sql needs a
> relationship of its own to translate between them.
>
> this has kept me from being able to use the obvious:
> [records]
> id,field1,field2,field3,...
>
> because in a query for 'variable1', depending on the message it may have
> to look at 'field3' or 'field4' for the value. this requirement is why I
> have the tables I have now (recordindex, fieldindex and recordvalues).
>
> I realize this makes for very large indexes.. and like you said, the
> table itself is nothing more than a big index. This is the problem I'd
> like to solve. In my original post I explained how I attempted to
> eliminate redundant data, but I only eliminated 500mb (of 4gb) because
> the majority of volume in this db isn't the data itself, but the index
size.

The only similar situation I've seen like this (home-brew index constructs) is with a document imaging system called FileNET. In that case, the vendor actually created its own mini RDBMS to handle just these index/table constructs. It was very fast, but, of course, proprietary.

It's hard to tell exactly what you are trying to do, though. Could you get into the business requirements a bit? It would help me to understand what you need to do. It get the feeling from the solution you came up with that you are a programmer, not a DBA.

>
> Posted Via Usenet.com Premium Usenet Newsgroup Services
> ----------------------------------------------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----------------------------------------------------------
> http://www.usenet.com
Received on Fri Jun 04 2004 - 03:35:57 CEST