11 Managing Tables

 

Chapter 11  Managing Tables

After completing this lesson, you should be able to do the following:

★    Identify the various methods of storing data

★    Outline Oracle data types

★    Distinguish between an extended versus a restricted rowid

★    Outline the structure of a row

★    Create regular and temporary tables

★    Manage storage structures within a table

★    Reorganize, truncate,and drop a table

★    Drop a column within a table

Storing User Data

★    Regular table, Partitioned table,Index-organized table, cluster

Oracle Built-in Data Types

Data type

★    User-defined , built-in

★    Scalar, Collection, Relation

★    char(N), nchar(n), varchar2(n), nvarchar2(n), number(p,s), date, timestamp, raw(n), blob, clob, nclob, bfile, long, long raw, rowid, urowid, varray, table, ref

General Information

Here is some general information about tables, regardless of their type:

★    A table can have up to 1,000 columns, althouth I recommend against a design that does contain the maximum number of columns, Oracle will internally store a row with more than 254 columns in separate row pieces that point to eache other and must be reassembled to produce the entire row image.

★    A table can have as many indexes as there are permutations of columns

★    There is no limit to the number of tables you may have, enven with a single database.

ROWID Format

★    Extended rowid format

OOOOOO

FFF

BBBBBB

RRR

 

Data Object Number

Relative file number

Block number

Row number

 

★    Restricted rowid format

BBBBBBBB

RRRR

FFFF

 

 

Block number

Row number

File number

 

 

 

Locating a Row Using ROWID

ROWID is a data type that can be queried along with other columns in a table.It has the following characteristics:

★    ROWID is a unique identifier for each row in the database.

★    ROWID is not stored explicitly as a column value.

★    Although the ROWID does not directly give the physical address of a row, it can be used to locate the row.

★    ROWID provides the fastest means of accessing a row in a table.

★    ROWIDS are stored in indexes to specify rows with a given set of key values.

★    Because a segment can only reside in one tablespace, by using the data object number used to locate the block

Structure of a Row

Row data is stored in database blocks as variable-length records.Columns for a row are generally stored in the order in which they are defined and and any trailing NULL columns are not stored.

Note: A single byte for column length is required for non trailing NULL columns.Each row in a table has:

★    Row header: Used to store the number of columns in the row, the chaining information, and the row lock status.

★    Row data: For each column, the Oracle server stores the column length and value(One btye is needed to store the column length if the column will require more than 250 bytes of storage in which case three bytes will be used for column length. The column values is stored immediately following the column length bytes.)

★    Adjacent rows do not need any space between them. Each row in the block has a slot in the row directory. The directory slot points to the beginning of the row.

﹟create tablespace assm datafiel ‘/u01/oradata/bbk/assm_1.dbf’ size 100M extent managemnt local uniform size 128K segment space management auto;

﹟create tablespace ussm datafile ‘/u01/oradata/bbk/ussm_1.dbf’ size 100M extent management local uniform size 128K segment space management manual;

﹟create user as1 identified by as1 default tablespace assm;

﹟createt user us1 identified by us1 default tablespace mssm;

grant connect, resource to as1;

grant connect, resource to us1;

desc user_segment;

select segment_name, segment_type from user_segment;

create table t(

id int,

name char(10)

)

storage(initial 128K next 128K pctincrease 0 min extents 1 maxextents 5)

tablespace assm;

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.

ININITTANS: Both ASSM and MSSM. The number of transaction slots initially allocated to a block.If set too low (it defaults to and has a mimiumum of 2),this option can cause concurrentcy issues in a block that is accessed by many uses. If a database block is queue up waiting for this block, as each concurrent transaction needs  a transaction slot. If you believe you will have many concurrent updates to the same blocks , you should consider increasing this value.

Creating a Table:Guidelines

Place tables in separate tablespaces.

Use locally-managed tablespaces to avoid fragmentation.  --碎片

Use few standard extent sizes for tables to reduce tablespace fragmentation.

 

Creating Temporary Tables

﹟create global temporary table hr.employees_temp as select * from hr.employees;

Tables retain data only for the duration of a transaction or session.

DML locks are not acquired on the data.

You can create indexes, views, and triggers on temporary tables.

Temporary Tables

Temporary tables are used to hold intermdiate resultsets, for the duration of either a transaction or a session. The data held in a temporary table is only ever visible to the current session----no other session will see any other session’s data, even if the currrent session COMMIT the data. Multiuser concurrency is not an issue with regard to temporary tables either, as or session can never block another session by using a temporary table. temporary tables generate significantly less redo than regular tables However, since they must generate undo inforamtion for the data they contain,they will generate some amount of redo. UPDATEs and DLELTEs will generate the largest amount; INSERTs and SELECTs the least amount.

SQL> create global temporary table tmp_session   --session 级

  2  on commit preserve rows

  3  as select * from t where 1=0;

 

Table created.

 

SQL> create global temporary table tmp_transaction on commit delete rows --transaction 级

2        as select * from t where 1=0;

SQL> insert into tmp_session select * from t;

 

2 rows created.

 

SQL> insert into tmp_transaction select * from t;

 

2 rows created.

 

SQL> select count(*) from tmp_session;

 

  COUNT(*)

----------

         2

 

SQL> select count(*) from tmp_transaction;

 

  COUNT(*)

----------

         2

 

SQL> commit;

 

Commit complete.

 

SQL> select count(*) from tmp_session;

 

  COUNT(*)

----------

         2

 

SQL> select count(*) from tmp_transaction;

 

  COUNT(*)

----------

         0

 

Row Migration

If PETFREE is set to a low value, there may be insufficient space in a block to accommodate a row that grows as a result of an update.When this happens, the Oracle sever will move the entire row to a new block and leave a pointer from the original block.

Row Chaining  -- 链接

Row chaining occurs when a row is too large to fit into any block.This might occur when the row contains columns that are very long.In this case, the Oracle Server divides the row into smaller chunks called row pieces.

alter table hr.emplyees

pctfree 30 pctused 50 storate(next 500K minextend 200K);

Nonpatitoned Table Reorg

alter table hr.employees move tablespace data1;

Moving a table from one tablespace to another

★    When a nonpartitioned table is reorganized, its structure is kept, but not its contents.

★    It is used to move a table

★    A nonpartitoned table can be moved without having to run the Export or Import utility addition, it allows the storage paramters to be changed. This is useful when:

Reorganizing the table to eliminate row migration

 

SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','T')

--------------------------------------------------------------------------------

 

  CREATE TABLE "AS1"."T"

   (    "ID" NUMBER(*,0),

        "NAME" CHAR(10)

   ) PCTFREE

SQL> alter table t move tablespace mssm;

 

Table altered.

 

SQL> select * from t;

 

        ID NAME

---------- ----------

         0 boobooke

         1 boobooke

 

SQL> select table_name, tablespace_name from user_tables;

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TMP_SESSION

TMP_TRANSACTION

T                              MSSM

 

SQL> create table m select * from t;

create table m select * from t

               *

ERROR at line 1:

ORA-00922: missing or invalid option

 

 

SQL> create table m as select * from t;

 

Table created.

Truncating a Table

truncate table hr.employees;

★    Truncating a table deletes all rows in a table and releases used space.

★    Corresponding indexes are turncated

★    The effects of using this command are as follows:
﹟All rows in the table are deleted.

﹟No undo data is generated and the command commits impliclitly because Truncate table is a ddl command.

﹟Corresponding indexex are alse truncated.

﹟A table that is being referenced by a foreign key cannot be truncated.

﹟The delete triggers do not fire when this command is used.

Dropping  a table

Drop table hr.departments cascade constraints;

When a table is dropped, the extents used by the table are released. If they are continugous they may be coalesced either automatically or manually at a later stage.

The CASCADE CONSTRAINTS option is necessay if the table is the parent table in a foreign key relationship;

Dropping a Column

Removing a column from a table

alter table hr.employees

drop column comments

cascade constraints checkpoint 1000;

﹟Removes the column length and data from each row

  freeing space in the data block

﹟Dropping a column in a large table takes a considerable amount of time.

Renaming a column from a table

alter table [schema.]table_name

rename column old_column_name

to new_column_name;

Using the UNUSED Option

Instead of removing a column from a table, the column can be marked as unused and then removed late.This has the advantage of being relatively quickly,because it does not reclaim the disk space because the data is not removed.Columns that are marked as unused can be removed at a later time from the table when is less activity on the system;

 

Using the UNUSED Option

﹟Mark a column as unused:

alter table t set unused column myname cascade constraints;

﹟Drop unused columns:

alter table t drop unused columns checkpoint 1000;

﹟Continue to drop column operation:

alter table t

drop columns continue checkpoint 1000;

DBA_TABLES

DBA_OBJECTS

DBA_EXTENTS

练习：

TRUNCATE TABLE  TEST_YZH REUSE STORAGE;  --删除数据，空间未释放

truncate table test_yzh;

 

Managing indexes

After completing this lesson, you should be able to do the following:

﹟List the different types of indexes and their uses

﹟Create various types of indexes

﹟Reorganize indexes

﹟Maintain indexes

﹟Mointor the usage of an index

﹟Obtain index informaiion

Classification of Indexes

﹟Logical

    ﹟Single column or concatenated

    ﹟Unique or nonunique

    ﹟Function-based

    ﹟Domain

★    Physical:

﹟Partioned or nonpartitioned

﹟B-tree: Normal or reverse key

﹟Bitmap

B-Tree Index

Root ->Branch->Leaf->Index entry header, Key column length,Key column value, ROWID

 

 

SQL> CREATE INDEX t_idx1 ON t(id);

 

Index created.

 

SQL> SELECT object_name, object_type FROM user_objects;

 

OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

T_IDX1                         INDEX

T                              TABLE

 

SQL> CREATE INDEX t_idex2 ON t(sex,name);

 

Index created.

 

SQL>  SELECT object_name, object_type FROM user_objects;

 

﹟Balance query and DML needs.

﹟Place in separate tablespace.

﹟Use uniform extent size: Multiples of five blocks or MINIMUM NOLOGGING size for tablespace.

﹟Consider NOLOGGING for large indexes.

﹟INITRANS should generally be higher on indexes than on the corresponding tables.

 

 

 

Use the ALTER INDEX command to:

﹟Move an index to a different tablespace

﹟Improve space utilization by removing deleted entries.

alter index orders_region_id_idx rebuild tablespace index02;

⑴.  Lock the table

⑵.  Create a new, temporary index by reading against the contents of the existing index.

⑶.  Drops the orginial index.

⑷.  Renames the temporary index to make it seem to be the original index.

⑸.  Remove the table lock.

Rebuild an index in the following situation:

⑴The existing index must be moved to a different tablespace.

⑵An index contains many deleted entries.

⑶An existing normal index must be converted into a reverse key index.

⑷The table of the index has been moved to another tablespace using the ALTER TABLE …. MOVE TABLESPACE command.

★    Indexes can be rebuilt with minimal table locking

★    ALTER INDEX oerders_id_idx rebuild online;

Online Index rebuilding

1.      Lock the table.

2.      Create a new, temporary and empty index and an IOT to store on-going DML.

3.      Release the table lock.

4.      Poplulate the temporary index by reading against the contents of the existing index.

5.      Merge contents of

COALESCE the index

ALTER INDEX orders_id_idx COALESCE;

﹟Scan along the base of the index

﹟Where adjacent nodes can be combined into a single node.do so.

Check index vality

ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;

SQL> select height, name, lf_rows, lf_blks, del_lf_rows from index_stats;

 

    HEIGHT NAME                              LF_ROWS    LF_BLKS DEL_LF_ROWS

---------- ------------------------------ ---------- ---------- -----------

         2 T_IDX1                               1000          3           0

 

SQL> select height, name, lf_rows, lf_blks, del_lf_rows from index_stats;

 

    HEIGHT NAME                              LF_ROWS    LF_BLKS DEL_LF_ROWS

---------- ------------------------------ ---------- ---------- -----------

         2 T_IDX1                               1000          3           0

 

SQL> delete from t where id>900;

 

100 rows deleted.

 

SQL> ANALYZE INDEX t_idx1 VALIDATE STRUCTURE;

 

Index analyzed.

 

SQL> SELECT height, name,lf_rows, lf_blks, del_lf_rows FROM index_stats;

 

    HEIGHT NAME                              LF_ROWS    LF_BLKS DEL_LF_ROWS

---------- ------------------------------ ---------- ---------- -----------

         2 T_IDX1                               1000          3         100

★Drop and re-create an index before bulk loads.

★Drop indexes that are infrequently needed, and build indexes when necessay.

★Drop and re-create invalid indexes.

Monitoring index

★    To start mointoring the usage of an index:

★    AlTER INDEX hr.dept_id-idx MONITORING USAGE.

★    V$OBJECT_USAGE

SQL> SELECT object_name, object_type FROM user_objects;

 

OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

T_IDX1                         INDEX

T                              TABLE

T_IDEX2                        INDEX

MY_BIT_IDX                     INDEX

 

SQL> SELECT * FROM v$object_usage;

 

no rows selected

 

SQL> ALTER INDEX t_idx1 MONITORING USAGE;

 

Index altered.

 

SQL> SELECT * FROM v$object_usage;

 

INDEX_NAME                     TABLE_NAME                     MON USE

------------------------------ ------------------------------ --- ---

START_MONITORING    END_MONITORING

------------------- -------------------

T_IDX1                         T                              YES NO

11/23/2009 18:32:05

 

 

SQL> DESC v$object_usage;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 INDEX_NAME                                NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 MONITORING                                         VARCHAR2(3)

 USED                                               VARCHAR2(3)

 START_MONITORING                                   VARCHAR2(19)

 END_MONITORING                                     VARCHAR2(19)