Oracle 執行計劃中的buffer sort實際上沒有排序,只是把數據加載到內存,不掃描多次表。
--制造數據 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); --執行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%') 統計信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1991 bytes sent via SQL*Net to client 348 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed --#keys=0表示沒有排序,Oracle中如何判斷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 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]