many-to-many join

[ppbbcp]

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

for example data might look like
Tablea
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
.
.
Tableb

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
,b.col2
from tablea as a,
tableb as b
where a.date = b.date
Any ideas?

 

[j355ga]

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.

 

[ppbbcp]

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

 

[j355ga]

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.