Updatable Cursors Optimize Query Design | Teradata Vantage - Using Updatable Cursors to Optimize Query Design - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

In ANSI mode, you can define a cursor for the query results and for every row in the query results in order to update or delete the data row via the cursor associated with the row.

This means that update and delete operations do not identify a search condition; instead, they identify a cursor (or a pointer) to a specific row to be updated or deleted.

You can use pocketable cursors to update each row of a select result independently as it is processed.

Recommendations

To reap the full benefit from the updatable cursor feature, you should minimize:

  • The size of query result and number of updates/transaction
  • The length of time you hold the cursor open

Using many updates per cursor may not be optimal because:

  • They block other transactions.
  • The system requires longer rollbacks.

In this case, use the MultiLoad utility to do updates.