---

Primary key

 

primary key is a single field or combination of fields that uniquely defines a record.

None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

In Oracle, a primary key can not contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

----------

A primary key itself is not an index, and nor is a unique constraint -- they are both constraints. However an index is used to support them.

A unique index is rather different as it can exist in the absence of a unique or primary key constraint,

and neither constraint type require that the index supporting it be unique.

==========

This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation.

To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.
 

Having a small value for maxextents for tables and indexes can cause unplanned outages and many DBA's set all objects to "maxextents unlimited", and all data files to "autoxtend on" (the default with later releases of Oracle).

============

PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow. The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.

Preventing tablespace fragmentation
Always set PCTINCREASE to 0 or 100. Bizarre values for PCTINCREASE will contribute to fragmentation. For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.

Locally Managed tablespaces (available from Oracle 8i onwards) with uniform extent sizes virtually eliminates any tablespace fragmentation. Note that the number of extents per segment does not cause any performance issue anymore, unless they run into thousands and thousands where additional I/O may be required to fetch the additional blocks where extent maps of the segment are stored.

 

ALTER TABLE reimburse_detail
  ADD CONSTRAINT my_pk PRIMARY KEY (
    myid,
    myid_seq
  )
  USING INDEX
    TABLESPACE otsspace1_idx
    STORAGE (
      INITIAL    2880 K
      NEXT        948 K
      MAXEXTENTS  121
      PCTINCREASE   0
    )

 

---

Create table

 

CREATE TABLE mytable (
  myid        NUMBER(10,0) NOT NULL,
)

 

For numeric columns, you can specify the column as:

column_name NUMBER
Optionally, you can also specify a precision (total number of digits) and
scale (number of digits to the right of the decimal point):

column_name NUMBER (precision, scale)

If a precision is not specified, the column stores values as given.
If no scale is specified, the scale is zero.

Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:

column_name NUMBER (*, scale)
In this case, the precision is 38, and the specified scale is maintained.

7,456,123.89

NUMBER

7456123.89
--------
7,456,123.89

NUMBER(*,1)

7456123.9
--------
7,456,123.89

NUMBER(9)

7456124
--------
7,456,123.89

NUMBER(9,2)

7456123.89

7,456,123.89

NUMBER(9,1)

7456123.9

7,456,123.89

NUMBER(6)

(not accepted, exceeds precision)

=================

SELECT [columns]
FROM table t1
WHERE value= (select max(value) from table)

=================

SELECT row_name FROM A.B where rownum <= 100;

=================

Oracle: ROWNUM could be used.

SELECT Max(reimb_seq) FROM A.B;

雙條件

select * from (select * from table
order by value desc, date_column)
where rownum = 1;

=================

Pattern-matching Conditions

last_name LIKE 'Ma%'

last_name NOT LIKE 'Ma%'

=================

Sort a varchar2 field as a numeric field

sort chars in varchar2 alphabetically

=================

substr( string, start_position, [ length ] )

Start with 1