Archives of the TeradataForum

Message Posted: Tue, 05 Nov 2003 @ 03:00:54 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Sorry if I did not communicate this properly. The concern about the Primary Index for a table is when you have multiple columns that are not covered in any joins then the query will do a full table scan.

In regards to the Bobj joins, if you are covering your primary index in the join and have additional columns that you are joins as residual criteria then you may find that you can take advantage of secondary indexes on these other columns.

I did not mean to communicate that you should change your Primary indexes. if you are currently covering the PI in your join strategies the perhaps that is the best you are going to get.

Try running explains on the SQL generated by Bobj... You can then play around with adjusting secondary indexes and determining if they are being accessed in the explain.

Another consideration is determining what tables are being redistributed, the FACT or the DIM tables. If you find the larger tables are being redistributed, you may want to adjust the indexing to get the smaller tables to be a part of the redistribution or try to get a nested merge join using your secondary index.

If you understand how the data is distributed and how the data must interact on the amps, then you can adjust your indexes to guide the data to the proper amps. It is an art. A big part of our job is to understand the data and not just the architecture. What are your customers trying to answer with there queries?

Other considerations are locks and blocks... Do you find that some of your reports run faster at different times in the day??? Perhaps you have some blocking that needs to be investigated... Are you using DISTINCTS in your views versus GROUP BY??? Do you have LOCKING FOR ACCESS on your views???

It all adds up to make a huge consistent difference in performance...

Bobj slows things down anyway so you need everything that you can get...

Chris Coffing
Senior Vice President
Coffing Data Warehousing
www.coffingdw.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>