Sometimes, regardless of the join plan or indices defined, certain joins cannot be performed in a short enough time frame to satisfy the users. When this is the case, another alternative must be explored. Later chapters in this book discuss temporary tables and summary tables as available techniques. If none of these provide a viable solution, yet another option is needed.
The other way to improve join processing is the use of a JOIN INDEX. It is a pre-join that stores the joined rows. Then, when the join index "covers" the user's SELECT columns, the optimizer automatically uses the stored join index rows to retrieve the pre-joined rows from multiple tables instead of doing the join again. The term used here is covers. It means that if all columns requested by the user are present in the join index it is used. If even one column is requested that is not in the join index, it cannot be used. Therefore, the actual join must be processed to get that extra column.
The speed of the join index is its main advantage. To enhance its on-going use, whenever a value in a column in a row for a table used within a join index is changed, the corresponding value in the join index row(s) is also changed. This keeps the join index consistent with the rows in the actual tables.
The syntax for using a join index:CREATE JOIN INDEX <join-index-name> AS<valid-join-select-goes-here> ;
There is no way for a client to directly reference a join index. The optimizer is the only compound that has access to the join index. For more information on join index usage, see the NCR reference manual.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.