Of course, you must deal with existing column
data. When modifying a tables column datatype you may want to
export the rows, redefine the table and then re-import you data.
In a traditional database you would need to
follow these cumbersome steps to alter a column data type:
1 - Create the new column at the end of the table.
2 - Run an update to populate the new table column
3 - Drop the old table column
4 - Re-name the new column to the original column
Also, starting in Oracle 11g, the new
virtual column feature is useful for changing table column
Implement Bitmap Indexes
was called upon to troubleshoot and fix a State Police query
system that was experiencing slow query performance. The system
was read-only except for a 30-minute window at night for data
loading. Upon inspection of the SQL, I noted complex combinational
color=’BLU’ and make=’CHEVY’ and year=1997 and doors=2;
The distinct values for each of these columns were less than
200, and concatenated indexes were employed. Replacing the
b-tree indexes with bitmap indexes resulted in a stunning
performance improvement for the entire system, taking queries
from 3 seconds down to under one-tenth of a second.