Topics: |
Adapter optimization allows the RDBMS to perform the work for which it is best suited, reducing the volume of RDBMS-to-server communication and improving response time. It also enables the RDBMS to exploit its own internal optimization techniques.
For more information, see Optimizing Requests and Optimizing Requests to Pass Virtual Fields Defined as Constants.
How to: |
The Adapter for Teradata supports array insert if the release of Teradata Client is:
This technique substantially reduces network traffic and CPU utilization.
Using high values increases the efficiency of requests involving many rows, at the cost of higher virtual storage requirements.
Tip: You can change this setting manually.
In combination with LOADONLY, the block size for an INSERT applies to MODIFY INCLUDE requests. INSERTSIZE is also supported for parameterized DIRECT SQL INSERT statements.
ENGINE SQLDBC SET INSERTSIZE n
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Is the number of rows to be inserted using array insert techniques. Accepted values are 1 to 5,000. The default value is 1. If the result set contains a column that has to be processed as a LOB, the INSERTSIZE value used for that result set is 1.
How to: |
Reference: |
A left outer join selects all records from the host table and matches them with records from the cross-referenced table. When no matching records exist, the host record is still retained, and default values (blank or zero) are assigned to the cross-referenced fields. The adapter can optimize any WHERE-based left outer join command in which the conditional expression is supported by the RDBMS.
JOIN LEFT_OUTER FILE hostfile AT hfld1 [TAG tag1] [WITH hfld2] TO {UNIQUE|MULTIPLE} FILE crfile AT crfld [TAG tag2] [AS joinname] [WHERE expression1; [WHERE expression2; ...] END
where:
Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.
Is the host Master File.
Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used as segment references.
Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in its data source that is referenced.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.
Is a data source field with which to associate a DEFINE-based conditional JOIN. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.
Specifies a one-to-many relationship between from_file and to_file. Note that ALL is a synonym for MULTIPLE.
Specifies a one-to-one relationship between hostfile and crfile. Note that ONE is a synonym for UNIQUE.
Note: Unique returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).
The unique join is a WebFOCUS concept. The RDBMS makes no distinction between unique and non-unique situations; it always retrieves all matching rows from the cross-referenced file.
If the RDBMS processes a join that the request specifies as unique, and if there are, in fact, multiple corresponding rows in the cross-referenced file, the RDBMS returns all matching rows. If, instead, optimization is disabled so that WebFOCUS processes the join, a different report results because WebFOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.
Is the cross-referenced Master File.
Is the join field name in the cross-referenced Master File. It can be any field in the segment.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.
Is the name associated with the joined structure.
Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.
or
or
The following request creates a left outer conditional join between two Teradata data sources and reports against the joined data sources. The STMTRACE is turned on in order to view the SQL generated for this request:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT JOIN LEFT_OUTER FILE baseapp/EQUIP AT CARS TO ALL FILE baseapp/CARREC AT CARC WHERE CARS NE CARC; END TABLE FILE baseapp/EQUIP PRINT CARS CARC STANDARD BY MODEL END
The WebFOCUS request is translated to a single Teradata SELECT statement that incorporates the left outer join, and the non-equality condition is passed to the RDBMS in the ON clause:
SELECT T1."CARS"(CHAR( 16)),T1."STANDARD"(CHAR( 40)), T2."CARC"(CHAR( 16)),T2."MODEL"(CHAR( 24)) FROM ( EQUIP T1 LEFT OUTER JOIN CARREC T2 ON (T1."CARS" <> T2."CARC") ) ORDER BY T2."MODEL";
WebFOCUS | |
Feedback |