The New Home of Oracle-wiki.net
All things oracle...
A website for IT Professionals involved with Oracle database.
USE_CONCAT Hint
Introduction
In our ongoing series on Oracle hints, we'll examine the purpose and usage of the USE_CONCAT hint. The USE_CONCAT hint in Oracle instructs the optimizer to transform a query with OR conditions in the WHERE clause into a UNION ALL query. This transformation can enhance performance by enabling Oracle to optimize each condition individually and leverage indexing more effectively.
The Way it Works
When a query includes multiple OR conditions, Oracle usually processes them through a full table scan or index filtering, depending on index availability. However, in certain scenarios, converting the query into multiple UNION ALL branches can be more efficient. Here are some reasons why
Index Utilization - If different OR conditions can benefit from separate indexes, using USE_CONCAT allows Oracle to perform indexed scans independently for each condition rather than a full table scan.
Parallel Execution - Each branch of the UNION ALL can be executed in parallel, improving performance on large datasets.
Predicate Filtering - Oracle can optimize each subquery independently, applying the best access path for each part of the OR condition.
Reduced Logical I/O and Buffer Cache Usage - By transforming the query into UNION ALL, Oracle can reduce unnecessary block reads because each branch of the UNION ALL focuses only on the subset of data that satisfies a specific condition, reducing buffer cache usage.
Improved Cardinality Estimates - The USE_CONCAT hint allows Oracle to estimate cardinality separately for each condition in the UNION ALL, potentially leading to a more accurate plan selection.
Potential for Partition Pruning - If the table is partitioned, USE_CONCAT can help Oracle prune partitions more effectively. Instead of scanning unnecessary partitions due to broad OR conditions, each subquery in the UNION ALL can target specific partitions, reducing the number of rows scanned.
Avoiding Suboptimal Index Merging - When multiple indexed columns appear in OR conditions, Oracle may attempt index merging, which doesn’t always yield the best performance. By using USE_CONCAT, Oracle can use the most efficient index for each subquery independently, avoiding the overhead of index merging.
There are many examples for using the hint USE_CONCAT, but lets walk through one of them
Example
Lest assume we have a large sales table partitioned by sale_date, with indexes on customer_id and product_id
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
customer_id NUMBER,
product_id NUMBER,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
PARTITION sales_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);
CREATE INDEX idx_customer_id ON sales(customer_id);
CREATE INDEX idx_product_id ON sales(product_id);
Now, let's run our query where we want to find all sales made by customer 101 or for product 202
SELECT *
FROM
sales
WHERE
customer_id = 101
OR
product_id = 202;
In this example, we may find that this query is sub-optimal for the following reasons
Partition pruning may not work well as Oracle might have to scan multiple partitions since it sees an OR condition spanning different columns.
Oracle may attempt index merging (customer_id index + product_id index), which is sometimes slower than using separate index scans.
If Oracle's optimizer decides that index merging is expensive, it might default to a full table scan, increasing the I/O overhead.
The query plan may look something like this
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1000 (100)| |
| 1 | TABLE ACCESS FULL | SALES | 10000 | 500K | 1000 (100)| 00:00:15 |
-------------------------------------------------------------------------------------
Running the query with the hint
SELECT /*+ USE_CONCAT */ *
FROM
sales
WHERE
customer_id = 101
OR
product_id = 202;
Will result in the Oracle optimizer transforming the query to a UNION ALL as seen below
SELECT *
FROM sales
WHERE customer_id = 101
UNION ALL
SELECT *
FROM sales
WHERE product_id = 202;
This maye result in better performance for the following reasons
The first query uses idx_customer_id, and the second uses idx_product_id. Each query can now fully leverage its respective index without merging.
If sale_date is a partition key, Oracle can independently prune partitions for each UNION ALL branch instead of scanning unnecessary partitions.
Each SELECT in the UNION ALL can run in parallel, making use of multiple CPU threads.
The resulting query plan may look like this
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 100 (100) |
| 1 | UNION ALL | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES | | | 10 (10) |
| 3 | INDEX RANGE SCAN | IDX_CUSTOMER| | | 10 (10) |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | | | 10 (10) |
| 5 | INDEX RANGE SCAN | IDX_PRODUCT | | | 10 (10) |
-----------------------------------------------------------------------------------------
Related Hints
Further Reading
You can find more information on using the hint in the documentation
Published on
Published 10th February 2025