How To Improve Case Insensitive Searches in 19c for Select Distinct Queries (Doc ID 2662693.1)

Last updated on JANUARY 15, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 19.1.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Slow query performance with sort unique with case insensitive searches in 19c for select distinct queries:
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1185K(100)| 4 |00:00:14.50 | 2125K| 2070K| | | |
| 1 | SORT UNIQUE | | 1 | 57M| 1185K (1)| 4 |00:00:14.50 | 2125K| 2070K| 2048 | 2048 | 1/0/0|
| 2 | TABLE ACCESS FULL| PARTICIPANT | 1 | 57M| 563K (1)| 78M|00:00:06.95 | 2125K| 2070K| | | |
---------------------------------------------------------------------------------------------------------------------------------------------

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

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