Oracle 执行计划中的buffer sort

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]
<！-- Baidu Button BEGIN -->更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址