Oracle442 application scenarios ---------- database logical Object Management

Oracle442 application scenarios ---------- database logical Object Management

----------------- Database logical Object Management --------------------

ORACLE basic data types (also called built-in data types built-in datatypes) can be divided: string type, numeric type, date type, LOB type, long raw & RAW type, ROWID & UROWID type.

Before describing the string type, you must first talk about encoding. String data can be encoded into two types: database character set (CHAR/VARCHAR2/CLOB/LONG) and international character set (NCHAR/NVARCHAR2/NCLOB. String data in the database is stored in data blocks only after the characters are converted to digits (Binary) by character sets. Through conversion of different character sets, even the same characters may be converted into different binary codes. This is also the cause of garbled characters. The encoding format of the database is generally specified when the database is created. Of course, you can also modify the database encoding.

View the Data Types contained in the database view: SELECT * FROM DBA_TYPES where owner is null. For details, see Oracle? Database SQL Language Quick Reference 10/11g or official documentation

 

 

1. string type

String data types can also be divided into fixed-length types (CHAR/NCHAR) and variable-length types (VARCHAR2/NVARCHAR2) based on the storage space.

The so-called Fixed Length: although the input field value is less than the limit length of the field, but the actual storage of data, will first automatically fill in the right after spaces, the content of the field value is stored in the data block. This method is a waste of space, but the storage efficiency is better than the variable length type. At the same time, it can reduce the occurrence of row migration.

Variable Length: when the input field value is smaller than the maximum length of the field, the content of the field value is directly stored in the data block without filling in the blank space, this saves block space.

1.1: CHAR (size [BYTE | CHAR])

CHAR type, fixed length string, will be filled with spaces to reach its maximum length. A non-null char (12) always contains 12 bytes of information. The CHAR field can store up to 2,000 bytes of information. If the CHAR length is not specified during table creation, the default value is 1. In addition, you can specify the storage byte or character, such as CHAR (12 BYTYE) CHAR (12 CHAR). Generally, the storage byte is used by default. You can view the database parameters.

Note: the database's NLS_CHARACTERSET is AL32UTF8, meaning that a Chinese character occupies three to four bytes. If the NLS_CHARACTERSET is ZHS16GBK, one character occupies two bytes.

If the length of a string is less than or equal to 250 (0x01 ~ 0xFA), Oracle uses 1 byte to represent the length. For all strings with a length greater than 250, the length is indicated by a flag byte 0xFE followed by two bytes. Therefore, if there is a VARCHAR2 (80) that contains "Hello World", the block may be 12.-1

Clip_image002

1.2: NCHAR type

This is a fixed-length string that contains UNICODE data. The NCHAR field can store up to 2,000 bytes of information. Its maximum length depends on the national character set. In addition, if the field is NCHAR type during query, you need to write the following

SELECT translated_description FROM product_descriptions

WHERE translated_name = n' LCD Monitor 11/PM ';

1.3 VARCHAR type

Do not use the VARCHAR data type. Use the VARCHAR2 data type. Although the VARCHAR data type is currently synonymous with VARCHAR2, The VARCHAR data type is scheduled to be redefined as a separate data type for Variable Length strings, with different comparative semantics.

1.4: VARCHAR2 type

Variable-length string. Unlike CHAR, it does not use spaces to fill in the maximum length. VARCHAR2 can store up to 4,000 bytes of information.

1.5: NVARCHAR2 type

This is a variable-length string that contains UNICODE data. NVARCHAR2 can store up to 4,000 bytes of information.

 

 

Ii. digit type

 

2.1 NUMBER type

NUMBER (P, S) is the most common numeric type and can be stored in the range of 10 ^ 130 ~ 10 ^ 126 (excluding this value), 1 ~ 22 bytes (BYTE) of storage space.

P is the abbreviation of Precison, that is, the abbreviation of precision, indicating the number of digits of a valid number, up to 38 valid digits

S is the abbreviation of Scale, which can be in the range of-84 ~ 127. When Scale is a positive number, it indicates the number of digits from the decimal point to the lowest valid number. When it is a negative number, it indicates the number of digits from the maximum valid number to the decimal point.

2.2 INTEGER type

INTEGER is a subtype of NUMBER. It is equivalent to NUMBER () and is used to store integers. If the inserted or updated values have decimal places, the values are rounded in.

For example:

CREATE TABLE TEST

(

ID INTEGER

)

The DDL definition of the TEST table is as follows:

Create table "SYS". "TEST"

("ID" NUMBER (*, 0)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM ";

INSERT INTO TEST

SELECT 12.34 from dual;

INSERT INTO TEST

SELECT 12.56 from dual;

SQL> SELECT * FROM TEST;

ID

----------

12

13

2.3 floating point number

A floating point number can have a decimal digit ranging from the first digit to the last digit, or it can have any decimal point. An index may (optional) be used to increase the following quantity (for example, 1.777e-20 ). The scale value is not applicable to floating-point numbers because the number of digits that can be displayed after the decimal point is unlimited.

Different numbers of binary floating point values are stored in the Oracle database. Store values with decimal precision. The storage range and quantity of identical numbers are determined by the supported precision of all texts. It is precisely because the decimal precision (numbers 0 to 9) is used to represent text storage. Uses binary precision (numbers 0 and 1) to store binary floating point numbers. This storage solution cannot represent all exact decimal precision values. Frequently, if an error occurs when you convert a value from decimal to binary, undo the value and convert it back to decimal. In literal 0.1, this is an example.

The Oracle database provides two numeric data types specifically for floating point numbers:

BINARY_FLOAT

BINARY_FLOAT is a 32-bit, single-precision floating point digital data type. It can support at least 6-bit precision. Each BINARY_FLOAT value requires 5 bytes, including length bytes.

BINARY_DOUBLE

BINARY_DOUBLE is a 64-bit double-precision floating-point numeric data type. The value of each BINARY_DOUBLE must be 9 bytes in length.

In the numeric column, floating point numbers have decimal precision. In the BINARY_FLOAT or BINARY_DOUBLE columns, floating point numbers have binary precision. The special values supported by binary floating point numbers are infinite and NaN (not numbers ).

You can specify a floating point number in the range of 2-4 in the table. "Numeric text" defines the format used to specify floating point numbers.

Table 2-3 Floating Point Number Limits

Value

Binary-Float

Binary-Double

Maximum positive finite value

3.40282E + 38F

1.79769313486231E + 308

Minimum positive finite value

1.17549E-38F

2. 22507485850720e-308

2.5 FLOAT Type

FLOAT is also a child type of NUMBER.

Float (n), number n indicates the precision of the bit, the number of values that can be stored. The value of N ranges from 1 to 126. To convert data from binary to decimal, multiply n by 0.30103. To convert data from decimal to binary, use the decimal precision of 3.32193. The maximum binary precision of 126 bits is about 38 decimal places.

 

Iii. date type

The date type is used to store date data, but it is not stored directly to the database in the normal format.

3.1 DATE type

DATE is the most common data type. It stores DATE and time information. Although date and time information can be represented by characters or numbers, the date data type has special Association attributes. For each date value, Oracle stores the following information: century, year, month, date, hour, minute, and second. It generally occupies 7 bytes of storage space.

3.2 TIMESTAMP type

This is a 7-byte, 12-byte, fixed-width date/time data type. It is different from the DATE data type, because the TIMESTAMP can contain decimal seconds, and the TIMESTAMP with decimal seconds can retain a maximum of 9 digits on the right of the decimal point

3.3 timestamp with time zone Type

This is a variant of the TIMESTAMP type. It contains the value of the time zone offset.

3.4 timestamp with local time zone type

3.5 INTERVAL YEAR TO MOTH

3.6 INTERVAL DAY TO SECOND

 

Iv. LOB type

Built-in LOB data types include BLOB, CLOB, NCLOB, and BFILE (External Storage) for large-scale and unstructured data, such as text, images, visual screens, and space data storage. BLOB, CLOB, and NCLOB types

 

4.1 CLOB Data Type

It stores single-byte and multi-byte character data. Supports character sets with fixed width and variable width. CLOB objects can store up to (4 gigabytes-1) * (database block size) characters

4.2 NCLOB Data Type

It stores UNICODE data and supports character sets of fixed width and variable width. NCLOB objects can store up to (4 gigabytes-1) * (database block size) text data.

4.3 BLOB Data Type

It stores unstructured binary data big objects. It can be considered as a bit stream without character set semantics, generally images, sounds, videos, and other files. BLOB objects can store up to (4 gigabytes-1) * (database block size) binary data.

4.4 BFILE Data Type

Binary file, a system file stored outside the database, read-only. The database treats the file as a binary file.

5. RAW & long raw type

 

5.1 LONG type

 

It stores variable-length strings, up to 2 GB of character data (2 GB refers to 2 Gigabit bytes rather than 2 Gigabit characters), the same as the VARCHAR2 or CHAR type, text stored in the LONG type must undergo Character Set conversion. We recommend that you use CLOB to replace the LONG type in ORACLE development. LONG columns are supported only to ensure backward compatibility. The CLOB type is much less restrictive than the LONG type. The restrictions of the LONG type are as follows:

1. Only one column in a table can be of the LONG type. (Why? Some do not understand)

2. The LONG column cannot be defined as a primary key or a unique constraint,

3. indexes cannot be created.

4. The regular expression cannot be specified for LONG data.

5. Parameters of the LONG data type cannot be accepted in functions or stored procedures.

6. The LONG column cannot appear in the WHERE clause or Integrity Constraint (except for the possible NULL and not null constraints)

 

It is used to store binary or character-type data and variable-length binary data. This indicates that data stored in this data type will not undergo Character Set conversion. This type can store up to 2,000 bytes of information.

 

Vi. ROWID & UROWID type

Each row in the database has an address. However, the addresses of some table rows are not physical or permanent, or are not generated by the ORACLE database.

For example, the index organization table row address is stored in the index leaf and can be moved.

For example, the ROWID of an External table (for example, accessing a DB2 table through a gateway) is not ?? The rowid of the standard ORACLE.

ORACLE uses the General ROWID (UROWIDs) storage address to index the table and External table. The index organization table has the urowids logic, and the urowids outside the country's external table ,. The two types of UROWID are stored in the ROWID pseudo (physical row id of the table in the heap organization ).

Create a primary key for the logical rowid in the table. The logical rowid does not change as long as the primary key does not change. The ROWID pseudo UROWID Data Type of the index organization table. You can access this pseudo column, and you will heap the row ID of the Organization table (that is, use a SELECT... ROWID Statement ). If you want to store rowid indexes to organize tables, you can define a column's phenotype urowid to the rowid pseudo of the column's retrieved value.

 

Table Constraints

 

Oracle restrictions

 

Constraints are some rules defined in the table to maintain database integrity. Define the conventions for fields in the table

To avoid inserting incorrect data into the table.

Note:

1. if a constraint only applies to individual fields, you can define the constraint at the field level or at the table level. However, if a constraint applies to multiple fields, constraints must be defined at the table level.

2. Constraints in oracle are identified by names. When defining constraints, you can use the constraint keyword to name constraints. If you do not specify a name for the constraint, oracle automatically creates a default name for the constraint.

Primary key constraint (primary key ):

Primary Key constraints:

Fields defined as primary key constraints cannot contain duplicate values, and cannot contain null values.

Only one primary key constraint can be defined in the same table.

You can define a primary key constraint for a field or a combination of multiple fields.

Oracle automatically creates one unique index and one non-empty constraint for fields with primary key constraints.

For example:

Create table person

(

P_id int primary key, -- defines this field as a primary key constraint

P_name varchar2 (20 ),

P_age int

-- Constraint p_pk primary key (p1_id)

);

Create table person

(

P_id int constraint p_pk primary key, -- defines this field as a primary key constraint and specifies the constraint name

P_name varchar2 (20 ),

P_age int

-- Constraint p_pk primary key (p1_id)

);

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int,

Constraint p_pk primary key (p_id, p_name) -- Define the composite primary key and specify the name

-- Primary key (p_id, p_name)

);

 

Non-null constraint (not null ):

Non-empty constraints:

Fields that define non-null constraints cannot contain null values.

You can only define non-null constraints at the field level.

Multiple non-empty constraints can be defined in the same table.

For example:

Create table person

(

P_id int,

P_name varchar2 (20) not null, -- defines non-null constraints for this column

P_age int

);

 

Unique constraint (unique ):

Unique constraints:

Fields defining unique constraints cannot contain duplicate values.

You can define a unique constraint for one field or a combination of multiple fields. Therefore, the unique constraint can be defined either at the field level or at the table level.

Oracle automatically creates a unique index for fields with unique constraints.

You can define non-null and unique constraints for the same field at the same time.

If only a unique constraint is defined on one field, but no non-null constraint is defined, this field can contain multiple null values.

For example:

Create table person

(

P_id int,

P_name varchar2 (20) unique, -- defines the unique constraint of this column

P_age int

);

Create table person

(

P_id int,

P_name varchar2 (20) constraint p_un unique not null, -- Define unique and non-empty constraints at the same time

P_age int

);

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int,

Constraint p_un unique (p_name) -- specify the constraint name

);

 

Foreign key constraint (foreign key)

Foreign key constraints:

Fields defined as foreign key constraints can only contain values or null values of referenced fields in other tables.

You can define a foreign key constraint for one field or a combination of multiple fields.

Fields defining foreign key constraints and corresponding reference fields can exist in the same table, which is called self-reference.

You can define both foreign keys and non-null constraints for the same field.

The referenced columns in the primary table must have primary key constraints or unique constraints.

For example:

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int,

W_id int,

Constraint p_fk foreign key (w_id) references works (w_id) -- foreign key constraint

);

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int,

W_id int constraint p_fk references works -- foreign key constraint, reference the primary key in the works table

);

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int,

W_id int references works -- foreign key constraint

-- W_id int constraint w_fk references works (w_id)

);

When defining foreign key constraints, you can also use the on keyword to specify the type of reference behavior. When a record in the primary table is deleted, you must use the reference behavior to determine how to handle the value of the foreign key column in The subtable.

Delete cascade ),

Set the foreign key value of all related records to null (delete set null)

Check Constraints)

Check constraints:

The expression that checks the constraints must reference one or more fields in the table, and the calculation result of the expression must be a Boolean value.

The expression cannot contain subqueries.

The expression cannot contain SQL functions such as sysdate, uid, user, userenv, or pseudo columns such as rowid and rownum.

Check constraints can be defined at the field level and table level.

Multiple check constraints can be defined for the same field, and both check constraints and non-empty constraints can be defined for the same field.

For example:

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int check (p_age> 20) -- check Constraints

-- Constraint p_check check (p_age> 20) -- check Constraints

);

Create table person

(

P_id int,

P_name varchar2 (20 ),

P_age int constraint p_check check (p_age> 20) -- check Constraints

-- Constraint p_check check (p_age> 20) -- check Constraints

);

 

Add Constraints

If UNIQUE, primary key, foreign key, and CKECK are added, the ADD clause of the alter table statement must be used;

If the not null constraint is added, the MODIFY clause of the alter table statement must be used, for example:

Alter table table_name ADD [CONSTRAINT constraint_name]

Constraint_type (column ,...)

Alter table table_name MODIFY column

[CONSTRAINT constraint_name] not null;

 

Add foreign key constraints

Alter table emp add foreign key (deptno) references dept (deptno );

Alter table emp add constraint d_fk foreign key (deptno) references dept (deptno );

Add primary key constraints

Alter table table_name add primary key (column_name );

Alter table table_name add constraint t_pk primary key (column_name );

Add non-empty Constraint

Alter table person modify p_name not null;

Alter table person modify p_name constraint t_notnull not null;

Constraint delay (re-verify when the transaction is committed, no delay by default)

Deferrable -- latency Verification

Not deferrable -- no delay verification (default)

The meaning of a separate deferrable is whether the constraint check is allowed to be postponed. When deferrable is set to deferrable separately, the restriction check delay cannot be used directly. The constraint must be used with the deferred parameter. If this parameter is immediate, the constraint is applied in DML. If the deferred parameter is set to deferred, the constraint is applied when the transaction is committed. If an error occurs, the entire transaction is rolled back.

When creating constraints, you can specify the Enable Delay

Create table tt

(

Id int primary key deferrable initially deferred

);

Set constraints constraint name immediate; Disable constraint Delay

Set constraints constraint name deferred; Enable constraint Delay

You can also set it at the session level.

Alter session set constraints = immediate;

Alter session set constraints = deferred;

Rename Constraints

Alter table name rename constraint original constraint name to new constraint name

Delete Constraints

Alter table Name drop constraint name;

Alter table Name drop constraint name cascade ;-? -When a primary key is deleted, the foreign key constraint of the child table is deleted in cascade mode.

Constraint invalid

Alter table name disable constraint name

Alter table name disable constraint name cascade; -- when the primary key fails, the foreign key constraint of the child table is deleted cascade.

Constraints take effect

Alter table name enable constraint name

Novalidate: -- Do not verify old data

Validate: -- verify old data

Controls whether existing data has application constraints, such

Alter table t2 deferrable enable novalidate constraint SYS_C007211; -- the constraint takes effect and does not verify that the existing data meets the constraints.

 

Alter table t2 enable validate constraint SYS_C007211; -- the constraints take effect and check whether the existing data meets the constraints.

 

Alter table t2 disable validate constraint SYS_C007211;

-- Constraint failure, while verifying that the existing data meets the constraints, after the table cannot be dml operation, otherwise the ORA-25128 error will be reported

 

Alter table t2 disable novalidate constraint SYS_C007211;

-- The constraint is invalid. Check whether the existing data meets the constraints (default)

 

Note: operations after the constraint takes effect (enable) are restricted.

 

Query Constraints

View Name Description

All_constraints contains the basic description of all constraints, including the constraint name, type, status, latency, and other information.

Dba_constraints

User_constraints

All_cons_columns contains the field information that defines the course constraints. This view can be used to view the fields defined by the constraints.

Dba_cons_columns

User_cons_columns

 

Index

In oracle, an index is a database structure that allows the server to quickly search for a row in a table. Creating an index in a database has the following functions.
(1) quick access to data.
(2) It can improve the database performance and ensure the uniqueness of column values.
(3) implement integrity of reference between tables
(4) When using orderby and groupby clauses for data retrieval, using indexes can reduce the time for sorting and grouping.

Indexing principles
In a relational database, each row is uniquely identified by a row RowID. RowID includes the file where the row is located, the number of blocks in the file, and the row number in the block. An index contains an index entry. Each index entry has a key value and a RowID. The key value can be a combination of one or more columns.
(1) indexes are classified by storage method. They can be classified into B * tree indexes and bitmap indexes.
(1) B * The storage structure of tree indexes is similar to the index structure of books. There are two types of storage data blocks: branch and leaf. The branch block is equivalent to a large directory of books, A leaf block is equivalent to a specific index page. Oracle uses the B * tree mechanism to store index entries to ensure that the key value is accessed using the shortest path. By default, most B * tree indexes are used. This index is the unique and reverse index that is usually seen.
(2) bitmap index storage is mainly used to save space and reduce oracle access to data blocks. It uses bitmap offset to correspond to the table's row ID. The bitmap index is generally a table field with too many duplicate values. Bitmap indexes are rarely used in actual intensive OLTP (online transaction processing) Because OLTP deletes, modifies, and creates a large number of tables. Each Oracle operation locks the data block to be operated. To prevent database lock waits or deadlocks that are easily caused by multi-user operations. It is advantageous to apply bitmap in OLAP (Online Analytical Processing) because most of OLAP queries databases and generally uses data warehouse technology, therefore, the use of Bitmap indexes for a large amount of data saves much space. When the command for creating a table contains a unique keyword, bitmap indexes cannot be created and global partition indexes cannot be used.
(2) There are also the following types of indexes by function and index object.
(1) A unique index means that no two rows record the same index key value. The records in the unique index table do not have RowID, and no other indexes can be created for the records. In oracle10g, to create a unique index, you must set the primary keyword in the Table. tables with a unique index are only sorted by the unique index structure.
(2) Non-unique indexes do not restrict the uniqueness of index column values.
(3) Partition index refers to the index that can be scattered in multiple different tablespaces. Its advantage is that it can improve the efficiency of data query.
(4) unordered indexes are also called forward indexes. The rows in the Oracle10g Database are sorted in ascending order. You do not have to specify the sorting order when creating an index, but use the default order.
(5) reverse indexes are also called reverse indexes. This index also keeps the columns in order, but reverses the bytes of each indexed column.
(6) A function-based index means that one or more columns in an index are a function or expression. The index calculates the value of the index Column Based on the function or expression. You can create a bitmap index based on a function.
In addition, indexes can be divided into single-column indexes and composite indexes based on the number of columns contained in the indexes. Only one index in an index column is a single index. A compound index is used for simultaneous indexing of multiple columns.

 

3. Edit indexing principles

If indexes are correctly used, indexes can speed up searching corresponding tables. When you consider using indexes in a table, you should follow the following basic principles.
(1) Insert data into the table and create an index. After data is inserted into a table, the efficiency of creating indexes is higher. If an index is created before data is loaded, the index must be changed for each row inserted in oracle.
(2) Correct tables and columns are indexed. If you frequently search for rows in a table that contains a large amount of data smaller than 15%, you need to create an index. To improve the relationship between multiple tables, index columns are often used for relational join.
(3) The primary key and the column with the unique keyword automatically have an index, but an index should be created on the column where the external keyword in the associated table is located.
(4) Reasonably arrange the index columns. In the createindex statement, column sorting affects the query performance. Generally, the most common columns are placed in front. When creating an index to improve the query efficiency of multiple columns, you should be clear about which columns the index can access and which columns the access to is invalid.
For example, create an index on columns A, B, and C.
A valid
AB valid
ABC valid
(5) Limit the number of indexes in the table. Although a table can have any number of indexes, the more indexes you need, the more work you need to change the indexes when modifying the data in the table, and the lower the efficiency. Similarly, unused indexes should be deleted in a timely manner.
(6) specify the use of index data block space. When an index is created, the index data block is filled with the existing values in the table until PCTFREE is reached. If you want to insert many rows into the indexed table, PCTFREE should be set to a higher value. You cannot specify PCTUSED for the index.
(7) set storage Parameters Based on the index size. Before creating an index, you must estimate the size of the index to facilitate disk space planning and management. The maximum value of a single index is about half the size of the data block.

 

Clusters and cluster tables

 

A cluster is actually a group of tables that share the same data block. Combining frequently used tables into clusters can improve processing efficiency.

 

A table in a cluster is called a cluster table. The order of creation is: Cluster → cluster Table → data → Cluster Index

 

1. cluster creation format

 

Create cluster cluster_name
(Column date_type [, column datatype]...)
[PCTUSED 40 | integer] [PCTFREE 10 | integer]
[SIZE integer]
[INITRANS 1 | integer] [MAXTRANS 255 | integer]
[TABLESPACE tablespace]
[STORAGE storage]

SIZE: Specifies the average cluster key and the number of bytes required for the related rows.

2. Create a cluster

Create cluster my_clu (deptno number)

Pctused 60

Pctfree 10

Size 1024

Tablespace users

Storage (

Initialize 128 k

Next 128 k

Minextents 2

Maxextents 20

);

3. Create a cluster Table

Create table t1_dept (

Deptno number,

Dname varchar2 (20)

)

Cluster my_clu (deptno );

 

Create table t1_emp (

Empno number,

Ename varchar2 (20 ),

Birth_date date,

Deptno number

)

Cluster my_clu (deptno );

4. Create an index for the cluster

Create index clu_index on cluster my_clu;
NOTE: If no index is created, an error is reported during data insertion: ORA-02032: clustered tables cannot be used before the cluster index is built

Manage Clusters

Use ALTER to modify CLUSTER attributes (you must have the alter any cluster permission)

1. Modify cluster attributes

Attributes of a cluster that can be modified include:

* PCTFREE, PCTUSED, INITRANS, MAXTRANS, and STORAGE

* To store the average SIZE of space required for all rows of the cluster key value

* Default degree of Parallelism

Note:

* INITIAL and MINEXTENTS values cannot be modified.

* The modified PCTFREE, PCTUSED, and SIZE parameters apply to all data blocks.

* INITRANS and MAXTRANS are only applicable to data blocks allocated in the future.

* After the STORAGE parameter is modified, only the disks allocated to the cluster will be affected.

Format:

Alter cluster my_clu

Pctused 40

2. delete a cluster

Drop cluster my_clu; -- applicable only to deleting empty Clusters

Drop cluster my_clu including tables; -- delete clusters and cluster tables

Drop cluster my_clu including tables cascade constraints;

-- Delete foreign key constraints at the same time

Note: A cluster table can be deleted as a normal table.

Hash clustering table

In a cluster Table, Oracle uses the key values stored in the index to locate the rows in the table. In a hash cluster Table, the hash function replaces the Cluster Index, hash calculation is performed by using internal functions or custom functions, and then the calculated code value is used to locate rows in the table. The HASHKEYS clause is required to create a hashed cluster.

1. Create a hashed Cluster

Create cluster my_clu_two (empno number (10 ))

Pctused 70

Pctfree 10

Tablespace users

Hash is empno

Hashkeys 150;

Note:

* The hash is clause specifies the columns to be hashed. If the column is a unique row, you can specify the column as the hash value.

* Hashkeys specifies and limits the number of unique hash values that a hash function can generate.

 

2. Create a hash

Create table t2_emp (

Empno number (10 ),

Ename varchar2 (20 ),

Birth_date date,

Deptno number)

Cluster my_clu_two (empno );

Note:

* The precision of the value must be set (the specific reason is unknown)

* You cannot create indexes for hashed clusters.

* The hash clustering cannot be ALTER: size, hashkeys, or hash is parameters.

 

Application Scenario 126: Create a table in Enterprise Manager

Scenario 127: Use the create table statement to CREATE a TABLE

Create an Employee table

Create table hrman. Employees
(
Emp_id NUMBER,
Emp_name VARCHAR2 (50) not null,
Sex VARCHAR2 (2 ),
Title VARCHAR2 (50 ),
Wage NUMBER (8, 2 ),
IdCard VARCHAR2 (20 ),
Dep_id NUMBER
);