Defeating an Unwanted Join Index?

[troutodg8]

Does anyone know a trick I could use to get the TD PE to not use an existing join index? Dropping the index is not an option for me.

In one case, I have been able to write a view over a table and include a column that's not in the JI - that worked fine. Solutions like that would be very handy.

In this particular case, tho, all of the columns in the table are also in the JI, so that won't work here.

The SQL is being generated out of Microstrategy, so I don't have 100% flexibility in structuring it, but I do have the luxury of being able to create views on top of the table.

BTW - i'm on v2r5

Thanks in advance!!

ps - i'm a newbie here. I searched and didn't find this question, but I apologize if it's been asked and answered.

 

[tdatgod]

Why doesn't the Join index work for the query? Wrong results? wrong PI? In the optimizers opinion accessing the JI is the BEST way to accomplish your query. If you disagree I suggest you contact the Teradata Global Support Center and file an incident and them look at your problem. Maybe you found a BUG in the optimizer.

 

[troutodg8]

tdatgod,

Thanks for the reply - the point of this JI is to redistribute the data on a different hash key. We have a ton of consumers that belong to consumer groupings. the xref table is hashed on consumer id (CID) but the JI is hashed on the CID group.

So if I go after a CIDgroup with 5 million cids in it, the table will be very smoothly distributed, but the JI will use a single AMP for the work.

It's my personal opinion that the JI shouldn't be there at all, but i'm not a DBA....

Since I can't really get rid of it, i was hoping there was a way to trick the optimizer to not use it.