Summary-Database Tables

Hopefully, after reading this chapter you have come to the conclusion that not all tables are created equal. Oracle provides a rich variety of table types that you can exploit. In this chapter, we have covered many of the salient aspects of tables in general and explored the many different table types Oracle provides for us to use.

We began by looking at some terminology and storage parameters associated with tables. We looked at the usefulness of FREELISTs in a multiuser environment wherea table is frequently inserted/updated by many people simultaneously, and how the use of ASSM tablespaces could make it so we don’t even have to think about that. We investigated the meaning of PCTFREE and PCTUSED, and we developed some guidelines for setting them correctly.

Then we got into the different types of tables, starting with the common heap. The heap-organized table is by far the most commonly used table in most Oracle applications, and it is the default table type. We moved on to examine index-organized tables, which provide us with the ability to store our table data in an index structure instead of a heap table. We saw how these are applicable for various uses, such as lookup tables and inverted lists, where a heap table would just be a redundant copy of the data. Later, we saw how IOTs can really be useful when mixed with other table types, specifically the nested table type.

We looked at cluster objects, of which Oracle has three kinds: index, hash, and sorted hash. The goals of the cluster are twofold:

•\ To give us the ability to store data from many tables together on thesame database block(s).

•\ To give us the ability to force like data to be stored physicallytogether based on some cluster key. In this fashion, all of the data fordepartment 10 (from many tables) may be stored together.

These features allow us to access related data very quickly with minimal physical I/O. We observed the main differences between index clusters and hash clusters, and we discussed when each would (and would not) be appropriate.

Next, we covered nested tables. We reviewed the syntax, semantics, and usage of nested tables. We saw how they are, in fact, a system-generated and maintained parent/ child pair of tables, and we discovered how Oracle physically does this for us. We looked at using different table types for nested tables, which by default use a heap-based table. We found that there will probably never be a reason not to use an IOT instead of a heap table for nested tables.

Then we looked into the ins and outs of temporary tables, including how to create them, where they get their storage from, and the fact that they introduce no concurrency-­related issues at runtime. We explored the differences between session-­ level and transaction-level temporary tables, and we discussed the appropriate method for using temporary tables in an Oracle database.

As with nested tables, we discovered there is a lot going on under the covers with object tables in Oracle. We discussed how object views on top of relational tables can give us the functionality of an object table while at the same time giving us easy access to the underlying relational data.

This chapter finished with a look into blockchain tables. This is a new table introduced in Oracle 21c and backported to version 19.10. Blockchain tables are specialized insert-only tables that are used for applications that require the security of never allowing deletes or modifications to data in the table.