0

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)

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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