On Wed, May 07, 2014 at 04:07:22PM -0500, John McKown wrote: > On Wed, May 7, 2014 at 3:36 PM, Randy Westlund <rwestlun@xxxxxxxxx> wrote: > > > I have a table with N (over 35k) rows. I need to compare each of > > those elements to every other element in the list, which is the > > handshake problem. > > > > This results in a symmetric matrix (an adjacency matrix for an > > undirected graph). Because all relations are symmetric, I only need to > > store the upper triangle of this matrix. A ~ B and B ~ A are the same. > > > > I need some advice on how to implement this. > > Just a crazy idea, but have you considered something like the below: > > create view view1 as select id1 as v_id1, id2 as v_id2, value from abovetbl > union select id2, id1, value from abovetbl; > > The above basically "expands" the "small matrix" table to have the same > values that the "full matrix" table would have. because id1, id2 has the > same "value" as id2, id1 . > > You can now create an index on "view1" like: > create index view1_index on view1(v_id1); > > To find all the values for "B", you can now: > select * from view1 where v_id1='B'; > > The view does not take up much space. But the index on the view might. But > only about as much space as an index on the "full matrix" table would. Oh, > I changed the column names in the view so that it would hopefully not be as > confusing has having multiple "id1" and "id2" column names with different > meanings. > > I'm not a real heavy, but this may be of some use. > -- > There is nothing more pleasant than traveling and meeting new people! > Genghis Khan > > Maranatha! <>< > John McKown This was exactly what I needed, thank you. Everything is working now :)
Attachment:
signature.asc
Description: Digital signature
