Provides a more efficient implementation of the sort algorithm for DISTINCT statements
This operation is implemented in the following versions
|
This operation replaces the SORT(UNIQUE) operation in limited circumstances
This example was developed using Oracle 10.2.0.1 on Linux
This example requires the following table definitions
CREATE TABLE t1 (c1 NUMBER);
The statement
SELECT DISTINCT (c1) FROM t1;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH (UNIQUE) 2 1 TABLE ACCESS (FULL) OF 'T1'
If the "_gby_hash_aggregation_enabled" parameter is set to FALSE (default TRUE) then the HASH (UNIQUE) operation is disabled. For example
ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE;
Now the statement
SELECT DISTINCT (c1) FROM t1;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (UNIQUE) 2 1 TABLE ACCESS (FULL) OF 'T1'