oracle decreasing vs increasing column varchar2 performance 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 &lt;= 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)
Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking?


Correct. 

When you decrease the length, Oracle needs to check the values in all rows in the table if any violates the new (reduced) length - it effectively reads all rows in the table.

This isn't necessary when you increase the length - no existing value can exceed that.