测试一下墨天轮5月10日索引题目 -m6米乐安卓版下载

原题

在oracle数据库中，有如下内容的一张表tab1，请问，以下创建唯一索引的语句中，哪些可以成功执行？
sql>  select * from tab1;
        id col1
---------- ----------
         1 a
         2 b
           c
           d
a、create unique index ind_tab1_id on tab1(id);
b、create unique index ind_tab1_col1_desc on tab1(col1 desc);
c、create unique index ind_tab1_id_desc on tab1(id desc);
d、create unique index ind_tab1_col1 on tab1(col1);

答案：abd

一、建表

scott@orcl> create table t(id int,name varchar2(20),card varchar2(20),pho number);
table created.
scott@orcl> insert into t values(1,'a',111111111,13111111111);
1 row created.
scott@orcl> insert into t values(2,'b',2222222222,13222222222);
1 row created.
scott@orcl> insert into t values('','c',333333333,133333333333);
1 row created.
scott@orcl> insert into t values('','d','',13444444444);
1 row created.
scott@orcl> commit;
commit complete.
scott@orcl> select * from t;
        id name                                               card                                            pho
---------- -------------------------------------------------- ---------------------------------------- ----------
         1 a                                                  111111111                                1.3111e 10
         2 b                                                  2222222222                               1.3222e 10
           c                                                  333333333                                1.3333e 11
           d                                                                                           1.3444e 10
scott@orcl> set numw 20
scott@orcl> /
                  id name                                               card                                                      pho
-------------------- -------------------------------------------------- ---------------------------------------- --------------------
                   1 a                                                  111111111                                         13111111111
                   2 b                                                  2222222222                                        13222222222
                     c                                                  333333333                                        133333333333
                     d                                                                                                    13444444444

二、dump数据块

scott@orcl> select  id,name,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid) block# from t;
                  id name                                                              file#               block#
-------------------- -------------------------------------------------- -------------------- --------------------
                   1 a                                                                     7                  350
                   2 b                                                                     7                  350
                     c                                                                     7                  350
                     d                                                                     7                  350
scott@orcl> alter system dump datafile 7 block 350;
system altered.
scott@orcl> select * from v$diag_info;
             inst_id name
-------------------- --------------------------------------------------
value
----------------------------------------------------------------------------------------------------------------------------------------------------------
              con_id
--------------------
                   1 diag enabled
true
                   0
                   1 adr base
/u01/app/oracle
                   0
                   1 adr home
/u01/app/oracle/diag/rdbms/orcl/orcl
                   0
                   1 diag trace
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
                   0
                   1 diag alert
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
                   0
                   1 diag incident
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
                   0
                   1 diag cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
                   0
                   1 health monitor
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
                   0
                   1 default trace file
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_48870.trc
                   0
                   1 active problem count
0
                   0
                   1 active incident count
0
                   0
                   1 oracle_home
/u01/app/oracle/product/19.0.0/db_1
                   0

三、数据块

block header dump:  0x01c0015e
 object id on block? y
 seg/obj: 0x12501  csc:  0x00000000005138eb  itc: 2  flg: e  typ: 1 - data
     brn: 0  bdba: 0x1c00158 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 itl           xid                  uba         flag  lck        scn/fsc
0x01   0x0003.005.0000067c  0x010001d7.01cb.08  --u-    4  fsc 0x0000.00513a98 --事物已经提交，但是锁还没有清除,影响4行
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01c0015e
data_block_dump,data header at 0x965be064
0x01c0015e--> 0001 1100 0000 0000 0001 0101 1110-->0001 1100 00  00 0000 0001 0101
===============
-- 0x01c0015e-->0000 0001 1100 0000 0000 0001 0101 1110-->(0000 0001 11)(00 0000 0000 0001 0101 1110)-->7 350
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x965be064
     76543210
flag=--------
ntab=1  --表数量
nrow=4  --行数量
frre=-1  --需要创建索引
fsbo=0x1a
fseo=0x1f3c
avsp=0x1f22
tosp=0x1f22
0xe:pti[0]      nrow=4  offs=0
0x12:pri[0]     offs=0x1f7e
0x14:pri[1]     offs=0x1f63
0x16:pri[2]     offs=0x1f4b
0x18:pri[3]     offs=0x1f3c
block_row_dump:
tab 0, row 0, @0x1f7e
tl: 26 fb: --h-fl-- lb: 0x1  cc: 4 --行数
此处代表每一行得数据，不同的字符类型转换方式不一样
col  0: [ 2]  c1 02  
col  1: [ 1]  41  --a
col  2: [ 9]  31 31 31 31 31 31 31 31 31  --111111111
col  3: [ 7]  c6 02 20 0c 0c 0c 0c  --
tab 0, row 1, @0x1f63
tl: 27 fb: --h-fl-- lb: 0x1  cc: 4
col  0: [ 2]  c1 03
col  1: [ 1]  62
col  2: [10]  32 32 32 32 32 32 32 32 32 32
col  3: [ 7]  c6 02 21 17 17 17 17
tab 0, row 2, @0x1f4b
tl: 24 fb: --h-fl-- lb: 0x1  cc: 4
col  0: *null*
col  1: [ 1]  63
col  2: [ 9]  33 33 33 33 33 33 33 33 33
col  3: [ 7]  c6 0e 22 22 22 22 22
tab 0, row 3, @0x1f3c
tl: 15 fb: --h-fl-- lb: 0x1  cc: 4
col  0: *null*
col  1: [ 1]  64
col  2: *null*
col  3: [ 7]  c6 02 23 2d 2d 2d 2d
end_of_block_dump

四、创建第一个索引

scott@orcl> alter session set events '10046 trace name context forever,level 12';
session altered.
scott@orcl> create unique index ind_t_id on t(name desc);
index created.

exec #139904316939736:c=825,e=888,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=4,plh=2683643009,tim=95981534675
close #139904316939736:c=1,e=1,dep=1,type=3,tim=95981534728
exec #139904316090312:c=87924,e=192722,p=9,cr=361,cu=91,mis=0,r=0,dep=0,og=1,plh=605356462,tim=95981534943
stat #139904316090312 id=1 cnt=1 pid=0 pos=1 obj=0 op='index build unique ind_t_id (cr=13 pr=0 pw=0 str=1 time=3914 us)'
stat #139904316090312 id=2 cnt=4 pid=1 pos=1 obj=0 op='sort create index (cr=7 pr=0 pw=0 str=1 time=63 us)'
stat #139904316090312 id=3 cnt=4 pid=2 pos=1 obj=75009 op='table access full t (cr=7 pr=0 pw=0 str=1 time=34 us cost=2 size=984 card=82)'
wait #139904316090312: nam='log file sync' ela= 2217 buffer#=2672 sync scn=5333913 p3=0 obj#=328 tim=95981537433
wait #139904316090312: nam='pga memory operation' ela= 31 p1=0 p2=0 p3=0 obj#=328 tim=95981537570
wait #139904316090312: nam='sql*net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=328 tim=95981537597
wait #139904316090312: nam='sql*net message from client' ela= 433 driver id=1650815232 #bytes=1 p3=0 obj#=328 tim=95981538046
parse #139904317414640:c=29,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=95981538117
binds #139904317414640:
 bind#0
  oacdty=123 mxl=656(656) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1400000 frm=00 csi=00 siz=680 off=0
toid ptr value=6f5b3900 length=16
86b64b666e77012ee053f706e80a06
  kxsbbbfp=7f3e0324d3d0  bln=656  avl=00  flg=15
 bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=656
  kxsbbbfp=7f3e0324d660  bln=22  avl=02  flg=01
  value=15
wait #139904317414640: nam='sql*net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=328 tim=95981538369
exec #139904317414640:c=240,e=240,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=95981538392

两个等待
log file sync、pga memory operation
有意思的等待事件，详细见
https://fritshoogland.wordpress.com/2017/03/01/oracle-12-2-wait-event-pga-memory-operation/

五、dump第一个索引

scott@orcl> select object_name,object_id from dba_objects where object_name='ind_t_id';
object_name                     object_id
------------------------------ ----------
ind_t_id                            75025
scott@orcl> alter system set events 'immediate trace name treedump level 75025';
system altered.
scott@orcl> select * from v$diag_info;

----- begin tree dump
leaf: 0x1c497eb 29661163 (0: row:4.4 avs:7944)
----- end tree dump

[oracle@19c01:/home/oracle]$vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_63035.trc
sys@orcl> select dbms_utility.data_block_address_file(29661163),dbms_utility.data_block_address_block(29661163) from dual;
dbms_utility.data_block_address_file(29661163) dbms_utility.data_block_address_block(29661163)
---------------------------------------------- -----------------------------------------------
                                             7                                          301035

object id on block? y
 seg/obj: 0x12511  csc:  0x000000000051638e  itc: 2  flg: e  typ: 2 - index
     brn: 0  bdba: 0x1c497e8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 itl           xid                  uba         flag  lck        scn/fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  c---    0  scn  0x000000000051638e
leaf block dump
===============
header address 139726569345124=0x7f14a0881064
kdxcolev 0
kdxcolev flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7988=0x1f34
kdxcoavs 7944
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 03
col 0; len 2; (2):  9b ff
row#1[8010] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 02
col 0; len 2; (2):  9c ff
row#2[7999] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 01
col 0; len 2; (2):  9d ff
row#3[7988] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 00
col 0; len 2; (2):  be ff

六、第二个索引

scott@orcl> alter system set events 'immediate trace name treedump level 75030';
system altered.
scott@orcl> select dbms_utility.data_block_address_file(29661171),dbms_utility.data_block_address_block(29661171) from dual;
dbms_utility.data_block_address_file(29661171) dbms_utility.data_block_address_block(29661171)
---------------------------------------------- -----------------------------------------------
                                             7                                          301043
scott@orcl> alter system dump datafile 7 block 301043;
system altered.
\

block header dump:  0x01c497f3
 object id on block? y
 seg/obj: 0x12516  csc:  0x0000000000517fd7  itc: 2  flg: e  typ: 2 - index
     brn: 0  bdba: 0x1c497f0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 itl           xid                  uba         flag  lck        scn/fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  c---    0  scn  0x0000000000517fd7
leaf block dump
===============
header address 140276218159204=0x7f949a276064
kdxcolev 0
kdxcolev flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=y
kdxconco 1
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8010=0x1f4a
kdxcoavs 7970
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 00
col 0; len 2; (2):  c1 02
row#1[8010] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 01
col 0; len 2; (2):  c1 03

只有两行

七、第三个索引

header address 140360327327844=0x7fa82f737064
kdxcolev 0
kdxcolev flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7988=0x1f34
kdxcoavs 7944
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 03
col 0; len 2; (2):  9b ff
row#1[8010] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 02
col 0; len 2; (2):  9c ff
row#2[7999] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 01
col 0; len 2; (2):  9d ff
row#3[7988] flag: -------, lock: 0, len=11, data:(6):  01 c0 01 5e 00 00
col 0; len 2; (2):  be ff
----- end of leaf block logical dump -----
----- end of leaf block dump -----

八、错误

scott@orcl> create unique index idx on t(id desc);
create unique index idx on t(id desc)
                             *
error at line 1:
ora-01452: cannot create unique index; duplicate keys found
exec #139858163333920:c=554,e=658,p=0,cr=2,cu=1,mis=0,r=1,dep=1,og=4,plh=0,tim=99722425398
close #139858163333920:c=1,e=0,dep=1,type=3,tim=99722425421
wait #139858163973528: nam='pga memory operation' ela= 17 p1=65536 p2=1 p3=0 obj#=-1 tim=99722425614
exec #139858163973528:c=6921,e=6776,p=0,cr=27,cu=54,mis=0,r=0,dep=0,og=1,plh=1638586637,tim=99722425699
error #139858163973528:err=1452 tim=99722425712