how to index the condition "null" or "not null"?
Hello together,
first for your background we would like to do the following change on a Table:
1. We have an old column varchar2(50) which is filled with strings.
2. We now have a new column number(3) which is empty.
Our aim is to migrate from the old column to the new column while each different string is mapped to a number. ("abc" -> 0, "xyz" -> 1, etc.)
The table containing the columns has 1.3 billion lines. There is no index on the old column.
If possible the migration should be done online (without downtime) and the temporary additional space should be as low as possible. Due to the affect to the performance we plan to cut the migration into multiple parts which will run on low load times.
To avoid full table scans I ask if there is a possibility to index the status of the row. With status i am only interested in "null" or "not null".
Is it possible to set some type of bitmap index? (0 = null, 1 = not null) which should boost up the migration-time and does not use much memory?
Unfortunately i am quite unfamiliar with indexes right now.
For splitting the migration into parts i thought about using to use "where rowum >= x and rownum <= x+10,000,000and new_column is not null " to do it in 10mio steps.
thanks in advance,
Andreas