I know it seems stupid question but why increasing the column of a table tend be fast but decreasing it takes time depend on the size of the table?
Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking?
Example:
Drop table ex_employee
/
create table ex_employee (id number(14) null, name varchar2(10) null)
/
insert into ex_employee
select level, level||'A' from dual
connect by LEVEL <= 20000000
/
Running the below takes 0.2 seconds
alter table ex_employee modify name VARCHAR2(100)
however decreasing it takes arround 15 seconds.
alter table ex_employee modify name VARCHAR2(10)