Hi all, please suggest me for my following situation. i have a table1 with 4 columns as its composite NUPI(col1,col2,col3,col4). When ever table1 is addressed in my warehouse it is typically done in this way. sel * from table1 A left join table 2 B on A.col1=B.col1 and A.col2=B.col2 ,table 3 C where A.Col3=C.col3 A.col4=C.col4 please note the columns joined in the above query are only thro NUPI of the respective tables
My question here is whether teradata will use the NUPI for its retrieval bcoz in explain i dont see any word that says thrrough index retrieval but find the word index join confidence.
and also is there potentially any wrong in index selection or is there an better index that u can recommend. there are no new queries that hit table1 expect the one described above
By specifying a NUPI of either "(col1, col2)" or "(col3,col4)" on table1, you can get it to join table1 to either table2 or table3 without a redistribution of the table. What are the sizes of each of the tables?
Also, is there no criteria used in these queries other than the join criteria specified? If you only have join criteria, the best you're going to be able to do with indexes (through a primary or join index) is to avoid a redistribution of one or more of the tables in the query. Other indexes, such as secondary indexes will only be used if there is some other criteria present in the query (other than join criteria).
You might want to also post your actual SQL and explain to get additional help.
Hi Bary, Thankyou for ur time. i am very sorry that i am nor clear with my previous post.here is my situation. The query that i used is the only query and it has no other criteria other that the one specified. Mu table1 is comparively small with some lakh records but table2 is very huge than table 3(lakh records) and table 1. My table1 has presently NUPI defined on col3 and col4.i want to redefine the index as i see the explain plan redistributing all the tables.
i just want you to guide me to select the proper NUPI for table1. whether to select(col1,col2,col3,col4) as NUPI or to select (col1,col2) as NUPI as these columns are used to join table2 which is very big. Potentially i should not be using any secondary indexes or join indexes for this case as they are not allowed in my warehouse. please guide me to choose the proper index for table1.
If table1 and table3 are small and also have the same NUPI, I would think that the optimizer would be joining those two first, and then redistributing and sorting the result to join to table2, avoiding the redistribution of table2. This is not what you are seeing?
Do the estimates in the explain look correct? Can you post the explain?
yes bary , This is what is happening in my explain plan if i include the index.i work in a private network so i am not able to copy it an paste as it is restricted.so please bear with me. I will some how try to post it to u.until then please bear with me. thanks for your time
If you are avoiding the redistribution of your large table, then your query is about as efficient as it's going to get without doing something like creating a join index. Do you have a requirement to make it run faster than it currently runs or are you just trying to find out if it's running as efficiently as it can?
Bary, Actually we got spool space error while running the query befor joining the 2 columns with the current columns as NUPI. My requirment is to suggest some stats and potentially change the NUPI(if needed).so that we can avoid spool space error in future. My question is either to keep the existing and add these columns as NUPI(MY manager is of the view that if we add these two columns to the existing then the optimizer might ignore the index and go for FTS) or to take the existing columns and keep only these two columns as NUPI so that redistribution of large table is avoided Request to suggest me an approach
In order to help determine the optimal path, you'll want to know the following things:
Number of rows in table1? Number of rows in table2? Number of rows in table3?
Expected number of rows when table1 is left outer joined to table2? Expected number of rows when table1 is inner joined to table3?
If table1 and table3 are small, and the number of resulting rows after joining table1 and table3 are small, then I would say to make the NUPI of table1 and table3 (col3, col4) and make the NUPI of table2 (col1, col2). The optimizer should join table1 and table3 together first and then redistribute and sort the result and join to table2.
I would collect stats on all of the NUPI's and on the join columns (if different from the NUPI).