Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

many-to-many join

many-to-many join

many-to-many join

I have 2 tables, each has a date as a primary index.
The dates are NOT unique!

for example data might look like
2008/01/31 col2 col3
2008/01/31 col2 col3
2008/01/31 col2 col3
2007/12/31 col2 col3
2007/12/31 col2 col3

2008/01/31 col2 col3
2008/01/31 col2 col3
2008/01/31 col2 col3
2008/01/31 col2 col3
2007/12/31 col2 col3

There are no other columns that can be indexed. They have
to be joined on the date.
This, of course has thousands of like indices.
the sql might look something like this:

select a.col1
from tablea as a,
tableb as b
where a.date = b.date
Any ideas?

RE: many-to-many join

There are many solutions.  General solutions that come to mind:

1. Do nothing - does the NUPI cause a performance slowdown?  If not then leave it.

2. Add another column to the NUPI to decrease skew. You didn't say what the other cols are but this might work if you add a NUSI on the original NUPI.

3. Use a PPI to eliminate paritions.

RE: many-to-many join

the main problem is that there are less rows in table a, (approx 650k) as opposed to tableb (1 million or so)

RE: many-to-many join

So you are getting a product join then or is it a performance problem?  You didn't explain what the problem is or what you want to accomplish.

I don't know exactly what you are trying to do, but I think you might need to normalize your tables to 3rd normal form.   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close