其他先不说,先来了解下视图的结构,通过DBMS_METADATA.GET_DDL来获取该视图的DDL语句

DBA_DATA_FILES:

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "FILE_ID", "TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO", "AUTOEXTENSIBLE", "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BYTES", "USER_BLOCKS", "ONLINE_STATUS") AS
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#;

COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."FILE_NAME" IS 'Name of the database data file';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."FILE_ID" IS 'ID of the database data file';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."TABLESPACE_NAME" IS 'Name of the tablespace to which the file belongs';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."BYTES" IS 'Size of the file in bytes';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."BLOCKS" IS 'Size of the file in ORACLE blocks';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."STATUS" IS 'File status: "INVALID" or "AVAILABLE"';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."RELATIVE_FNO" IS 'Tablespace-relative file number';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."AUTOEXTENSIBLE" IS 'Autoextensible indicator: "YES" or "NO"';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."MAXBYTES" IS 'Maximum size of the file in bytes';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."MAXBLOCKS" IS 'Maximum size of the file in ORACLE blocks';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."INCREMENT_BY" IS 'Default increment for autoextension';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."USER_BYTES" IS 'Size of the useful portion of file in bytes';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."USER_BLOCKS" IS 'Size of the useful portion of file in ORACLE blocks';
COMMENT ON COLUMN "SYS"."DBA_DATA_FILES"."ONLINE_STATUS" IS 'Online status of the file';
COMMENT ON TABLE "SYS"."DBA_DATA_FILES" IS 'Information about database data files';
GRANT SELECT ON "SYS"."DBA_DATA_FILES" TO "SELECT_CATALOG_ROLE";
GRANT SELECT ON "SYS"."DBA_DATA_FILES" TO "APEX_030200";
GRANT SELECT ON "SYS"."DBA_DATA_FILES" TO "OWBSYS";
该解释还是很详细的,包括创建语句,及创建后的一系列操作,从上面可以看到该视图调用了5张表获取数据:

这里我首先认识到x$开头的表均为数据库的固定表

x$kccfn kernel cache control data file name 内核缓存控制层的数据文件名

x$kccfe kernel cache control  date file entries ( from control file ) 内核缓存控制数据文件

x$ktfbhc kernel transaction file bitmap header control 内核事务文件位图控制头

先来看看这些表的结构

Fixed View: X$KCCFN

Versions

Columns

VERSION 6

Indexes

VERSION 6

Version 6 of this fixed view has one index

对DBA_DATA_FILES执行查询计划

可以看到执行计划顺序分两块分别如下

第一块

1、对FILE$唯一索引进行一次检索并与X$KCCFN表扫的结果嵌套.

2、对X$KCCFE非唯一索引检索得到的结果,与第一步进行嵌套.

3、对TS$唯一索引进行检索与上面步奏得到的结果进行嵌套.

第二块

1、对X$KCCFN进行表扫,同时将X$KTFBHC表非唯一索引扫得的结果与之嵌套.同时与TS$表唯一索引I_TS#扫的结果嵌套.

2、将FILE$表唯一性索引I_FILE1检索的结果,与前一步进行嵌套

3、最后将X$KCCFE表非唯一索引检索得出的结果与第二步得出结果嵌套.

最终将第一块得到的结果与第二块得到的结果集合并就得到了DBA_DATA_FILES的结果.刚开始对执行计划为什么会分成两块来执行不是很理解,后来再看了视图创建语句之后,就很容易理解了.