Heap-Organized Tables-Database Tables-2

That aside, what is important to know about heap tables? Well, the CREATE TABLE syntax spans some 100+ pages in the Oracle Database SQL Language Reference manual provided by Oracle, so there are lots of options that go along with them. There are so many options that getting a hold on all of them is pretty difficult. The wire diagrams (or train track diagrams) alone take 20 pages to cover. One trick I use to see most of the options available to me in the CREATE TABLE statement for a given table is to create the table as simply as possible, for example:

$ sqlplus eoda/foo@PDB1
SQL> set long 100000
SQL> create table t( x int primary key,y date,z clob); Table created.

Then, using the standard supplied package DBMS_METADATA, I query the definition of it and see the verbose syntax:

SQL> select dbms_metadata.get_ddl( ‘TABLE’, ‘T’ ) from dual;DBMS_METADATA.GET_DDL(‘TABLE’,’T’)
CREATE TABLE “EODA”.”T”(“X” NUMBER(*,0),”Y” DATE, “Z” CLOB, PRIMARY KEY (“X”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE “USERS” ENABLE
) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING TABLESPACE “USERS” LOB (“Z”) STORE AS SECUREFILE (TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES )

The nice thing about this trick is that it shows many of the options for my CREATE TABLE statement. I just have to pick datatypes and such; Oracle will produce the verbose version for me. I can now customize this verbose version, perhaps changing the ENABLE STORAGE IN ROW to DISABLE STORAGE IN ROW, which would disable the storage of the LOB data in the row with the structured data, causing it to be stored in another segment. I use this trick myself all of the time to avoid having to decipher the huge wire diagrams. I also use this technique to learn what options are available to me on the CREATE TABLE statement under different circumstances.

Now that you know how to see most of the options available to you on a given CREATE TABLE statement, which are the important ones you need to be aware of for heap tables? In my opinion, there are three with ASSM:

•\ PCTFREE: A measure of how full a block can be is made during the INSERT process. As shown earlier, this is used to control whether a row may be added to a block or not based on how full the block currently is. This option is also used to control row migrations caused by subsequent updates and needs to be set based on how you use the table.

•\ INITRANS: The number of transaction slots initially allocated to a block. If set too low (defaults to two), this option can cause concurrency issues in a block that is accessed by many users. If a database block is nearly full and the transaction list cannot be dynamically expanded, sessions will queue up for this block, as each concurrent transaction needs a transaction slot. If you believe you will have many concurrent updates to the same blocks, consider increasing this value.

•\ COMPRESS/NOCOMPRESS: Enables or disables compression of table data during either direct path operations or conventional path (“normal,” if you will) operations such as INSERT. Use the COMPRESS or NOCOMPRESS to specify the use of table compression (or not) during direct path operations only. The options available are NOLOGGING, COMPRESS FOR OLTP, and COMPRESS BASIC. NOLOGGING disables
any compression, COMPRESS FOR OLTP enables compression for all operations (direct or conventional path), and COMPRESS BASIC enables compression for direct path operations only. Starting with Oracle 12c, these compression options are now specified syntactically as ROW STORE COMPRESS BASIC (enables compression during direct path operations) and ROW STORE COMPRESS ADVANCED (enables compression for all operations).

Note LOB data that is stored out of line in the LOB segment does not make use of the PCTFREE/PCTUSED parameters set for the table. These LOB blocks are managed differently: they are always filled to capacity and returned to the FREELIST only when completely empty.