Re: Calculations in maximizing rows per block

On 2017/08/14 22:09, Mark W. Farnham wrote:

> There is a decent chance you would benefit by physically storing your table as two tables in a cluster and referenced by a view (make sure you can follow the rules for an insertable view if you don't want to have to change your applications). I only mention this because of your indication your largest table might be worth some effort.

Intriguing idea! I would love to try that, but I don't think JD Edwards would like it, as I've seen it occasionally UPDATE by ROWID. For the record, this is the table in question (ignore the numeric datatypes, as the SQL generated is likely not targeted for Oracle): http://www.jdetables.com/?schema=910&table=F42199

> Since you have existing data, you can also determine the "most null" columns and you may have knowledge of which columns are used least. Putting the "most null" columns last can save a lot of space if you have them and saves some row reading overhead because Oracle does take a short cut when all the rest are null.

The first 254 columns of our implemented table have no defaults, but even then, I have not seen any NULLs in this table -- ever. That's not to say that JDE couldn't INSERT with NULLs, then immediately populate with an UPDATE, but I doubt it. I need to mine redos to verify...

> Putting the least frequently used columns late physically *MIGHT* pay off if your queries routinely only reference the columns used rather than "*".
>
> Even without clustering, that might mean you don't need the second row piece for a query.
>
> I mention this because my experience in the field shows that *MOSTLY* tables with over 255 columns have a lot of unpopulated columns and a lot of rarely or never used columns and sometimes columns that are functionally audit trail columns that could be coordinated as a second logical object. IF you can get rid of them to get under 255, that is plan A.

JDE maintains its own data dictionary that it uses to generate DDL. It gets most upset when table definitions (and to a much lesser extent, indexes) in the DB differ from its repository.

> RE: pctfree 0, yes, that controls whether a new row will be inserted into the block and does not change the mechanics of whether empty space in the block can be used for row updates that lengthen the row.

Perfect! That's what I was hoping for. But I still need to calculate that free space to help determine the costs of inter-row chaining/migration vs. lower block density.

> The ASSM space records are in their own blocks of the segment (not in a separate segment as I had BEGGED*) and additional space record blocks are allocated as needed.

So there's no ASSM info in each block?

> All the stuff mentioned earlier in the thread is probably gold.

 As is your post. Thanks!

Rich

--
http://www.freelists.org/webpage/oracle-l