In Oracle, how does one determine whether SQL is actually sorted?

In Oracle, how does one determine whether SQL is actually sorted?

In Oracle, sometimes when you see the sort keyword in the execution plan, some people think that the sorting is actually not necessarily. You can experiment with the following method:

Drop table test purge;

Create table test as select * from dba_objects;
Exec dbms_stats.gather_table_stats (user, 'test ');

Select/* + gg_test */* from test order by object_id;
Select/* + gg_test */* from test order by object_id, object_name;
Select/* + gg_test */* from test order by object_id, object_name, object_type;

-- # Keys indicates the number of sorted fields in the returned results
SQL> select projection
From v $ SQL _plan_statistics_all
Where SQL _id in (select SQL _id from v $ SQL where
SQL _text like 'select/* + gg_test */* from test order by object_id ')
And operation = 'sort 'and options = 'ORDER ';
PROJECTION
-----------------------------------------------------------------------
(# Keys = 1) "TEST ". "OBJECT_ID" [NUMBER, 22], "TEST ". "OWNER" [VARCHAR2, 30], "TEST ". "OBJECT_NAME" [varchar 2, 128],
"TEST". "SUBOBJECT_NAME" [VARCHAR2, 30], "TEST". "EDITION_NAME" [VARCHAR2, 30],
"TEST". "DATA_OBJECT_ID" [NUMBER, 22], "TEST". "OBJECT_TYPE" [VARCHAR2, 19], "TEST". "CREATED" [DATE, 7],
"TEST". "LAST_DDL_TIME" [DATE, 7], "TEST". "TIMESTAMP" [VARCHAR2, 19], "TEST". "STATUS" [VARCHAR2, 7],
"TEST". "TEMPORARY" [VARCHAR2, 1], "TEST". "GENERATED" [VARCHAR2, 1], "TEST". "SECONDARY" [VARCHAR2, 1],
"TEST". "NAMESPACE" [NUMBER, 22]

SQL> select projection
From v $ SQL _plan_statistics_all
Where SQL _id in (select SQL _id from v $ SQL where
SQL _text = 'select/* + gg_test */* from test order by object_id, object_name ')
And operation = 'sort 'and options = 'ORDER ';
PROJECTION
-----------------------------------------------------------------------
(# Keys = 2) "TEST ". "OBJECT_ID" [NUMBER, 22], "TEST ". & quot; OBJECT_NAME & quot; [VARCHAR2, 128], & quot; TEST & quot ". "OWNER" [VARCHAR2, 30],
"TEST". "NAMESPACE" [NUMBER, 22], "TEST". "SUBOBJECT_NAME" [VARCHAR2, 30], "TEST". "EDITION_NAME" [VARCHAR2, 30],
"TEST". "DATA_OBJECT_ID" [NUMBER, 22], "TEST". "OBJECT_TYPE" [VARCHAR2, 19], "TEST". "CREATED" [DATE, 7],
"TEST". "LAST_DDL_TIME" [DATE, 7], "TEST". "TIMESTAMP" [VARCHAR2, 19], "TEST". "STATUS" [VARCHAR2, 7],
"TEST". "TEMPORARY" [VARCHAR2, 1], "TEST". "GENERATED" [VARCHAR2, 1], "TEST". "SECONDARY" [VARCHAR2, 1]

SQL> select projection
From v $ SQL _plan_statistics_all
Where SQL _id in (select SQL _id from v $ SQL where
SQL _text = 'select/* + gg_test */* from test order by object_id, object_name, object_type ')
And operation = 'sort 'and options = 'ORDER ';
PROJECTION
-----------------------------------------------------------------------
(# Keys = 3) "TEST". "OBJECT_ID" [NUMBER, 22], "TEST". "OBJECT_NAME" [VARCHAR2, 128],
"TEST". "OBJECT_TYPE" [VARCHAR2, 19], "TEST". "OWNER" [VARCHAR2, 30], "TEST". "NAMESPACE" [NUMBER, 22],
"TEST". "SUBOBJECT_NAME" [VARCHAR2, 30], "TEST". "EDITION_NAME" [VARCHAR2, 30],
"TEST". "DATA_OBJECT_ID" [NUMBER, 22], "TEST". "SECONDARY" [VARCHAR2, 1], "TEST". "CREATED" [DATE, 7],
"TEST". "LAST_DDL_TIME" [DATE, 7], "TEST". "TIMESTAMP" [VARCHAR2, 19], "TEST". "STATUS" [VARCHAR2, 7],
"TEST". "TEMPORARY" [VARCHAR2, 1], "TEST". "GENERATED" [VARCHAR2, 1]