Oracle Concepts - 2 Tables and Table Clusters

1. To complete these task download this file 

http://download.oracle.com/otn/other/virtualbox/dd/OTN_Developer_Day_VM.ova

This virtual machine contains:

Oracle Linux 6.5
Oracle Database 12c Release 1 Enterprise Edition
Oracle XML DB
Oracle SQL Developer
Oracle SQL Developer Data Modeler
Oracle Application Express
Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

2. Download and install Oracle Virtual Box 

https://www.virtualbox.org/wiki/Downloads

3. import virtual machine to your VirtualBox 

Menu File > Import Appliance > select downloaded file 'OTN_Developer_Day_VM.ova'

most password is 'oracle'

4. you may change network settings in this virtual machine to you can access from outside and by add private network interface to VirtualBox 

- Login as sys user
$ sqlplus sys/oracle as sysdba

- show Database, Instance status,version and edition
PDB1@ORCL> select instance_name, status,database_status,version,edition from v$instance;

INSTANCE_NAME STATUS       DATABASE_STATUS VERSION  EDITION
---------------- ------------ ----------------- ----------------- -------
orcl OPEN      ACTIVE 12.1.0.1.0  EE

- Determining Whether a Database is a CDB
PDB1@ORCL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID
-------- --- ----------
ORCL YES      0

-  Identifying Information About Each Container in a CDB
PDB1@ORCL> COLUMN NAME FORMAT A8
PDB1@ORCL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME     CON_ID  DBID  CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
PDB1  3 3345156736 3345156736 F0832BAF14721281E045000000000001

- Viewing the Name and Open Mode of Each PDB
PDB1@ORCL> COLUMN NAME FORMAT A15
PDB1@ORCL> COLUMN RESTRICTED FORMAT A10
PDB1@ORCL> COLUMN OPEN_TIME FORMAT A30
PDB1@ORCL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

NAME OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB1 READ WRITE NO      12-SEP-14 11.20.54.079 AM

- create user "dba1" with tablespace "sales" in datafile "sales01.dbf"

PDB1@ORCL> create tablespace sales datafile '/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/sales01.dbf'

  2  size 100M;

Tablespace created.

PDB1@ORCL> create user dba1 identified by oracle

  2  default tablespace sales

  3  temporary tablespace temp

  4  account unlock;

User created.

PDB1@ORCL> alter user dba1 quota unlimited on sales;

User altered.

PDB1@ORCL> grant unlimited tablespace to dba1;

Grant succeeded.

PDB1@ORCL> grant connect, resource to dba1;

Grant succeeded.

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

Tables

Tables

• Hold relation data

• Composed of rows and columns
• Row – individual instances of information
• Columns – individual types of information
• Referred to as 'heap organized' table
• Datatypes
  • CHAR – fixed width of character
  • VARCHAR2 – variable width of character
  • Number(p,s)
    • precision(max number of digits)
    • scale (number of digits to the right)
    • Eg. NUMBER(5.2) = 456.78 But NOT 4567.8
  • DATE – containing date and time information
• Tables and column Naming Rules
  • Names between 1 and 30 characters
  • only alphanumeric and #,_,$
  • Must begin with letter between A and Z
  • Reserved words can't be used
  • Most of these rules can be overridden by using
• CREATE TABLE syntax

CREATE TABLE table_name (

column 1 datatype,

column 2 datatype,

…...

column datatype

);

SQL> create table test_table (

column1 char(5),

column2 varchar2(5),

column3 number(5,2),

column4 date

);

desc test_table;

select * from test_table;

Temporary Tables

Temporary Tables

• Temporary table is a transient table
• table visible to all sessions, but the data is not
• create GLOBAL TEMPORARY TABLE … ON COMMIT
• DELETE ROWS - specific to a transaction
• PRESERVE ROWS - specific to a session
• stored in different segment from regular data in temp tablespace 

### Using SQL Developer

-- 10_03 temporary tables - on commit delete rows 

create global TEMPORARY table test_temp3 (

  col1 number (3),

  col2 varchar2(10)

)

ON COMMIT delete rows;

select * FROM test_temp3;

insert into test_temp3 values (3, 'TEST3');

select * FROM test_temp3;

commit;

select * FROM test_temp3;

-- on commit preserve rows 

create global TEMPORARY table test_temp4 (

  col1 number (3),

  col2 varchar2(10)

)

ON COMMIT PRESERVE rows;

select * FROM test_temp4;

insert into test_temp4 values (4, 'TEST4');

select * FROM test_temp4;

commit;

select * FROM test_temp4;

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

- schema 

 a logical container for data structures, called schema objects
.
> Examples of schema objects are tables and indexes
> Each user owns a single schema
> schema contains the data for the user owning the schema
> schema owner usually represents a database application rather than a person
> each schema object of a particular type has a unique name

- Schema Object Types

1. Tables - most important schema objects in a relational database, stores data in row (tuples)

2. Indexes - schema objects that contain an entry for each indexed row of the table or table cluster and provide direct, fast access to rows

3. Partitions - pieces of large tables and indexes. Each partition has its own name and may optionally have its own storage characteristics

4. Views - customized presentations of data in one or more tables or other views. Views do not actually contain data

5. Sequence - a user-created object that can be shared by multiple users to generate integers

6. Dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. Dimensions are commonly used to categorize data such as customers, products, and time

7. Synonym is an alias for another schema object. Because a synonym is simply an alias

8. PL/SQL subprograms and packages
8.1. PL/SQL subprogram - named PL/SQL block that can be invoked with a set of parameters
8.2. PL/SQL package - groups logically related PL/SQL types, variables, and subprograms

Other types of objects are also stored in the database and can be created and manipulated with SQL statements but are not contained in a schema. These objects include database users, roles, contexts, and directory objects.

- Schema Object Storage

- Some schema objects store data in logical storage structures called segments.

- Other schema objects, such as views and sequences, consist of metadata only.

- no relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces, data of each object is physically contained in one or more data files. The data segment for one table spans two data files, which are both part of the same tablespace. A segment cannot span multiple tablespaces

- Schema Object Dependencies:

> view contains a query that references tables or views
> PL/SQL subprogram invokes other subprograms
> Oracle Database ensure that a dependent object is always up to date with respect to its referenced objects

- SYS and SYSTEM Schemas

>  SYS & SYSTEM account is also automatically created when a database is created
> account SYS is automatically created when a database is created
> SYS schema stores the base tables and views for the data dictionary
> SYS schema are manipulated only by the database and must never be modified by any user
> SYSTEM schema stores additional tables and views that display administrative information
> Never use the SYSTEM schema to store tables of interest to nonadministrative users.

- Sample Schemas
> set of interlinked schemas that enable Oracle documentation and Oracle instructional materials to illustrate common database tasks

-  Table is the basic unit of data organization in an Oracle database. A table describes an entity

- Oracle Database tables fall into the following basic categories:

a. Relational tables
Relational tables have simple columns and are the most common table type.

b. Object tables
The columns correspond to the top-level attributes of an object type.

- Create a relational table with the following organizational characteristics:

1. heap-organized table does not store rows in any particular order. The CREATE TABLE statement creates a heap-organized table by default.

2. index-organized table orders rows according to the primary key values.

3. external table is a read-only table whose metadata is stored in the database but whose data is stored outside the database.

- table is either permanent or temporary

• temporary table definition persists in the same way as a permanent table definition, but the data exists only for the duration of a transaction or session 
• permanent table definition and data persist across sessions

- Column - identifies an attribute of the entity described by the table

- Virtual Column - does not consume disk space, set of user-specified expressions or functions

- Invisible Column - user

- Invisible Column - user-specified column whose values are only visible when the column is explicitly specified by name, help migrate and evolve online applications (New  with 12c )

for more http://www.juliandyke.com/Blog/?p=419

example:

CREATE TABLE products ( prod_id INT, count INT INVISIBLE );
ALTER TABLE products MODIFY ( count VISIBLE );


PDB1@ORCL> CREATE TABLE products ( prod_id INT, count INT INVISIBLE );

Table created.

PDB1@ORCL> alter table products modify (count visible);

PDB1@ORCL> SET COLINVISIBLE ON

PDB1@ORCL> desc products
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID    NUMBER(38)
 COUNT    NUMBER(38)

PDB1@ORCL> alter table products modify (count invisible);

Table altered.

PDB1@ORCL> desc products
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID    NUMBER(38)
 COUNT (INVISIBLE)    NUMBER(38)


- Row - a collection of column information corresponding to a record in a table

Example 2-1 CREATE TABLE employees

CREATE TABLE employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
         CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
        CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
        CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
        CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;

Example 2-2 ALTER TABLE employees

ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pk
                       PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_dept_fk
                       FOREIGN KEY (department_id)
                         REFERENCES departments
    , CONSTRAINT     emp_job_fk
                       FOREIGN KEY (job_id)
                         REFERENCES jobs (job_id)
    , CONSTRAINT     emp_manager_fk
                       FOREIGN KEY (manager_id)
                         REFERENCES employees
    ) ;

[oracle@localhost ~]$ lsnrctl start

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> startup

ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size    1344840 bytes
Variable Size  348129976 bytes
Database Buffers  100663296 bytes
Redo Buffers    6008832 bytes
Database mounted.
Database opened.

SQL> connect hr/oracle

SQL> desc employees

SQL> select employee_id, first_name,last_name, salary, department_id from employees;

- some of the following important characteristics of tables, columns, and rows:

• primary key, which means that every employee is uniquely identified by employee ID 
• non-key column can contain rows with identical values. like salary 
• foreign key column refers to a primary or unique key in the same table or a different table. department_id corresponds to the department_id column of the departments table 
• field is the intersection of a row and column. 
• column allows nulls unless a NOT NULL or primary key integrity constraint has been defined on this column

Oracle Data Types

- data type, which is associated with a specific storage format, constraints, and valid range of values.

Some of Oracle's built-in data types are:

• NUMBER
• CHAR
• NCHAR
• VARCHAR2
• NVARCHAR2
• DATE
• TIMESTAMP
• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND
• BLOB - Binary Large Object
• CLOB - Character Large Object
• NCLOB
• BFILE - locator to external binary file
• XMLType - Storage of XML data
• RAW
• LONG
• LONG RAW

Abstract datatypes can also be defined.

Rowid Data Types

Every row stored in the database has an address. Oracle Database uses a ROWID data type to store the address (rowid) of every row in the database. Rowids fall into the following categories:

• Physical rowids store the addresses of rows in heap-organized tables, table clusters, and table and index partitions.
• Logical rowids store the addresses of rows in index-organized tables.
• Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.

A data type called the universal rowid, or urowid, supports all types of rowids.

SQL> select ROWIDTOCHAR(rowid) from dual;
ROWIDTOCHAR(ROWID)

SQL> select * from dual where rowid = CHARTOROWID('AAAAECAABAAAAgiAAA');

Use of Rowids

• Rowids are the fastest means of accessing particular rows.
• Rowids provide the ability to see how a table is organized.
• Rowids are unique identifiers for rows in a given table.

ROWID Pseudocolumn

Every table in an Oracle database has a pseudocolumn named ROWID. A pseudocolumn behaves like a table column, but is not actually stored in the table

for more http://www.orafaq.com/wiki/Pseudo-column

- format model determines how the database interprets the string. In SQL, you can use a format model as an argument of the TO_CHAR and TO_DATE functions

updates a hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the string '1998 05 20' to a DATE value:

- integrity constraint is a named rule that restrict the values for one or more columns in a table. These rules prevent invalid data entry into tables. Also, constraints can prevent the deletion of a table when certain dependencies exist.database stores constraints in the data dictionary.

Table Storage & Organization

• Database uses a data segment in a tablespace to hold table data. segment contains extents made up of data blocks.
• By default, a table is organized as a heap, heap-organized table is an unordered collection of rows, Rows are not guaranteed to be retrieved in the order in which they were inserted
• database usually stores columns in the order in which they were listed in the CREATE TABLE statement
• but this order is not guaranteed. For example, if a table has a column of type LONG, then Oracle Database always stores this column last in the row
• table can contain a virtual column, which unlike normal columns does not consume space on disk, values in a virtual column on demand by computing a set of user-specified expressions or functions.

Row Storage

• database stores rows in data blocks. Each row of a table containing data for less than 256 columns is contained in one or more row pieces
• Rows in a table cluster contain the same information as rows in nonclustered tables
• Additionally, rows in a table cluster contain information that references the cluster key to which they belong

Rowids of Row Pieces

• rowid is effectively a 10-byte physical address of a row
• row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece
• For table clusters, rows in different tables that are in the same data block can have the same rowid
• Oracle Database uses rowids internally for the construction of indexes
• Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O

Storage of Null Values

• Null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data
• require 1 byte to store > fall between columns with data values
• no data is stored > fall in the last column(s)

Table Compression

• Table compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution. 
• Table compression is transparent to database applications

types of dictionary-based table compression

• Basic table compression

This type of compression is intended for bulk load operations.use direct path INSERT operations (bypassing Database Buffer Cache)

• Advanced row compression

This type of compression is intended for OLTP applications and compresses data manipulated by any SQL operation.

following statement applies advanced row compression to the orders table:

ALTER TABLE oe.orders ROW STORE COMPRESS ADVANCED;

following example of a partial CREATE TABLE statement specifies advanced row compression for one partition and basic table compression for the other partition:

CREATE TABLE sales (
    prod_id     NUMBER     NOT NULL,
    cust_id     NUMBER     NOT NULL, ... )
 PCTFREE 5 NOLOGGING NOCOMPRESS
 PARTITION BY RANGE (time_id)
 ( partition sales_2013 VALUES LESS THAN(TO_DATE(...)) ROW STORE COMPRESS BASIC,
   partition sales_2014 VALUES LESS THAN (MAXVALUE) ROW STORE COMPRESS ADVANCED );

- Hybrid Columnar Compression, 

• the database stores the same column for a group of rows together. The data block does not store data in row-major format, but uses a combination of both row and columnar methods
• Hybrid Column Compression optimizes disk storage, whereas the IM column store optimizes memory storage
• Hybrid Columnar Compression is optimized for Data Warehousing and decision support applications on Exadata storage
• Hybrid Columnar Compression is ideal for in-database archiving of older data that is infrequently accessed
• database chooses the algorithms based on a variety of factors, including the data type of the column, the cardinality of the actual values in the column, and the compression level chosen by the user
• database must lock all rows in the compression unit if an update is made to any row in the unit
• Updates to rows using Hybrid Columnar Compression cause rowids to change.

- apply Hybrid Columnar Compression to a daily_sales table. At the end of every day

Sample Table daily_sales

Item_ID	Date	Num_Sold	Shipped_From	Restock
1000	01-JUN-14	2	WAREHOUSE1	Y
1001	01-JUN-14	0	WAREHOUSE3	N
1002	01-JUN-14	1	WAREHOUSE3	N
1003	01-JUN-14	0	WAREHOUSE2	N
1004	01-JUN-14	2	WAREHOUSE1	N
1005	01-JUN-14	1	WAREHOUSE2	N

- Compression Unit  - a unit that Hybrid Columnar Compression uses a logical construct to store a set of rows

Compression Unit

- table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data block can contain rows from multiple tables
- cluster key is the column or columns that the clustered tables have in common. For example, the employees and departments tables share the department_id column
- You specify the cluster key when creating the table cluster and when creating every table added to the table cluster

- Table Clusters offer the following benefits over nonclustered tables:

• Disk I/O is reduced for joins of clustered tables.
• Access time improves for joins of clustered tables.
• Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.

- Clustering tables is not appropriate in the following situations:

• The tables are frequently updated.
• The tables frequently require a full table scan.
• The tables require truncating.

- index cluster is a table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key. A cluster index must be created before any rows can be inserted into clustered tables

- Create the cluster employees_departments_cluster with the cluster key department_id:
 (Because the HASHKEYS clause is not specified, this cluster is an indexed cluster)

1. create an index named idx_emp_dept_cluster on this cluster key
2. create the employees and departments tables in the cluster, specifying the department_id column as the cluster key
3. add rows to the employees and departments tables
4. database physically stores all rows for each department from the employees and departments tables in the same data blocks.

Indexed Cluster

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4))
SIZE 512;

CREATE INDEX idx_emp_dept_cluster 
   ON CLUSTER employees_departments_cluster;

CREATE TABLE employees ( ... )
   CLUSTER employees_departments_cluster (department_id);
 
CREATE TABLE departments ( ... )
   CLUSTER employees_departments_cluster (department_id);

Clustered Table Data
database stores rows for employees in department 20 together, department 110 together

- hash cluster is like an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index

- With an indexed table or indexed cluster, Oracle Database locates table rows using key values stored in a separate index, database must perform at least two I/Os:

• One or more I/Os to find or store the key value in the index
• Another I/O to read or write the row in the table or table cluster

To find or store a row in a hash cluster, Oracle Database applies the hash function to the cluster key value of the row.

- Hash clusters may be beneficial when the following conditions are met:

• A table is queried much more often than modified.
• The hash key column is queried frequently with equality conditions, for example, WHERE department_id=20. For such queries, the cluster key value is hashed. The hash key value points directly to the disk area that stores the rows.
• You can reasonably guess the number of hash keys and the size of the data stored with each key value.

- Cluster key, like the key of an indexed cluster, is a single column or composite key shared by the tables in the cluster

- Hash key value is an actual or possible value inserted into the cluster key column. For example, if the cluster key is department_id, then hash key values could be 10, 20, 30, and so on.

- Oracle Database uses a hash function that accepts an infinite number of hash key values as input and sorts them into a finite number of buckets. Each bucket has a unique numeric ID known as a hash value. 

- To create a hash cluster, you use the same CREATE CLUSTER statement as for an indexed cluster, with the addition of a hash key

Create Hash Cluster

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;

After you create employees_departments_cluster, you can create the employees and departments tables in the cluster. You can then load data into the hash cluster just as in the indexed cluster

- Single-table hash cluster is an optimized version of a hash cluster that supports only one table at a time. A one-to-one mapping exists between hash keys and rows

- Sorted hash cluster stores the rows corresponding to each value of the hash function in such a way that the database can efficiently return them in sorted order

- Hashing multiple input values to the same output value is called a hash collision

Retrieving Data from a Hash Cluster When a Hash Collision Occurs