Updating index on oracle table
Most of those tables are larger than the one I'm having trouble with both data and column-count-wise. But then I first added the columns with no default values.What do I have to do to be able to add those new columns? Six minutes is "longer than I'd like but what the hey it's nearly 4 million records I'll sit here a bit and let it grind hey let's check statistics on that and see what's going on ho-hum glad I get paid for this hmmm what's in the junk machine whoops time for a quick trip down the hall dang is this thing still going hey how'd the team do yesterday what's up with what's-her-name in HR heard she's splitsville with the old man sure I've got time for a question what a maroon dang is..it's done! After columns got added I specified the default value and it executed immediately with no more waiting.The format of a row is: row header, column length - value; column_length - value; column_length - value; ....The length of a field is stored as an attribute in the row.CASCADE performs the same operations on all dependent objects.I need to add two DATE columns to an already existing table.But it takes very long and I have to kill the process.
Table compression saves disk space and reduces memory use in the buffer cache, and is completely transparent to applications.
@Jens - that's because adding a default value later on does not affect the existing rows, whereas when adding a new column, the default value must be set on each row - unless the default is NULL, in which case Oracle doesn't have to visit the existing rows. I am adding a new column with character varying(50) without any defaults.
My table contains about 1 million records but the query is executing for the last 1 hour and is still on.. This is the query that I have given: ALTER TABLE ccdb.consumer_index_details ADD COLUMN rapdrp_pole_code character varying(50); I was also facing this problem when adding a varchar2(4000) column with no default value one a 61K row table in Oracle 11g. I tried to abort the operation, and retried it, but it still didn't finish.
I've come back here to check out this pattern again. MERNIS_NO); A note regarding the two solutions that suggest: 1) Insert, if exception then update, or 2) Update, if sql%rowcount = 0 then insert The question of whether to insert or update first is also application dependent. The one that is most likely to succeed should go first.
It fails silently when concurrent inserts are attempted. If you pick the wrong one you will get a bunch of unnecessary index reads. In the first example the update does an index lookup. Oracle opens an implicit cursor, and we use it to wrap a corresponding insert so we know that the insert will only happen when the key does not exist.
@Tim Sylvester - Oracle uses transactions, so guarantees the snapshot of data at the start of a transaction is consistent throughout the transaction save any changes made within it.