Skip to Main Content
  • Questions
  • Stats_mode function - Deterministic or Non- Deterministic

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Deepak.

Asked: January 28, 2021 - 10:15 am UTC

Last updated: July 29, 2021 - 4:09 am UTC

Version: 19c

Viewed 100+ times

You Asked

Stats_mode function - Deterministic or Non- Deterministic?

What does Oracle return when there are multiple keys with same mode (highest) and how?

and we said...

In the *current* implementation, we are doing a sort

SQL> create table t as
  2  select *
  3  from (
  4  select 'a' c1, 1 c2 from dual union all
  5  select 'b' c1, 1 c2 from dual union all
  6  select 'c' c1, 1 c2 from dual union all
  7  select 'd' c1, 1 c2 from dual union all
  8  select 'e' c1, 1 c2 from dual union all
  9  select 'a' c1, 2 c2 from dual union all
 10  select 'b' c1, 2 c2 from dual union all
 11  select 'c' c1, 2 c2 from dual union all
 12  select 'd' c1, 2 c2 from dual union all
 13  select 'e' c1, 2 c2 from dual
 14  );

Table created.

SQL>
SQL> set autotrace on explain
SQL> select stats_mode(c2) from t;

STATS_MODE(C2)
--------------
             1


Execution Plan
----------------------------------------------------------
Plan hash value: 1476560607

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| T    |    10 |    30 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


so from that sense it will be deterministic as picking the lowest value out of those with the highest count.

But as per the docs: "If more than one mode exists, then Oracle Database chooses one and returns only that one value."

so we are giving *no* guarantee that this will always be the case. We might change to a GROUP HASH BY at some stage in the future which could change things.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.