My Oracle Support Banner

Using Hints to Optimize an Update with Subquery Not Using Index on Updated Table (Doc ID 68084.1)

Last updated on OCTOBER 23, 2019

Applies to:

Oracle Database - Enterprise Edition
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Purpose

This document suggests methods of processing update statements that contain subqueries so that the query drives off the subquery (i.e. it examines the subquery first before it looks at the table to be updated). This can have advantages when the subquery contains information that would allow indexes to be used on the updated table that would otherwise be unavailable. Note that the use of the techniques illustrated here are not restricted to updates but can be modified to affect many other queries.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.