TKPROF: Release 10.2.0.5.0 - Production on Mon Sep 24 23:19:28 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. Trace file: msctest_ora_9264_MYSQLTRACE.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** error connecting to database using: msctest_ora_9264_MYSQLTRACE.trc ORA-01017: invalid username/password; logon denied EXPLAIN PLAN option disabled. ******************************************************************************** select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 4 0 Execute 16 0.01 0.13 0 0 0 0 Fetch 81 0.00 0.00 0 208 0 65 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 0.01 0.14 0 208 4 65 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 17 SORT ORDER BY (cr=29 pr=0 pw=0 time=781 us) 17 HASH JOIN OUTER (cr=29 pr=0 pw=0 time=834 us) 17 NESTED LOOPS OUTER (cr=25 pr=0 pw=0 time=323 us) 17 TABLE ACCESS CLUSTER IND$ (cr=23 pr=0 pw=0 time=129 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=16 us)(object id 3) 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=2 pr=0 pw=0 time=134 us) 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=2 pr=0 pw=0 time=65 us)(object id 712) 2 VIEW (cr=4 pr=0 pw=0 time=72 us) 2 SORT GROUP BY (cr=4 pr=0 pw=0 time=66 us) 2 TABLE ACCESS CLUSTER CDEF$ (cr=4 pr=0 pw=0 time=25 us) 1 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=9 us)(object id 30) ******************************************************************************** select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 8 0 Execute 196 0.00 0.48 0 0 0 0 Fetch 196 0.00 0.00 0 776 0 188 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 396 0.00 0.48 0 776 8 188 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=4 pr=0 pw=0 time=38 us) 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=3 pr=0 pw=0 time=23 us)(object id 257) ******************************************************************************** select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 2 0 Execute 223 0.00 0.00 0 0 0 0 Fetch 222 0.00 0.01 0 666 0 3766 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 447 0.01 0.01 0 666 2 3766 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 20 SORT ORDER BY (cr=3 pr=0 pw=0 time=96 us) 20 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=58 us) 1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=10 us)(object id 252) ******************************************************************************** select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 4 0 Execute 65 0.00 0.00 0 0 0 0 Fetch 177 0.00 0.00 0 354 0 112 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 246 0.00 0.00 0 354 4 112 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 5 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=12 pr=0 pw=0 time=23 us) 5 INDEX RANGE SCAN I_ICOL1 (cr=7 pr=0 pw=0 time=33 us)(object id 40) ******************************************************************************** select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2, nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182, scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$, rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2, nvl(spare3,0) from col$ where obj#=:1 order by intcol# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 2 0 Execute 18 0.00 0.00 0 0 0 0 Fetch 1046 0.00 0.00 0 157 0 1028 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1068 0.01 0.01 0 157 2 1028 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 162 SORT ORDER BY (cr=6 pr=0 pw=0 time=586 us) 162 TABLE ACCESS CLUSTER COL$ (cr=6 pr=0 pw=0 time=340 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=9 us)(object id 3) ******************************************************************************** select text from view$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 4 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=9 us) ******************************************************************************** select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 8 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 7 0.00 0.00 0 23 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 18 0.00 0.00 0 23 8 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=3 pr=0 pw=0 time=42 us) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=3 pr=0 pw=0 time=30 us) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=3 pr=0 pw=0 time=24 us)(object id 103) ******************************************************************************** select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 8 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 118 0.00 0.00 0 40 0 111 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 129 0.00 0.00 0 40 8 111 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 23 SORT GROUP BY (cr=6 pr=0 pw=0 time=145 us) 23 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=6 pr=0 pw=0 time=183 us) 23 INDEX RANGE SCAN I_OBJAUTH1 (cr=3 pr=0 pw=0 time=151 us)(object id 103) ******************************************************************************** select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 8 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 129 0.00 0.00 0 260 0 118 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 144 0.00 0.00 0 260 8 118 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=6 pr=0 pw=0 time=21 us) 2 INDEX RANGE SCAN I_CDEF3 (cr=4 pr=0 pw=0 time=15 us)(object id 52) ******************************************************************************** select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0), rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 8 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 222 0.00 0.00 0 247 0 211 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 237 0.00 0.00 0 247 8 211 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 44 TABLE ACCESS CLUSTER CDEF$ (cr=48 pr=0 pw=0 time=20 us) 1 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 30) ******************************************************************************** select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 10 0 Execute 211 0.00 0.00 0 0 0 0 Fetch 442 0.00 0.00 0 884 0 231 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 656 0.00 0.01 0 884 10 231 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=0 pw=0 time=20 us) 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=0 pw=0 time=17 us)(object id 54) ******************************************************************************** select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 0 0 0 Fetch 6 0.00 0.00 0 24 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 15 0.00 0.00 0 24 0 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=0 pw=0 time=24 us) 1 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=14 us)(object id 37) ******************************************************************************** select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 8 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 13 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 13 8 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=5 pr=0 pw=0 time=44 us) 0 TABLE ACCESS CLUSTER COLTYPE$ (cr=5 pr=0 pw=0 time=36 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 3) ******************************************************************************** select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 8 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 13 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 13 8 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=5 pr=0 pw=0 time=29 us) 0 TABLE ACCESS CLUSTER SUBCOLTYPE$ (cr=5 pr=0 pw=0 time=19 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 3) ******************************************************************************** select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 8 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 3 8 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID NTAB$ (cr=1 pr=0 pw=0 time=16 us) 0 INDEX RANGE SCAN I_NTAB2 (cr=1 pr=0 pw=0 time=11 us)(object id 200) ******************************************************************************** select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 6 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 13 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10 0.00 0.00 0 13 6 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=5 pr=0 pw=0 time=28 us) 0 TABLE ACCESS CLUSTER LOB$ (cr=5 pr=0 pw=0 time=20 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us)(object id 3) ******************************************************************************** select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 8 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 3 8 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID REFCON$ (cr=1 pr=0 pw=0 time=14 us) 0 INDEX RANGE SCAN I_REFCON2 (cr=1 pr=0 pw=0 time=10 us)(object id 204) ******************************************************************************** select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 8 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 363 0.00 0.00 0 13 0 360 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 369 0.00 0.00 0 13 8 360 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 162 SORT ORDER BY (cr=5 pr=0 pw=0 time=446 us) 162 TABLE ACCESS CLUSTER COL$ (cr=5 pr=0 pw=0 time=337 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=8 us)(object id 3) ******************************************************************************** select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtype$ where obj# = :1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 8 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 3 8 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID OPQTYPE$ (cr=1 pr=0 pw=0 time=16 us) 0 INDEX RANGE SCAN I_OPQTYPE1 (cr=1 pr=0 pw=0 time=10 us)(object id 206) ******************************************************************************** select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname, o.dataobj#,o.flags from obj$ o where o.obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 0 0 0 Fetch 6 0.00 0.00 0 18 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 16 0.00 0.00 0 18 0 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=19 us) 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=10 us)(object id 36) ******************************************************************************** select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts, NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0), NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 4 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 9 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 9 4 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=37 us) 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=26 us)(object id 9) ******************************************************************************** select condition from cdef$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 10 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 4 10 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=10 us) ******************************************************************************** explain plan for(SELECT 'Full Import on Terminal' move_code, eqpa_container_type, Count(eqpa_container) cont, SUM(Nvl(Decode(eqpa_container_size, '20', 1, '40', 2), 0)) teus FROM mtu_2009.id_equipment_all_moves WHERE eqpa_company = 'MTU' AND eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012' AND eqpa_move_code = 'DSFULL' AND ( eqpa_company, eqpa_container, eqpa_serial_no ) IN (SELECT a.eqpa_company, a.eqpa_container, Max(a.eqpa_serial_no) FROM mtu_2009.iv_equipment_all_moves_gv a, mtu_2009.id_equipment_control WHERE a.eqpa_company = 'MTU' AND a.eqpa_company = eqip_company AND a.eqpa_container = eqpa_container AND a.eqpa_container_type = eqpa_container_type AND a.eqpa_move_date <= SYSDATE AND eqpa_soc = 'L' GROUP BY a.eqpa_company, a.eqpa_container) GROUP BY eqpa_container_type) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.05 0.05 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.05 0.05 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us) 0 HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us) 0 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us) 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN EQUIPMENT_CONTROL_KEY (cr=0 pr=0 pw=0 time=0 us)(object id 52984) 0 TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols, nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans, t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln, t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1), nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0), nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 2 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 8 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 8 2 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE JOIN OUTER (cr=4 pr=0 pw=0 time=51 us) 1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=19 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=8 us)(object id 3) 0 BUFFER SORT (cr=1 pr=0 pw=0 time=18 us) 0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=11 us) 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=5 us)(object id 710) ******************************************************************************** insert into plan_table (statement_id, timestamp, operation, options, object_node, object_owner, object_name, object_instance, object_type, search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21, :22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 9 0.00 0.00 0 7 15 9 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10 0.00 0.00 0 7 15 9 Misses in library cache during parse: 1 Misses in library cache during execute: 5 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 1 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 0 1 1 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SEQUENCE ORA_PLAN_ID_SEQ$ (cr=53 pr=0 pw=0 time=4627 us) 1 FAST DUAL (cr=0 pr=0 pw=0 time=5 us) ******************************************************************************** select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$, flags from seq$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 4 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID SEQ$ (cr=4 pr=0 pw=0 time=30 us) 2 INDEX UNIQUE SCAN I_SEQ1 (cr=2 pr=0 pw=0 time=15 us)(object id 102) ******************************************************************************** update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 2 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=138 us) 1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=45 us)(object id 102) ******************************************************************************** ALTER SESSION SET sql_trace = false call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 0 Misses in library cache during parse: 2 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** ALTER SESSION SET sql_trace = true call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** ALTER SESSION SET tracefile_identifier = mysqltrace call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Parsing user id: SYS ******************************************************************************** SELECT 'Full Import on Terminal' move_code, eqpa_container_type, Count(eqpa_container) cont, SUM(Nvl(Decode(eqpa_container_size, '20', 1, '40', 2), 0)) teus FROM mtu_2009.id_equipment_all_moves WHERE eqpa_company = 'MTU' AND eqpa_move_date BETWEEN '01-JAN-2009' AND '23-SEP-2012' AND eqpa_move_code = 'DSFULL' AND ( eqpa_company, eqpa_container, eqpa_serial_no ) IN (SELECT a.eqpa_company, a.eqpa_container, Max(a.eqpa_serial_no) FROM mtu_2009.iv_equipment_all_moves_gv a, mtu_2009.id_equipment_control WHERE a.eqpa_company = 'MTU' AND a.eqpa_company = eqip_company AND a.eqpa_container = eqpa_container AND a.eqpa_container_type = eqpa_container_type AND a.eqpa_move_date <= SYSDATE AND eqpa_soc = 'L' GROUP BY a.eqpa_company, a.eqpa_container) GROUP BY eqpa_container_type call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 6.45 78.30 222550 234699 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 6.48 78.33 222550 234699 0 11 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 11 HASH GROUP BY (cr=234699 pr=222550 pw=13062 time=78300501 us) 10989 HASH JOIN RIGHT SEMI (cr=234699 pr=222550 pw=13062 time=78282826 us) 489572 VIEW VW_NSO_1 (cr=117350 pr=122633 pw=10073 time=71520730 us) 489572 HASH GROUP BY (cr=117350 pr=122633 pw=10073 time=68583298 us) 2372718 NESTED LOOPS (cr=117350 pr=112560 pw=0 time=43016920 us) 1 INDEX UNIQUE SCAN EQUIPMENT_CONTROL_KEY (cr=1 pr=0 pw=0 time=15 us)(object id 52984) 2372718 TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=117349 pr=112560 pw=0 time=38271465 us) 274978 TABLE ACCESS FULL ID_EQUIPMENT_ALL_MOVES (cr=117349 pr=93526 pw=0 time=1375128 us) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.08 0.09 0 0 0 0 Execute 6 0.00 0.00 0 0 0 0 Fetch 2 6.45 78.30 222550 234699 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 6.54 78.39 222550 234699 0 11 Misses in library cache during parse: 4 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 78 0.02 0.02 0 0 132 0 Execute 820 0.08 0.67 0 8 17 10 Fetch 3046 0.04 0.04 0 3747 1 6215 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3944 0.15 0.74 0 3755 150 6225 Misses in library cache during parse: 27 Misses in library cache during execute: 30 6 user SQL statements in session. 811 internal SQL statements in session. 817 SQL statements in session. 0 statements EXPLAINed in this session. ******************************************************************************** Trace file: msctest_ora_9264_MYSQLTRACE.trc Trace file compatibility: 10.01.00 Sort options: default 0 session in tracefile. 6 user SQL statements in trace file. 811 internal SQL statements in trace file. 817 SQL statements in trace file. 32 unique SQL statements in trace file. 8704 lines in trace file. 210 elapsed seconds in trace file. -------------------- scripts---------------------------------------------------- CREATE TABLE id_equipment_all_moves ( eqpa_company VARCHAR2(10) NOT NULL, eqpa_container VARCHAR2(15) NOT NULL, eqpa_serial_no NUMBER NOT NULL, eqpa_move_date DATE NOT NULL, eqpa_move_code VARCHAR2(10) NOT NULL, eqpa_service_type VARCHAR2(10) NOT NULL, eqpa_container_service_type VARCHAR2(10), eqpa_container_type VARCHAR2(10) NOT NULL, eqpa_container_size VARCHAR2(2) NOT NULL, eqpa_container_storage VARCHAR2(10) NOT NULL, eqpa_mode VARCHAR2(1) DEFAULT 'S' NOT NULL, eqpa_current_location VARCHAR2(5) NOT NULL, eqpa_current_status VARCHAR2(1) DEFAULT 'C' NOT NULL, eqpa_gross_weight NUMBER DEFAULT 0 NOT NULL, eqpa_tare_weight NUMBER DEFAULT 0 NOT NULL, eqpa_net_weight NUMBER DEFAULT 0 NOT NULL, eqpa_cargo_weight NUMBER DEFAULT 0 NOT NULL, eqpa_days NUMBER DEFAULT 0 NOT NULL, eqpa_created_by VARCHAR2(10) NOT NULL, eqpa_created_on DATE DEFAULT sysdate NOT NULL, eqpa_modified_by VARCHAR2(10), eqpa_modified_on DATE, eqpa_line VARCHAR2(10), eqpa_vessel VARCHAR2(10), eqpa_voyage VARCHAR2(10), eqpa_port VARCHAR2(5), eqpa_mother_line VARCHAR2(10), eqpa_mother_vessel VARCHAR2(10), eqpa_mother_voyage VARCHAR2(10), eqpa_mother_port VARCHAR2(5), eqpa_origin_port VARCHAR2(5), eqpa_origin_name VARCHAR2(50), eqpa_from_port VARCHAR2(5), eqpa_to_port VARCHAR2(5), eqpa_final_port VARCHAR2(5), eqpa_final_name VARCHAR2(50), eqpa_truck_number VARCHAR2(25), eqpa_transporter VARCHAR2(50), eqpa_customer VARCHAR2(10), eqpa_seal_number VARCHAR2(25), eqpa_bill_no VARCHAR2(50), eqpa_bl_number VARCHAR2(25), eqpa_do_number VARCHAR2(25), eqpa_remarks VARCHAR2(240), eqpa_out_gauge VARCHAR2(1) DEFAULT 'N', eqpa_out_height NUMBER, eqpa_out_width NUMBER, eqpa_out_length NUMBER, eqpa_reefer_status VARCHAR2(1) DEFAULT 'N', eqpa_min_temprature NUMBER, eqpa_max_temprature NUMBER, eqpa_imco_status VARCHAR2(1) DEFAULT 'N', eqpa_imco_unno VARCHAR2(10), eqpa_imco_class VARCHAR2(10), eqpa_imco_page VARCHAR2(10), eqpa_imco_flash VARCHAR2(10), eqpa_food_status VARCHAR2(1) DEFAULT 'N', eqpa_food_description VARCHAR2(240), eqpa_booking_type VARCHAR2(10), eqpa_booking_number VARCHAR2(25), eqpa_job_type VARCHAR2(10), eqpa_job_number VARCHAR2(25), eqpa_job_basis VARCHAR2(50), eqpa_amount NUMBER DEFAULT 0, eqpa_train_number VARCHAR2(25), eqpa_train_reference VARCHAR2(50), eqpa_account_status VARCHAR2(1) DEFAULT 'X', eqpa_booking_status VARCHAR2(1) DEFAULT 'X', eqpa_billing_status VARCHAR2(1) DEFAULT 'X', eqpa_flex_1 VARCHAR2(240), eqpa_flex_2 VARCHAR2(240), eqpa_flex_3 VARCHAR2(240), eqpa_flex_4 VARCHAR2(240), eqpa_flex_5 VARCHAR2(240) DEFAULT 'N', eqpa_entry_type VARCHAR2(1) DEFAULT 'D' NOT NULL, eqpa_stowage VARCHAR2(10), eqpa_commodity VARCHAR2(10), eqpa_sequence NUMBER, eqpa_soc VARCHAR2(1) DEFAULT 'L', eqpa_voyage_service VARCHAR2(10), eqpa_tsp_port_1 VARCHAR2(5), eqpa_tsp_port_2 VARCHAR2(5), eqpa_tsp_port_3 VARCHAR2(5), eqpa_lease_number VARCHAR2(25), eqpa_lease_type VARCHAR2(10), eqpa_out_width_left NUMBER, eqpa_out_width_right NUMBER, eqpa_out_length_fore NUMBER, eqpa_out_length_back NUMBER, eqpa_ccm_gen_status VARCHAR2(1) DEFAULT 'N' NOT NULL, eqpa_ccm_file_name VARCHAR2(50), eqpa_ccm_on DATE, eqpa_tsp_status VARCHAR2(1) DEFAULT 'N', eqpa_batch VARCHAR2(25), eqpa_filter_0 VARCHAR2(240), eqpa_filter_1 VARCHAR2(240), eqpa_filter_2 VARCHAR2(240), eqpa_filter_3 VARCHAR2(240), eqpa_filter_4 VARCHAR2(240), eqpa_filter_5 VARCHAR2(240), eqpa_filter_6 VARCHAR2(240), eqpa_filter_7 VARCHAR2(240), eqpa_filter_8 VARCHAR2(240), eqpa_filter_9 VARCHAR2(240), eqpa_commission_status VARCHAR2(1) DEFAULT 'N', eqpa_slot_operator VARCHAR2(10), eqpa_terminal VARCHAR2(10), eqpa_haulage VARCHAR2(1), eqpa_from_sub_location VARCHAR2(10), eqpa_to_sub_location VARCHAR2(10), eqpa_barge_voyage VARCHAR2(10), eqpa_barge_name VARCHAR2(100), eqpa_current_loc_depot VARCHAR2(10), eqpa_quality_status VARCHAR2(1), eqpa_dest_location VARCHAR2(10), eqpa_dest_depot VARCHAR2(10), eqpa_demage_reason VARCHAR2(2000), eqpa_demage_remarks VARCHAR2(2000), eqpa_repair_reference VARCHAR2(240), eqpa_invoice_to VARCHAR2(10), eqpa_tsp_port_4 VARCHAR2(10), eqpa_tsp_port_5 VARCHAR2(10), eqpa_reefer_temp_basis VARCHAR2(1), eqpa_orgin_depot VARCHAR2(10), eqpa_orgin_location VARCHAR2(10), eqpa_origin_loc_name VARCHAR2(100), eqpa_orgin_zip VARCHAR2(50), eqpa_dest_loc_name VARCHAR2(100), eqpa_dest_loc_zip VARCHAR2(50), eqpa_feeder_voyage VARCHAR2(10), eqpa_feeder_vessel VARCHAR2(10), eqpa_dest_terminal VARCHAR2(10), eqpa_scac_code VARCHAR2(10), eqpa_pre_booking_number VARCHAR2(25), eqpa_lod_dis_lot_number NUMBER, eqpa_line_reference VARCHAR2(240), eqpa_other_reference VARCHAR2(240), eqpa_contract_no VARCHAR2(240), eqpa_dest_type VARCHAR2(1), eqpa_to_loc VARCHAR2(5), eqpa_to_loc_name VARCHAR2(75), eqpa_program_id VARCHAR2(50), eqpa_seal_type_1 VARCHAR2(1) DEFAULT 'N', eqpa_seal_type_2 VARCHAR2(1) DEFAULT 'N', eqpa_seal_type_3 VARCHAR2(1) DEFAULT 'N', eqpa_seal_number_2 VARCHAR2(25), eqpa_seal_number_3 VARCHAR2(25), eqpa_flex_6 VARCHAR2(240), eqpa_flex_7 VARCHAR2(240), eqpa_flex_8 VARCHAR2(240), eqpa_flex_9 VARCHAR2(240), eqpa_flex_10 VARCHAR2(240), eqpa_iso_code VARCHAR2(10), eqpa_pay_load NUMBER, eqpa_local_gen_status VARCHAR2(1), eqpa_local_file_on DATE, eqpa_local_file_name VARCHAR2(50), eqpa_cont_dep_doc_type VARCHAR2(10), eqpa_cont_dep_doc_number VARCHAR2(50), eqpa_seal_type VARCHAR2(1), eqpa_open_bal_remarks VARCHAR2(2000), eqpa_tsp_comm VARCHAR2(1) ) -- Indexes for ID_EQUIPMENT_ALL_MOVES CREATE INDEX cont_edi_idx ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_ccm_gen_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_bill_idx ON id_equipment_all_moves (eqpa_company, eqpa_bill_no, eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_cont_status ON id_equipment_all_moves (eqpa_company, eqpa_container, eqpa_current_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_created_date_key ON id_equipment_all_moves (eqpa_company, eqpa_created_on) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_current_cont_move_dt ON id_equipment_all_moves (eqpa_company, eqpa_current_status, eqpa_container, eqpa_move_date, eqpa_move_code, eqpa_current_location) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_current_status ON id_equipment_all_moves (eqpa_company, eqpa_current_status) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_line_key ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_vessel, eqpa_voyage, eqpa_port) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX eqpa_move_date_idx ON id_equipment_all_moves (eqpa_company, eqpa_move_date) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX equipment_all_moves_ind ON id_equipment_all_moves (eqpa_company, eqpa_customer) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE UNIQUE INDEX equipment_unique_key ON id_equipment_all_moves (eqpa_company, eqpa_container, eqpa_move_date, eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX euipment_all_moves_ind ON id_equipment_all_moves (eqpa_container_type) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX euipment_all_moves_ind2 ON id_equipment_all_moves (eqpa_container_service_type) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX euipment_all_moves_ind3 ON id_equipment_all_moves (eqpa_line) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX euipment_all_moves_ind4 ON id_equipment_all_moves (eqpa_vessel) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX euipment_all_moves_ind5 ON id_equipment_all_moves (eqpa_move_code) PCTFREE 10 INITRANS 2 MAXTRANS 255 / CREATE INDEX fsa_eqc_dem_idx ON id_equipment_all_moves (eqpa_company, eqpa_line, eqpa_current_location, eqpa_move_code, eqpa_move_date, eqpa_bl_number) PCTFREE 10 INITRANS 2 MAXTRANS 255 / -- Constraints for ID_EQUIPMENT_ALL_MOVES ALTER TABLE id_equipment_all_moves ADD CONSTRAINT all_move_key PRIMARY KEY (eqpa_company, eqpa_container, eqpa_serial_no) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT eqpa_seq_unique UNIQUE (eqpa_company, eqpa_sequence) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT equipment_unique_key UNIQUE (eqpa_company, eqpa_container, eqpa_move_date, eqpa_move_code) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_carrier_check FOREIGN KEY (eqpa_line) REFERENCES ID_LINE_MASTER(line_code) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_code_check FOREIGN KEY (eqpa_move_code) REFERENCES ID_MOVE_MASTER(move_code) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_container_check FOREIGN KEY (eqpa_company, eqpa_container) REFERENCES ID_EQUIPMENT_MASTER(eqp_company,eqp_container) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_container_service_check FOREIGN KEY (eqpa_container_service_type) REFERENCES ID_SERVICE_MASTER(service_code) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_conttype_check FOREIGN KEY (eqpa_container_type) REFERENCES ID_CONTTYPE_MASTER(container_type) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_customer_check FOREIGN KEY (eqpa_company, eqpa_customer) REFERENCES ID_SHIP_CUSTOMER_MASTER(scm_company,scm_code) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_vessel_check FOREIGN KEY (eqpa_vessel) REFERENCES ID_VESSEL_MASTER(vessel_code) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT move_voyage_check FOREIGN KEY (eqpa_company, eqpa_line, eqpa_vessel, eqpa_voyage, eqpa_port) REFERENCES ID_VOYAGE_MASTER(voyage_company,voyage_line,voyage_vessel,voyage_voyage, voyage_port) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT account_status_check CHECK (eqpa_account_status IN ('X' , 'N' , 'P' , 'T')) / ALTER TABLE id_equipment_all_moves ADD CONSTRAINT eqpa_mode_check CHECK ( eqpa_mode IN ('S' , 'R' , 'T' , 'N', 'B')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_soc IN ('L' , 'S')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_entry_type IN ('R' , 'T' , 'D' , 'A' , 'I' , 'E')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_food_status IN ('Y' , 'N')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_booking_status IN ('P' , 'N' , 'X')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_billing_status IN ('X' , 'N' , 'P')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_service_type IN ('LCL' , 'MTY' , 'FTSP' , 'MTSP' , 'FCL')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_current_status IN ('C' , 'D' , 'H')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_out_gauge IN ('Y' , 'N')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_reefer_status IN ('Y' , 'N')) / ALTER TABLE id_equipment_all_moves ADD CHECK (eqpa_imco_status IN ('Y' , 'N')) / -- End of DDL script for ID_EQUIPMENT_ALL_MOVES