Database Reference
In-Depth Information
Then, using the standard supplied package
DBMS_METADATA
, I query the definition of it and see the verbose syntax:
EODA@ORA12CR1> 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 data types 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 and five with MSSM:
FREELIST
: MSSM only. Every table manages the blocks it has allocated in the heap on a
FREELIST
. A table may have more than one
FREELIST
. If you anticipate heavy insertion into
a table by many concurrent users, configuring more than one
FREELIST
can have a major
positive impact on performance (at the cost of possible additional storage). Refer to the
previous discussion and example in the section “FREELISTS” for the sort of impact this setting
can have on performance.
•
PCTFREE
: Both ASSM and MSSM. 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.
•
PCTUSED
: MSSM only. A measure of how empty a block must become before it can be a
candidate for insertion again. A block that has less than
PCTUSED
space used is a candidate for
insertion of new rows. Again, like
PCTFREE
, you must consider how you will be using your table
to set this option appropriately.
•
INITRANS
: Both ASSM and MSSM. The number of transaction slots initially allocated to a
block. If set too low (defaults to 2, 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.
•
