Querying data stores with SQL : Performance tuning : Oracle Marketing Cloud bulk operations

Oracle Marketing Cloud bulk operations

Hybrid Data Pipeline supports Oracle Eloqua bulk operations with some limitations. The Enable Bulk Load connection option can be used to enable or disable Oracle Eloqua bulk operations.

Note: Bulk operations are most efficient for queries that return large amounts of data for a relatively small set of columns. For example, SELECT folderid,name,country,c_website FROM Account WHERE country='Switzerland' has only four columns but many rows.

Bulk operations for some Select queries with a Top n clause are not supported because it is usually faster to use a standard query to fetch more columns for a few rows than to use a bulk operation. Nevertheless, you can use the Bulk Top Threshold connection option to control, in part, how queries with a Top n clause are handled. When bulk operations are enabled, bulk load is used to process queries with a Top n clause. The default value of Bulk Top Threshold is 1000.

Note: Bulk load must be enabled to support queries against Activity objects. If bulk load is not enabled, queries against Activity objects will fail.

Queries on Account and Contact tables have additional limitations. The following criteria must be met for queries on Account and Contact tables.

The result must have multiple rows.

The result cannot have more than 250 columns.

The result must include at least one user-defined column.

The query must either have no TOP n clause, or the value of n in the TOP n clause must be greater than the value specified in the Bulk Top Threshold option.

The query can only include columns that the bulk interface supports. For more information, see the table below.

Table 212. Columns that cannot be retrieved using bulk operations
Account table	Contact table
accessedAt	accessedAt
createdBy	bouncebackDate
currentStatus	currentStatus
Description	createdBy
folderId	Description
Permissions	folderId
scheduledFor	Permissions
sourceTemplateId	scheduledFor
updatedBy	sourceTemplateId
	subscriptionDate
	updatedBy
	unsubscriptionDate

The following table provides some query examples and describes why they would not take advantage of bulk operations and offers suggestions for modifying them. However, there will obviously be use cases where an application will use queries that cannot be returned using bulk operations.

Query	Description
SELECT * FROM Contact WHERE Country='Switzerland'	There are more than100 columns in the Contact table. To take advantage of bulk operations, constrain the SELECT statement to a set of 100 columns or less.
SELECT * FROM ContactList WHERE Region='East'	The ContactList table is not supported for bulk operations.
SELECT Id, C_Website FROM Account WHERE Id=17	This returns one row.
SELECT Id, C_Website FROM Contact WHERE Country='Switzerland' AND Region='EAST'	More than one criterion or comparison operators are used in the WHERE clause. Tips: If a query has zero or one comparison operators (meaning one of =, <=, <, >, >=, <>) in the WHERE clause, the query will usually be processed using the bulk operations. If a query contains the LIKE operator in the WHERE clause, the query is not processed using the bulk operations.

In this section:

Efficient queries