oracle左连接和相关执行计划实验

版本11.2.0.4 set linesize 170 set pagesize 200 col file_name for a60 此脚本解决tom脚本调用DBA_FREE_SPACE，无法显示已经写满的数据文件的问题。 select  a.file_id "file_id", a.file_name "file_name", (a.bytes/1048576) "curr_MB", nvl(((a.bytes-b.fbytes)/1048576),(a.bytes/1048576)) "used_MB", nvl2((b.fbytes),b.fbytes/1048576,0) "free_MB", (a.MAXBYTES/1024/1024) maxsize, a.AUTOEXTENSIBLE from dba_data_files a  left join (select file_id,sum(bytes) fbytes from DBA_FREE_SPACE group by file_id) b on a.file_id=b.file_id order by 1; ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 左连接执行计划实验 SQL> create table jt as select file_id,sum(bytes) fbytes from DBA_FREE_SPACE group by file_id; //原语句的b表 SQL> select * from jt order by 1;    FILE_ID     FBYTES ---------- ----------  1    9502720  2   33292288  3   85786624  4     786432  5   37289984  6   20905984 6 rows selected. SQL> select file_id,file_name from dba_data_files; //原语句的a表    FILE_ID FILE_NAME ---------- ------------------------------------------------------------  4 +DATA/dbm01/datafile/users.259.956474535  3 +DATA/dbm01/datafile/undotbs1.258.956474535  2 +DATA/dbm01/datafile/sysaux.257.956474533  1 +DATA/dbm01/datafile/system.256.956474533  5 +DATA/dbm01/datafile/example.264.956474661  6 +DATA/dbm01/datafile/undotbs2.265.956474955 6 rows selected. select /* +moniotr*/ a.file_id "file_id", a.file_name "file_name", b.FBYTES from dba_data_files a left join jt b on a.file_id=b.file_id order by 1; select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'d8dpu3441kvy1', report_level=>'ALL', type=>'ACTIVE',base_path=>'http://www.ly/smon') from dual;