How does an alter work? I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table.
the query is taking around 1hr to finish executing.
So my question is: How does an alter work? and why is it affected by the number of records?
is it possible to speed up an alter query using column indexes?
An ALTER TABLE operation that adds a column to a table should be a simple data dictionary only update.  There is no reason for Oracle to visit all the blocks/rows in the table.  So, the operation should be very fast.  (sub-second response time.)

The only exception to this would be, if you're on a version of Oracle prior to 11g, and you're setting a default value for the column being added, Oracle has to visit all the rows.  In 11g, even setting a default value will not precipitate a visit to all the rows in the table.