I understand that this discussion is old but my problem is new. I am using Teradat 13.10.
I have a large table eg: tablea with NUPI as (col1,col2,col3) . This table has approx 400 million rows.
Another table is smaller tableb with UPI as (cols1).
My query is
select col1,col2,col3,col4,col5 (all from tablea) from tablea inner join tableb
I am trying to force the redistribution of the smaller table, but for some reasons, the larger table gets redistributed.
I even tried to use the above logic and did the join using (table1.col1 = tableb.cols1 + 0) but it didn't help either.
I tried forcing the output into a table having similar primary index as the larger table but it didn't help either.
Can anyone suggest anything?
Thanks in advance
The reason that your tablea is redistributed is because the join is on UPI and no index. TableA has a three columns NUPI, and in the join condition you are only using one column, so basically no index will be used. On the other hand, tableb has a UPI and thus the optimizer is trying to redistribute the rows according to the UPI table join column.
You can either revisit the NUPI column selections for tablea because that is hammering the execution plan, while you can probably make some other column of tableb which qualifies for the index value, and create a USI on tableb.cols1
Thanks for the response and the suggestion.
What if I move all the data from the smaller table 'tableb' which has only 15 million rows into another table and create an altogether different PI.which won't have the col1 included....? Then in the join should it ignore the PI of tableb and start distributing it?
I don't have authority of changing the index of tablea, which is an enterprize wide table and won't be modified for my needs only :)
In that case the join will cost more as you are joining two non-index columns. TD will be redistributing the data for both of the tables on the joining columns to make sure that the data is on the same amp while joining.
As long as tableb has the UPI on the join column, it won't be redistributed! Is it possible that you can create the SI on the tablea.col1 only? The subtable rows will be on the same amp as tableb and will help the optimizer...
JI is one of the things that could help your query run faster. But not sure whether you will be allowed to create a JI on tablea or not!
Thanks again. yes you were right. I tried the above approach and it still didn't help. The larger table is still being redistributed.
There is already a secondary index in the larger table col1, so I guess Optimizer is doing the best it can, though it sounds illogical to me.
JI might not be an option because the smaller table is deleted and reloaded every week, so not sure how much extra burden it will create everytime this is done.