JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 5: Making the most of tables.


Fixed Bugs in Drop Column (6th Feb 2002)

Breaking PK/FK constraints (25th Feb 2001)

Rebuilding indexes online

Wide Tables:




Back to Book Index


Fixed bugs in Drop Column (6th Feb 2002)

This note appears as a consequence of reading the bug fixes note that came with a recent upgrade to Version There are bugs in 8.1.7 that are fixed in this release, so if you are running an earlier release, be careful. Note - At the time of writing this addendum, I have not visited Metalink to read up the details of any bugs reported.

Bug 1949786 - Data may be inserted into the wrong partition of a partitioned table after a column has been dropped if the dropped column appears before the partition key column(s) in the table definition. The workaround is to ensure that all partitioned tables are created with the partition key column(s) first in the column list in the 'create table' statement. Fortunately, this is simple rule of thumb ordering is likely to be something that most people will have chosen to do 'by accident', but it is of course possible that some code generators may simply build tables with colums listed in alphabetical order.

Bug 1561782 - Data corruption is possible from alter table move of a table with a dropped column. You may recall that I was not enthusiastic about dropping columns, but also suggested (p.72) that if you really wanted to get rid of the column and save most of the space, then you should consider marking the column unused, and then moving the table. I have not yet checked if this is a 'lucky workaround' to the problem.

Top of page


Breaking PK/FK constraints

I used the example of how researching one aspect of the sesson parameter skip_unusable_indexes led me to discover that you could breach foreign key integrity constraints (p. 63). This is now logged as a priority 2 bug, number 1628159, with Oracle. As at 25th Feb 2001, this bug is now fixed in Oracle 9.0.

Top of page


Rebuilding indexes online:

There is a mention of rebuilding indexes online (p.72) in this chapter. There is a fairly serious bug with this feature, described in the addenda to chapter 7, that is not fixed until version

Top of page


Wide Tables:

Oracle 8i allows tables to have up to 1,000 columns, and I describe (p.74) some of the odd side-effects that appear when the number of columns goes above 254. It is not just large numbers of columns that introduce odd effects, though; a small number of lengthy columns can result in odd behaviour too. This issue is not unique to Oracle 8.1, but it seems appropriate to mention it here.

Consider a table with the following definition (v1 varchar2(4000), v2 varchar2(4000), v3 varchar2(4000)). Create it with a PCTFREE = 0 in a database built using 8K blocks. In these circumstances, a single block has a nominal free space of about 8,100 bytes, so we should expect the following insert statement to put a single row into a single block:

	insert into wide_table values(
		rpad('x',4000), rpad('x',4000), rpad('x',20)

However, a block dump shows that this single row gets split into two row segments - the split point is likely to depend on the version of Oracle and the operating system, but the largest row segment that can fit into an 8K block seems to be about 7,989 bytes (including the overheads), so the above row will split as follows:

	Row 1:	First 41 bytes of column 1 
	Row 2:	Remaining 3,959 bytes of column 1, all of column 2, all of column 3.

In other words, you can get chained rows which chain inside a single block simply because they are rather long. (I have not pursued this line into 16K and 32Kblocks - but if you are planning to use long rows you may want to experiment with the full range of possible block sizes.).

(If you are looking for the full byte-count of 7,989 in Row 2 above, remember that each row has 2 bytes overhead, one byte column count, and that the 'length byte' for longer columns is actually 3 bytes.)

The consequences of this unexpected chaining is reflected 'correctly' when you analyze the table - the chained rows show up in user_tables.chain_cnt. Queries against these rows also show their effects in v$sysstat under the statistic 'table fetch continued row'

Top of page.



Back to Book Index