Tables Wizard (Oracle)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Object Wizards

Tables are the most basic data storage units for Oracle. As you might expect, data is stored in rows and columns. The Table Wizard constructs the necessary CREATE TABLE statement from the information that you supply. The Table Wizard varies slightly in content based on the version of Oracle on the target datasource. But in all cases, you name columns and determine column width or precision and scale depending on the column’s data type. A row collects the column information that corresponds to a single record.

You can set rules for the columns to live by, and these are called integrity constraints. For example, if you select NOT NULL, that column will have to have a value in each row.

Also, before beginning, consider what kind of table you want to create as the wizard will ask you to choose:

Heap organized table

This is a basic table where data is stored as an unordered collection, i.e., heap.

Index-organized table

A B-tree index structure stores data, sorted by primary key. Nonkey column values are stored too.

Partitioned table

Data is broken down into smaller, more manageable pieces called partitions or subpartitions. Each partition can be managed individually and operate independent of the other partitions.

Clustered table

This is a table that, once created, is part of a cluster. A cluster is a group of tables that share some data blocks and columns and are often used together. To create a cluster, use the Clusters Wizard (Oracle).

Note: The table wizard panels differ depending on what options you select.
Note: To simplify the process of creating a table, the Table Wizard focuses on creating the basic table definition with a primary key constraint. After you create the basic table definition you can add unique and foreign keys to the table on the Constraints tab of the Tables Editor.

To create a new table using a wizard:

  1. Open a creation wizard for a table. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Tables (Oracle) - Properties

When creating or editing a table, this tab/panel lets you work with the following settings:

Setting Description

Owner

Select the owner of the table.

Name

Provide a name for the table

Cache

Enabling this feature keeps a block in memory by placing it at the most recently used end. This option is useful for small lookup tables.

Row Movement

Enabling this option permits the migration of a row to a new partition if its key is updated.

Parallel Degree

A value indicating the number of query server processes that should be used in the operation.

Parallel Instances

A value indicating how you want the parallel query partitioned between the Parallel Servers.

Physical group

Choose a Row Organization of INDEX, HEAP, or EXTERNAL. If you chose INDEX or HEAP, enable or disable Logging. If you chose EXTERNAL, provide an External Type, Default Directory, Access Parameters, Reject Limit, and Location.

Logging

Redo logs minimize the loss of data in the event that an uncontrolled shutdown happens.

Tables (Oracle) - Columns

When creating or editing a table, this tab/panel lets you manage the columns for the table.

To add a column to the table

1. Click Add Column, provide a Name he column in the Property/Value list, and press TAB or ENTER.
The column is added to the columns list on the left, with default attribute values.
2. Proceed to edit the column attributes.

To edit column attributes

3. Select the column in the columns list on the left. The controls in the Property/Values list are updated with values of the selected column.
4. Use the following table as a guide to providing additional property values, noting that availability of a property differs by data type and other property selections.
Property or group Description

Virtual

Selecting this check box defines the column as an Oracle virtual column, with a value calculated from a column expression. Virtual columns do not use any disk space as there is o data to store, and INSERT/UPDATE operations are not supported. Consult Oracle documentation before working with virtual columns to familiarize yourself with topics such as column expressions and virtual column-specific details such as automatic type conversion. For more information, see Accessing Third Party Documentation. After specifying a column as virtual, use the Default Value box to provide the calculation.

Datatype

This group lets you select a valid Type for the column. Depending on your selection, additional properties such as Scale, Size, Width, and Unused may be available.

Allow Nulls

Select this check box to allow nulls in this column.

Encryption

Use controls in this group to have the column encrypted using transparent data encryption. Select the Encrypted check box to enable transparent data encryption for the column. Type a Password value used to build an IDENTIFIED BY clause. The column key will be derived from the value you provide. Select the Salted check box to add a SALT option, appending a random ‘salt’ string, to the clear text of the column before encrypting. Leaving the check box unselected adds a NO SALT option. From the Encryption Algorithm dropdown, select an algorithm (AES256, NONE, DES168, AES192, AES128, or DES156) that will be used to build a USING clause.

Default Value

If you selected the Virtual check box, type a valid Oracle column expression that will calculate the value of the column. Otherwise, either select a pseudocolumn (CURRENT_TIMESTAMP, USER, SYSDATE, or UID) from the dropdown or type an expression resulting in a value that matches the selected Datatype.

Comment

Lets you add a comment to the column.

LOB Storage

This group is available for bfile, blob, clob, and nclob types. Settings include Segment Name, Configuration properties (Tablespace, Chunk, Percent Version, Enable Storage In Row, Cache, and Logging), and Storage properties (Initial Extent, Next Extent, Percent Increase, Minimum Extents, Maximum Extents, Free Lists, and Free List Groups).

To delete a column

  1. Select a column from the column list on the left.
  2. Click Delete to remove the column from the table.

To change a column’s ordering position

  1. Select a column from the column list on the left.
  2. Use the arrow buttons to move the column up or down.

Tables (Oracle) - Indexes

Note: This tab/panel is not available with a Row Organization of EXTERNAL.

Click Add to open the Index Wizard. For details, see Indexes Wizard (Oracle).

Tables (Oracle) - Constraints

Note: This tab/panel is not available with a Row Organization of EXTERNAL.

Selecting a constraint type and clicking Add opens the object wizard for that object type. For details see:

Tables (Oracle) - Storage

Note: This tab/panel is not available with a Row Organization of EXTERNAL.

When creating or editing a table, this tab/panel has the following settings:

Settings Description

Data Block Storage group

Select the DEFAULT Tablespace only if you want the partitions in the same tablespace as the partitions in the underlying table. Percent Free identifies how much space you want to allocate for new rows or updates to existing rows. Initial Transactions ensures that a minimum number of concurrent transactions can update a primary key block, avoiding the overhead of allocating a transaction entry dynamically. Maximum Transactions limits concurrency on a primary key block.

Extents group

An extent is the unit of space allocated to an object whenever the object needs more space. Initial Extent - The initial space extent (in bytes) allocated to the object. Next Extent - The next extent (in bytes) that the object will attempt to allocate when more space for the object is required. Percentage Increase - Lets you type the percentage. NOTE: You should be careful when setting Percent Increase because it magnifies how an object grows and, therefore, can materially affect available free space in a tablespace. Minimum Extents - For a dictionary managed tablespace, this is the total number of extents to be allocated when the index is first created. For a locally managed tablespace, this is simply the initial amount of space allocated. Maximum Extents - For a dictionary managed tablespace, this is the total number of extents that can ever be allocated to the index. In a locally managed tablespace, the database will automatically manage the extents.

Freelists group

Free lists let you manage the allocation of data blocks when concurrent processes are issued against the primary key. You can potentially improve the performance of the primary key by identifying multiple free lists, which can reduce contention for free lists when concurrent inserts take place. The default and minimum value is 1. You should increase this number if multiple processes access the same data block. Free List Groups is the number of groups of free lists. NOTE: This option is only applicable for the parallel server option.

Buffer Pool

DEFAULT - Choose this if you want to use the default bufferpool. KEEP - Use this to retain the object in memory to avoid I/O conflicts. This type of bufferpool stores frequently referenced data blocks in a separate cache. RECYCLE - Select this option to save cache space by ridding data blocks from memory as soon as they are no longer in use.

Tables (Oracle) - IOT Properties

Note: This tab/panel is not available with a Row Organization of EXTERNAL.

Provide compression and space details for an index-organized table.

Tables (Oracle) - Partition

Note: This tab/panel is not available with a Row Organization of EXTERNAL.

Prior to working with partitions, you should be familiar with the material in Oracle Partitioning.

Click Create Partition to Partition a table.

Oracle Partitioning

Partitioning your tables lets you get around the problem of supporting large tables. Partitioning lets you break large tables into smaller pieces, which are called partitions. Partitions make the data in your table easier to manage and analyze. Your SQL statements can access the partitions rather than the entire table. Partitions are most useful in data warehouse applications, which store large amounts of data.

The table below describes the types of partitions in Oracle:

Partition Type Description

Range

Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year. Performance is best when the data evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning.

Hash

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key. Creating and using hash partitions gives you a highly tunable method of data placement, because you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).

Composite

Hash partitions partition the table according to a hash function. Composite partitions use both range and hash types, first partitioning the data by a range of values, and then further dividing the partitions into subpartitions by way of a hash function. This option is not available for index-organized tables.

List

Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.

Partition a table

The Add partition wizard lets you set up partitions for a table. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:

Step Settings and tasks

Properties

Select a Partition Type and optionally, a Subpartition Type.

Columns

For each column, click the New button and select a name from the Column dropdown. Use the Delete button to drop a selected column.

Subpartition Columns (only available with a Partition Type of RANGE and Subpartition Type of HASH or LIST)

For each column, click the New button and select a name from the Column dropdown. Use the Delete button to drop a selected column.

Subpartitions (only available with a Subpartition Type of HASH)

Specify a Default number of partitions. For each partition, click the New button and then select a tablespace from the dropdown.

Range Definitions (only available with a Partition Type of RANGE)

Click the New button to Add a partition definition.

Partition Definition (only available with a Partition Type of HASH)

To specify a partition method other than None, take one of the following actions: (1) Select the Number Of Partitions radio box, specify the Number Of Partitions, and for each partition, click the New button and choose a tablespace from the dropdown, or (2) select the By Partition Name radio box and for each partition, click the New button provide a name and then choose a tablespace from the dropdown.

List Definitions (only available with a Partition Type of List)

Click New to Add a partition definition.

Add a partition definition

Use the following topics as a guide in completing the settings in this wizard:

Step Settings and tasks

Partition Definition

Name

Provide a name.

Tablespace

Select a tablespace from the dropdown

Logging

Enable or disable logging.

Subpartitions

To specify a partition method other than None, select the By Subpartition Name radio box and for each partition, click the New button to open a dialog that lets you provide subpartition values. NOTE: When you split a range-list partition, you cannot specify the new partitions’ subpartition information.

Storage

Provide or select Data Block Storage, Extents, Freelists, and Buffer Pool values.