[20131001]ctas與檢視user_tables，user_objects.txt
[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';
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';
--必然沒有結果。
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.
連結：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 NOTNULL) 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,'SYNONYM',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#"=4OR "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','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-773846/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- null ctasNull
- 【oracle】user_tablesOracle
- Django：路由與檢視Django路由
- Dictionary inconsistency caused by CTAS in PARALLELParallel
- CTAS的一些特性
- iOS10 UI教程檢視的繪製與檢視控制器和檢視iOSUI
- MySQL檢視簡介與操作MySql
- docker 容器的使用與檢視Docker
- 檢視：重繪與 UIScrollViewUIView
- XPM轉換與檢視工具
- Flutter檢視的Layout與PaintFlutterAI
- 【asp.net core 系列】3 檢視以及檢視與控制器ASP.NET
- iOS10 UI教程檢視的邊界與檢視的框架iOSUI框架
- 物化檢視的快速重新整理測試與物化檢視日誌
- oracle 統計資訊檢視與收集Oracle
- ●linux程式的檢視與操作●Linux
- oracle 字符集檢視與修改Oracle
- 6、mysql與suse檢視版本資訊MySql
- oracle資料字典表與檢視Oracle
- MySQL資料庫檢視：檢視定義、建立檢視、修改檢視MySql資料庫
- 【檢視】SMON_SCN_TIME檢視--SCN與時間的對應關係
- [Django REST framework - 檢視元件之檢視基類、檢視擴充套件類、檢視子類、檢視集]DjangoRESTFramework元件套件
- flutter: 根檢視、根元素與根渲染Flutter
- MySQL--儲存過程與檢視MySql儲存過程
- Spark2 Dataset之檢視與SQLSparkSQL
- 檢視ubuntu版本與linux核心版本UbuntuLinux
- mysql字符集檢視與設定MySql
- Django教程（一） Django檢視與網址Django
- linux檢視組與使用者Linux
- EXPDP/IMPDP任務的檢視與管理
- exp / imp 任務的檢視與管理
- LINUX SWAP的檢視與維護Linux
- 資料庫的查詢與檢視資料庫
- 微信小程式-檢視檢視引用微信小程式
- ASP.NET Core 5.0 MVC中的檢視分類——佈局檢視、啟動檢視、具體檢視、分部檢視ASP.NETMVC
- 淺析 Flutter 與 iOS 的檢視橋樑FlutteriOS
- MySQL 檢視與修改預設字符集MySql
- Linux 字元編碼 檢視與轉換Linux字元