oracle脚本左连接,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=>') from dual;