How does one determine whether the SQL statements are actually sorted in Oracle?

How does one determine whether the SQL statements are actually sorted in Oracle?

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 fields sorted in the returned results SQL> select projectionfrom v $ SQL _plan_statistics_allwhere SQL _id in (select SQL _id from v $ SQL wheresql_text like 'select/* + gg_test */* from test order by object_id ') and operation = 'sort 'and options = 'ORDER BY'; PROJECTION ----------------------------------------------------------------- (# keys = 1) "TEST ". "OBJECT_ID" [NUMBER, 22], "TEST ". "OWNER" [VARCHAR2, 30], "TEST ". & quot; OBJECT_NAME & quot; [VARCHAR2, 128], & quot; TEST & quot ". "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 projectionfrom v $ SQL _plan_statistics_allwhere SQL _id in (select SQL _id from v $ SQL wheresql_text = 'select/* + gg_test */* from test order by object_id, object_name ') and operation = 'sort 'and options = 'ORDER BY'; 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 projectionfrom v $ SQL _plan_statistics_allwhere SQL _id in (select SQL _id from v $ SQL wheresql_text = 'select/* + gg_test */* from test order by object_id, object_name, object_type ') and operation = 'sort 'and options = 'ORDER BY'; PROJECTION ----------------------------------------------------------------- (# keys = 3) "TEST ". "OBJECT_ID" [NUMBER, 22], "TEST ". & quot; OBJECT_NAME & quot; [VARCHAR2, 128], & quot; TEST & quot ". "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

]