2

Say we run an alter table command to change the table column length from 5 to 6. If we run the same command again, does Oracle alter the table again?

3

Yes it does because database doesn't check the previous value it only sets the new value. The following demonstrates it.

SQL> conn jay
Connected.
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter table test modify name varchar2(20);

Table altered.

SQL> alter table test modify name varchar2(20);

Table altered.

SQL> alter session set sql_trace=false;

Session altered.

SQL> exit

The following is formatted output of trace file generated by tkprof.

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


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          6          4           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          4           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  TAB$ (cr=3 pr=0 pw=0 time=175 us)
         1          1          1   TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=11 us cost=2 size=142 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

SQL ID: 6vqvn8ya0xybh Plan Hash: 829209562

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


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          6          4           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          4           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  COL$ (cr=3 pr=0 pw=0 time=74 us)
         1          1          1   TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=19 us cost=2 size=61 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

SQL ID: 4yyb4104skrwj Plan Hash: 2683643009

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 
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        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          2           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          2           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  OBJ$ (cr=3 pr=0 pw=0 time=114 us)
         1          1          1   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=39 us cost=3 size=87 card=1)(object id 37)

********************************************************************************

As you can see the tab$, col$and obj$ base tables are updated two times(evidenced by number of executes which is 2). These base tables stores the objects meta data.

  • So, the second alter table does alter the column again, but it seems like the execution time is much less? This more apparent if you are working on a table with more rows? – Sun Oct 28 '16 at 13:30
  • @Sun : Yeah, since the query has been parsed already Oracle just executes the query, it saves time and resources. Also the required data can be found in the buffer cache. – JSapkota Oct 28 '16 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.