Buffer sort in Oracle execution plan
The buffer sort in the Oracle execution plan is not actually sorted. It only loads data to the memory and does not scan tables multiple times.
-- Manufacturing data
Drop table test purge;
Drop table test1 purge;
Create table test as select * from dba_objects where rownum <10;
Create table test1 as select * from dba_objects where rownum <10;
Create index ind_t_object_id on test (object_id );
Create index ind_t1_object_id on test1 (object_id );
Exec dbms_stats.gather_table_stats (user, 'test', cascade => true );
Exec dbms_stats.gather_table_stats (user, 'test1', cascade => true );
-- Execute SQL
Select/* + gg_test */* from test t, test1 t1 where t. object_name like't % ';
----------------------------------------------------------
Plan hash value: 702683263
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 9 | 1350 | 6 (0) | 00:00:01 |
| 1 | merge join cartesian | 9 | 1350 | 6 (0) | 00:00:01 |
| * 2 | table access full | TEST | 1 | 75 | 3 (0) | 00:00:01 |
| 3 | buffer sort | 9 | 675 | 3 (0) | 00:00:01 |
| 4 | table access full | TEST1 | 9 | 675 | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("T". "OBJECT_NAME" LIKE't % ')
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1991 bytes sent via SQL * Net to client
348 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
-- # Keys = 0 indicates no sorting. in Oracle, how can I determine whether the SQL is actually sorted?
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 t, test1 t1 where t. object_name like 't% ''')
And operation = 'buffer' and options = 'sort ';
(# Keys = 0) "T1 ". "OWNER" [VARCHAR2, 30], "T1 ". "OBJECT_NAME" [VARCHAR2, 128], "T1 ". "SUBOBJECT_NAME" [VARCHAR2, 30], "T1 ". "OBJECT_ID" [NUMBER, 22], "T1 ". "DATA_OBJECT_ID" [NUMBER, 22], "T1 ". "OBJECT_TYPE" [VARCHAR2, 19], "T1 ". "CREATED" [DATE, 7], "T1 ". "LAST_DDL_TIME" [DATE, 7], "T1 ". "TIMESTAMP" [VARCHAR2, 19], "T1 ". "STATUS" [VARCHAR2, 7], "T1 ". "TEMPORARY" [VARCHAR2, 1], "T1 ". "GENERATED" [VARCHAR2, 1], "T1 ". "SECONDARY" [VARCHAR2, 1], "T1 ". "NAMESPACE" [NUMBER, 22], "T1 ". "EDITION_NAME" [VARCHAR2, 30]