[20131001]ctas与视图user_tables，user_objects.txt

链接：http://www.itpub.net/thread-1819520-1-1.html
SQL> show user ;
USER is "SCOTT"
SQL> purge recyclebin;

Recyclebin purged.

SQL> select table_name from user_tables where table_name = 'NO_EXISTS';

no rows selected

SQL> create table NO_EXISTS as select table_name from user_tables where table_name = 'NO_EXISTS';

Table created.

SQL> select count(*) from NO_EXISTS;
  COUNT(*)
----------
         0

SQL> drop table NO_EXISTS purge ;

Table dropped.

SQL> select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';

no rows selected

SQL> create table NO_EXISTS as select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';

Table created.

SQL> select count(*) from NO_EXISTS;

???结果是多少？？？？

放假家里没有11G，10G的环境，使用12c测试结果一样。做一些简单的探究。

SYS@test> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

select table_name from user_tables where table_name = 'NO_EXISTS';
create table NO_EXISTS as select table_name from user_tables where table_name = 'NO_EXISTS';
select count(*) from NO_EXISTS;
drop table NO_EXISTS purge;
select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';

create table NO_EXISTS as select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';
select count(*) from NO_EXISTS;

--ctas 使用user_tables建立，查询count(*)=0,而使用user_objects建立，查询count(*)=1,为什么呢?

做一个10046跟踪看看。

drop table NO_EXISTS purge;
alter session set events '10046 trace name context forever, level 12';
create table NO_EXISTS as select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';
alter session set events '10046 trace name context off';


--查询跟踪文件，过滤出dml语句:
egrep   "^insert|^update|^delete|^merge" test_ora_2412.trc >bb1.txt

UPDATE obj$
   SET obj#      = :4,
       type# = :5,
       ctime = :6,
       mtime = :7,
       stime = :8,
       status = :9,
       dataobj# = :10,
       flags = :11,
       oid$ = :12,
       spare1 = :13,
       spare2 = :14,
       spare3 = :15,
       signature = :16,
       spare7 = :17,
       spare8 = :18,
       spare9 = :19
 WHERE owner#    = :1
   AND name      = :2
   AND namespace = :3
   AND remoteowner is null
   AND linkname is null
   AND subname is null
--无需关注。 Bind#17="_NEXT_OBJECT"
 Bind#17
  acdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  acflg=10 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=23d64a16  bln=32  avl=12  flg=09
  value="_NEXT_OBJECT"
 Bind#18
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=00 fl2=1000001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=0b1e0d58  bln=22  avl=02  flg=05
  value=1

INSERT into obj$(owner#, name, namespace, obj#, type#, ctime, mtime, stime, status, remoteowner, linkname, subname, dataobj#, flags, oid$, spare1, spare2, spare3,
signature, spare7, spare8, spare9)
VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18, :19,:20,:21,:22)

INSERT into seg$ (file#, block#, type#, ts#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, lists, groups, cachehint, hwmincr, spare1,
scanhint, bitmapranges)
VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, DECODE(:17, 0, NULL,:17),:18,:19)
--如果有插入数据，应该在这里。

INSERT into tab$(obj#, ts#, file#, block#, bobj#, tab#, intcols, kernelcols, clucols, audit$, flags, pctfree$, pctused$, initrans, maxtrans, rowcnt, blkcnt,
empcnt, avgspc, chncnt, avgrln, analyzetime, samplesize, cols, property, degree, instances, dataobj#, avgspc_flb, flbcnt, trigflag, spare1, spare6)
VALUES (:1,:2,:3,:4, decode(:5, 0, null,:5), decode(:6, 0, null,:6),:7,:8, decode(:9, 0,
       null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25, decode(:26, 1, null,:26), decode(:27, 1, null,:27),:28,:29,:30,:31,:32,:33)

INSERT into col$(obj#, name, intcol#, segcol#, type#, length, precision#, scale, null$, offset, fixedstorage, segcollength, deflength, default$, col#, property,
charsetid, charsetform, spare1, spare2, spare3, evaledition#, unusablebefore#, unusablebeginning#)
VALUES (:1,:2,:3,:4,:5,:6, decode(:5, 182/*DTYIYM*/,:7, 183/*DTYIDS*/,:7, decode(:7, 0, null,:7)), decode(:5, 2, decode(:8,-127/*MAXSB1MINAL*/, null,:8),
       178,:8, 179,:8, 180,:8, 181,:8, 182,:8, 183,:8, 231,:8, null),:9, 0,:10,:11, decode(:12, 0, null,:12),:13,:14,:15,:16,:17,:18,:19,:20, decode(:21, 1,
       null,:21), decode(:22, 0, null,:22), decode(:23, 0, null,:23))

delete from superobj$
 WHERE subobj# = :1

delete from tab_stats$ where obj#=:1

UPDATE tab$
   SET ts#  = :2,
       file# = :3,
       block# = :4,
       bobj# = decode(:5,
       0,
       null,
       :5),
       tab# = decode(:6,
       0,
       null,
       :6),
       intcols = :7,
       kernelcols = :8,
       clucols = decode(:9,
       0,
       null,
       :9),
       audit$ = :10,
       flags = :11,
       pctfree$ = :12,
       pctused$ = :13,
       initrans = :14,
       maxtrans = :15,
       rowcnt = :16,
       blkcnt = :17,
       empcnt = :18,
       avgspc = :19,
       chncnt = :20,
       avgrln = :21,
       analyzetime = :22,
       samplesize = :23,
       cols = :24,
       property = :25,
       degree = decode(:26,
       1,
       null,
       :26),
       instances = decode(:27,
       1,
       null,
       :27),
       dataobj# = :28,
       avgspc_flb = :29,
       flbcnt = :30,
       trigflag = :31,
       spare1 = :32,
       spare2 = decode(:33,
       0,
       null,
       :33),
       spare4 = :34,
       spare6 = :35
 WHERE obj# = :1

UPDATE seg$
   SET type#  = :4,
       blocks = :5,
       extents = :6,
       minexts = :7,
       maxexts = :8,
       extsize = :9,
       extpct = :10,
       user# = :11,
       iniexts = :12,
       lists = decode(:13,
       65535,
       NULL,
       :13),
       groups = decode(:14,
       65535,
       NULL,
       :14),
       cachehint = :15,
       hwmincr = :16,
       spare1 = DECODE(:17,
       0,
       NULL,
       :17),
       scanhint = :18,
       bitmapranges = :19
 WHERE ts#    = :1
   AND file#  = :2
   AND block# = :3

INSERT into hist_head$(obj#, intcol#, bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, spare1,
spare2, avgcln, col#)
VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)

UPDATE obj$
   SET obj#      = :4,
       type# = :5,
       ctime = :6,
       mtime = :7,
       stime = :8,
       status = :9,
       dataobj# = :10,
       flags = :11,
       oid$ = :12,
       spare1 = :13,
       spare2 = :14,
       spare3 = :15,
       signature = :16,
       spare7 = :17,
       spare8 = :18,
       spare9 = :19
 WHERE owner#    = :1
   AND name      = :2
   AND namespace = :3
   AND remoteowner is null
   AND linkname is null
   AND subname is null

--显示的方便，做了格式化处理。从以上DML可以看出要插入数据应该在INSERT into seg$处。

现在来看看以下两个执行计划：

select table_name from user_tables where table_name = 'NO_EXISTS';

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------

SQL_ID  9m90ttfz3qrg4, child number 0

-------------------------------------

select table_name from user_tables where table_name = 'NO_EXISTS'

Plan hash value: 1599383557

-----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                   | Name           | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

-----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                            |                |        |    10 (100)|       |       |          |

|   1 |  NESTED LOOPS OUTER                         |                |      1 |    10  (10)|       |       |          |

|   2 |   NESTED LOOPS OUTER                        |                |      1 |     9  (12)|       |       |          |

|   3 |    NESTED LOOPS OUTER                       |                |      1 |     8  (13)|       |       |          |

|   4 |     NESTED LOOPS OUTER                      |                |      1 |     7  (15)|       |       |          |

|   5 |      NESTED LOOPS                           |                |      1 |     6  (17)|       |       |          |

|   6 |       NESTED LOOPS                          |                |      1 |     5  (20)|       |       |          |

|   7 |        MERGE JOIN CARTESIAN                 |                |      1 |     4  (25)|       |       |          |

|*  8 |         HASH JOIN                           |                |      1 |     1 (100)|  2293K|  2293K|  699K (0)|

|*  9 |          FIXED TABLE FULL                   | X$KSPPI        |      1 |     0   (0)|       |       |          |

|* 10 |          FIXED TABLE FULL                   | X$KSPPCV       |   3341 |     0   (0)|       |       |          |

|  11 |         BUFFER SORT                         |                |      1 |     3   (0)|  2048 |  2048 | 2048  (0)|

|* 12 |          TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$           |      1 |     3   (0)|       |       |          |

|* 13 |           INDEX RANGE SCAN                  | I_OBJ2         |      1 |     2   (0)|       |       |          |

|* 14 |        TABLE ACCESS CLUSTER                 | TAB$           |      1 |     1   (0)|       |       |          |

|* 15 |         INDEX UNIQUE SCAN                   | I_OBJ#         |      1 |     0   (0)|       |       |          |

|  16 |       TABLE ACCESS CLUSTER                  | TS$            |      1 |     1   (0)|       |       |          |

|* 17 |        INDEX UNIQUE SCAN                    | I_TS#          |      1 |     0   (0)|       |       |          |

|  18 |      TABLE ACCESS CLUSTER                   | SEG$           |      2 |     1   (0)|       |       |          |

|* 19 |       INDEX UNIQUE SCAN                     | I_FILE#_BLOCK# |      1 |     0   (0)|       |       |          |

|* 20 |     INDEX RANGE SCAN                        | I_OBJ1         |      1 |     1   (0)|       |       |          |

|* 21 |    INDEX RANGE SCAN                         | I_OBJ1         |      1 |     1   (0)|       |       |          |

|* 22 |   INDEX RANGE SCAN                          | I_USER2        |      1 |     1   (0)|       |       |          |

-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   8 - access("INDX"="INDX")

   9 - filter(("KSPPINM"='_dml_monitoring_enabled' AND INTERNAL_FUNCTION("CON_ID")))

  10 - filter(("CON_ID"=0 OR "CON_ID"=3))

  12 - filter(BITAND("O"."FLAGS",128)=0)

  13 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"='NO_EXISTS')

  14 - filter(BITAND("T"."PROPERTY",1)=0)

  15 - access("O"."OBJ#"="T"."OBJ#")

  17 - access("T"."TS#"="TS"."TS#")

  19 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#")

  20 - access("T"."BOBJ#"="CO"."OBJ#")

  21 - access("T"."DATAOBJ#"="CX"."OBJ#")

  22 - access("CX"."OWNER#"="CU"."USER#")

Note
-----
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


57 rows selected.

--可以发现执行计划有对sys.tab$的访问，而这个操作在插入seg$之后。这样查询select table_name from user_tables where table_name = 'NO_EXISTS';
--必然没有结果。

select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------

SQL_ID  0r4910r0bgp65, child number 0

-------------------------------------

select object_name from user_objects where object_type = 'TABLE' and

object_name = 'NO_EXISTS'

Plan hash value: 3093390925

------------------------------------------------------------------------------------------

| Id  | Operation                               | Name             | E-Rows | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                        |                  |        |    11 (100)|

|   1 |  VIEW                                   | USER_OBJECTS     |      2 |    11   (0)|

|   2 |   UNION-ALL                             |                  |        |            |

|*  3 |    TABLE ACCESS BY INDEX ROWID          | SUM$             |      1 |     1   (0)|

|*  4 |     INDEX UNIQUE SCAN                   | I_SUM$_1         |      1 |     0   (0)|

|*  5 |    FILTER                               |                  |        |            |

|   6 |     NESTED LOOPS                        |                  |      1 |     5   (0)|

|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$             |      1 |     4   (0)|

|*  8 |       INDEX RANGE SCAN                  | I_OBJ5           |      1 |     3   (0)|

|*  9 |      INDEX RANGE SCAN                   | I_USER2          |      1 |     1   (0)|

|* 10 |     TABLE ACCESS BY INDEX ROWID         | IND$             |      1 |     2   (0)|

|* 11 |      INDEX UNIQUE SCAN                  | I_IND1           |      1 |     1   (0)|

|* 12 |     TABLE ACCESS FULL                   | USER_EDITIONING$ |      1 |     2   (0)|

|  13 |     NESTED LOOPS SEMI                   |                  |      1 |     2   (0)|

|* 14 |      INDEX SKIP SCAN                    | I_USER2          |      1 |     1   (0)|

|* 15 |      INDEX RANGE SCAN                   | I_OBJ4           |      1 |     1   (0)|

|* 16 |     TABLE ACCESS FULL                   | USER_EDITIONING$ |      1 |     2   (0)|

|* 17 |    FILTER                               |                  |        |            |

|* 18 |     INDEX RANGE SCAN                    | I_LINK1          |      1 |     1   (0)|

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)

   4 - access("S"."OBJ#"=:B1)

   5 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1))

              AND (( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR

              BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA

              $BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND

              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT

              NULL) AND  IS NOT NULL))))

   7 - filter(BITAND("O"."FLAGS",128)=0)

   8 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."NAME"='NO_EXISTS' AND

              "O"."LINKNAME" IS NULL)

       filter(("O"."NAME"<>'_NEXT_OBJECT' AND

              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND

              DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYN

              ONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE

              BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX

              PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA

              SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE

              SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB

              SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE

              SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XM

              L SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUA

              TION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER

              GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING

              MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE

              FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION',114,'SQL

              TRANSLATION PROFILE',115,'UNIFIED AUDIT POLICY','UNDEFINED')='TABLE'))

   9 - access("O"."OWNER#"="U"."USER#")

  10 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4

              OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9))

  11 - access("I"."OBJ#"=:B1)

  12 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))

  14 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cu

              rrent_edition_id')))

       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','c

              urrent_edition_id'))))

  15 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND

              "O2"."OWNER#"="U2"."USER#")

  16 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))

  17 - filter(NULL IS NOT NULL)

  18 - access("L"."OWNER#"=USERENV('SCHEMAID') AND "L"."NAME"='NO_EXISTS')

Note
-----
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


83 rows selected.

--执行计划很复杂，但是没有对sys.tab$的访问。而对sys.obj$的插入已经完成，这样查询
--select object_name from user_objects where object_type = 'TABLE' and object_name = 'NO_EXISTS';
--正式这个差异导致ctas user_objects后，select count(*) from NO_EXISTS;的结果是1.而不是0.