Oracle Concepts - 4 Partitions, Views, and Other Schema Objects

Oracle Database Partitions

Partitions

• 

    Partitioning – the act of breaking a large table into managable pieces
• Must be based on a partition key
• Not preferable in every situation

- types of partitions

1. Rang - based on range of values
2. Hash - based on hashing algorithm
3. List - based on fixed list of values
4. Composite - combinations of types into sub-partitions may be in one level based in rang partitioning and next level hash partitioning

### SQL Developer

-- create partitions by range 

create table order_entry_range (

order_id NUMBER,

order_desc varchar2(200),

order_region varchar2(40),

order_date date

)

partition by range (order_id)(

partition par0 values less than (1000000),

partition par1 values less than (2000000),

partition par2 values less than (3000000)

);

-- create partitions by hash

create table order_entry_hash (

order_id NUMBER,

order_desc varchar2(200),

order_region varchar2(40),

order_date date

)

partition by hash (order_id)

partitions 3;

-- create partitions by list

create table order_entry_list (

order_id NUMBER,

order_desc varchar2(200),

order_region varchar2(40),

order_date date

)

partition by list (order_region)(

partition par_n values ('SOUTH','SOUTHEST'),

partition par_s values ('NORTH','NORTHEAST'),

partition par_m values ('MIDWEST')

);

desc order_entry_range;

desc order_entry_hash;

desc order_entry_list;

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

- Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions

- Each partition is an independent object with its own name and optionally its own storage characteristics

- From the perspective of an application, only one schema object exists. DML statements require no modification to access partitioned tables

- Partitioning is useful for many different types of database applications, particularly those that manage large volumes of data

• Increased availability
• Easier administration of schema objects
• Reduced contention for shared resources in OLTP systems
• Enhanced query performance in data warehouses

- Partition key is a set of one or more columns that determines the partition in which each row in a partitioned table should go

- Oracle Database automatically directs insert, update, and delete operations to the appropriate partition by using the partition key

- Partitioning method of data distribution

• single-level partitioning strategy uses only one method of data distribution
• composite partitioning, a table is partitioned by one data distribution method and then each partition is further divided into subpartitions using a second data distribution method

- basic Partitioning strategies are range, list, and hash partitioning

1. Range Partitioning: 
- Range partitioning is the most common type of partitioning and is often used with dates

- Range partition key value determines the high value of the range partitions, which is called the transition point, database creates interval partitions for data beyond that transition point

-  Interval partitions extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions

Create a Table with Partitions

SQL Developer Sheet Partitions Section2. List Partitioning - database uses a list of discrete values as the partition key for each partition. You can use list partitioning to control how individual rows map to specific partitions. By using lists, you can group and organize related sets of data when the key used to identify them is not conveniently ordered

3. Hash partitioning, the database maps rows to partitions based on a hashing algorithm that the database applies to the user-specified partitioning key. The destination of a row is determined by the internal hash function applied to the row by the database

- Hashing algorithm is designed to evenly distributes rows across devices so that each partition contains about the same number of rows

- Partitioned index is an index that, like a partitioned table, has been decomposed into smaller and more manageable pieces. 

1. Global indexes are partitioned independently of the table on which they are created

2. local indexes are automatically linked to the partitioning method for a table.

- shows index partitioning options.

- Local partitioned indexes are common in data warehousing , offer  following advantages:

• Availability is increased because actions that make data invalid or unavailable in a partition affect this partition only.
• Partition maintenance is simplified. When moving a table partition, or when data ages out of a partition, only the associated local index partition must be rebuilt or maintained. In a global index, all index partitions must be rebuilt or maintained.
• If point-in-time recovery of a partition occurs, then the indexes can be recovered to the recovery time

Local Partitioned Index

CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;

Local Index Partitions

- Global bitmap indexes are supported only on nonpartitioned tables.

Local partitioned indexes are divided into the following subcategories:

 1. Local prefixed indexes - In this case, the partition keys are on the leading edge of the index definition

2. Local nonprefixed indexes - In this case, the partition keys are not on the leading edge of the indexed column list and need not be in the list at all

- global partitioned index is a B-tree index that is partitioned independently of the underlying table on which it is created

- global indexes are useful for OLTP applications, where rapid access, data integrity, and availability are important.

You can partition a global index by range or by hash.

a. If partitioned by range, then the database partitions the global index on the ranges of values from the table columns you specify in the column list.

b. If partitioned by hash, then the database assigns rows to the partitions using a hash function on values in the partitioning key columns.

- partition an index-organized table (IOT) by range, list, or hash. Partitioning is useful for providing improved manageability, availability, and performance for IOTs

Note the following characteristics of partitioned IOTs:

• Partition columns must be a subset of primary key columns.
• Secondary indexes can be partitioned locally and globally.
• OVERFLOW data segments are always equipartitioned with the table partitions.

- view is a logical representation of one or more tables. In essence, a view is a stored query. A view derives its data from the tables on which it is based, called base tables

Views

Views

- Queries can be complex

- views are queries that are stored as database objects

- views are used to …

a. Reduce query complexity

b. Data hiding

- views can be updateable in certain situations

- Practice Using SQL Developer

desc employees;

desc departments;

create view emp_view

as

select e.employee_id, e.first_name, e.job_id, d.department_name, d.location_id

from employees e, departments d

where e.department_id = d.department_id;

select * from  emp_view;

select * from employees;

create view emp_sel_view

as

select first_name, job_id, manager_id, hire_date

from employees;

select * from emp_sel_view;

 

Views are often used to:

• Provide an additional level of table security
• Hide data complexity For example, a single view can be defined with a join
• Present the data in a different perspective from that of the base table
• Isolate applications from changes in definitions of base tables
• Unlike a table, a view is not allocated storage space, nor does a view contain data
• Materialized views use a different data structure from standard views

- Example creates a view of the hr.employees table:

- How Data Is Accessed in Views

- Updatable join view, also called a modifiable join view, involves two or more base tables or views and permits DML operations. An updatable view contains multiple tables in the top-level FROM clause of the SELECT statement and is not restricted by the WITH READ ONLY clause.

- All updatable columns of a join view must map to columns of a key-preserved table. 

- key-preserved table in a join query is a table in which each row of the underlying table appears at most one time in the output of the query

- Object Views 

Just as a view is a virtual table, an object view is a virtual object table, Each row in the view is an object, which is an instance of an object type. An object type is a user-defined data type.

- Materialized views (Big Bang in OCM Exam) are query results that have been stored or "materialized" in advance as schema objects

- Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term).

Materialized views are used to:

1. In data warehouses, compute and store data generated from aggregate functions such as sums and averages.

2. In materialized view replication, replicate data at distributed sites and synchronize updates performed at several sites.

3. In mobile computing environments, download a data subset from central servers to mobile clients

- In a replication environment, a materialized view shares data with a table in a different database, called a master database

Materialized views are similar to indexes in the following ways:

• They contain actual data and consume storage space.
• They can be refreshed when the data in their master tables changes.
• They can improve performance of SQL execution when used for query rewrite operations.
• Their existence is transparent to SQL applications and users.
• Materialized views stored Rows (materialized) separately from the data in the master tables
• materialized view can be partitioned

Materialized View

- Example creates and populates a materialized aggregate view based on three master tables in the sh sample schema:

Refresh Methods for Materialized Views

- database maintains data in materialized views by refreshing them after changes to their master tables.

1. complete refresh occurs when the materialized view is initially defined as BUILD IMMEDIATE, unless the materialized view references a prebuilt table

2. fast refresh eliminates the need to rebuild materialized views from scratch. Thus, processing only the changes can result in a very fast refresh time.

- For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables

- Query rewrite is an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes materialized views. 

- Optimizer query transformer transparently rewrites the request to use the materialized view, requiring no user intervention and no reference to the materialized view in the SQL statement

Query Rewrite

- Sequence is a schema object from which multiple users can generate unique integers. A sequence generator provides a highly scalable and well-performing method to generate surrogate keys for a number data type.

sequence definition indicates general information, such as the following:

1. The name of the sequence
2. Whether the sequence ascends or descends
3. The interval between numbers
4. Whether the database should cache sets of generated sequence numbers in memory
5. Whether the sequence should cycle when a limit is reached

Example creates the sequence customers_seq in the sample schema oe.

CREATE SEQUENCE customers_seq
START WITH      1000
INCREMENT BY    1
NOCACHE
NOCYCLE;

Concurrent Access to Sequences

• same sequence generator can generate numbers for multiple tables
• sequence generator is useful in multiuser environments for generating unique numbers without the overhead of disk I/O or transaction locking
• If your application requires a gap-free set of numbers, then you cannot use Oracle sequences

Overview of Dimensions

A typical data warehouse has two important components: dimensions and facts.

• dimension is any category used in specifying business questions, for example, time, geography, product, department, and distribution channel. 
• fact is an event or entity associated with a particular set of dimension values, for example, units sold or profits.
• Creating a dimension permits the broader use of the query rewrite feature.
• By transparently rewriting queries to use materialized views, the database can improve query performance.

CREATE DIMENSION statement specifies:

• Multiple LEVEL clauses, each of which identifies a column or column set in the dimension
• One or more HIERARCHY clauses that specify the parent/child relationships between adjacent levels
• Optional ATTRIBUTE clauses, each of which identifies an additional column or column set associated with an individual level
• columns in a dimension can come either from the same table (denormalized) or from multiple tables (fully or partially normalized)
• only one public synonym with the same name can exist in the database
• Synonyms themselves are not securable. When you grant object privileges on a synonym, you are really granting privileges on the underlying object

- synonym is an alias for a schema object. For example, you can create a synonym for a table or view, sequence, PL/SQL program unit, user-defined object type, or another synonym. Because a synonym is simply an alias

You can create both private and public synonyms. 

• A private synonym is in the schema of a specific user who has control over its availability to others. 
• A public synonym is owned by the user group named PUBLIC and is accessible by every database user.