Table partition in Oracle

Partitioned Tables and Indexes

This chapter describes partitioned tables and indexes. It covers the following topics:

• Introduction to Partitioning
• Partitioning Methods
• Partitioned Indexes
• Partitioning to Improve Performance
  

  Note: Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.

Introduction to Partitioning

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

Note: All partitions of a partitioned object must reside in tablespaces of a single block size.

See Also: "Multiple Block Sizes" Oracle9i Data Warehousing Guide for more information about partitioning

Partitioning offers these advantages:

• Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
• Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partitionpruning) can provide order-of-magnitude gains in performance.
• Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.
  
  Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
• Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
• Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECTstatements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

Figure 11-1 offers a graphical view of how partitioned tables differ from nonpartitioned tables.

Figure 11-1 A View of Partitioned Tables

Text description of the illustration cncpt162.gif

Partition Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row. Oracle9i automatically directs insert, update, and delete operations to the appropriate partition through the use of the partition key. A partition key:

• Consists of an ordered list of 1 to 16 columns
• Cannot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID
• Can contain columns that are NULLable

Partitioned Tables

Tables can be partitioned into up to 64,000 separate partitions. Any table can be partitioned except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns withCLOB or BLOB datatypes.

Partitioned Index-Organized Tables

You can range partition index-organized tables. This feature is very useful for providing improved manageability, availability and performance for index-organized tables. In addition, data cartridges that use index-organized tables can take advantage of the ability to partition their stored data. Common examples of this are the Image and interMedia cartridges.

For partitioning an index-organized table:

• Only range and hash partitioning are supported
• 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

Partitioning Methods

Oracle provides the following partitioning methods:

• Range Partitioning
• List Partitioning
• Hash Partitioning
• Composite Partitioning

Figure 11-2 offers a graphical view of the methods of partitioning.

Figure 11-2 List, Range, and Hash Partitioning

Text description of the illustration cncpt158.gif

Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning. Figure 11-3 offers a graphical view of range-hash and range-list composite partitioning.

Figure 11-3 Composite Partitioning

Text description of the illustration cncpt168.gif

Range Partitioning

Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.

When using range partitioning, consider the following rules:

• Each partition has a VALUES LESS THAN clause, which specifies a noninclusive upper bound for the partitions. Any binary values of the partition key equal to or higher than this literal are added to the next higher partition.
• All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
• A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.

A typical example is given in the following section. The statement creates a table (sales_range) that is range partitioned on the sales_date field.

Range Partitioning Example

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

List Partitioning

List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

The details of list partitioning can best be described with an example. In this case, let's say you want to partition a sales table by region. That means grouping states together according to their geographical location as in the following example.

List Partitioning Example

CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within the set of values that describes the partition. For example, the rows are inserted as follows:

• (10, 'Jones', 'Hawaii', 100, '05-JAN-2000') maps to partition sales_west
• (21, 'Smith', 'Florida', 150, '15-JAN-2000') maps to partition sales_east
• (32, 'Lee', 'Colorado', 130, '21-JAN-2000') does not map to any partition in the table

Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.

The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.

Hash Partitioning

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:

• You do not know beforehand how much data maps into a given range
• The sizes of range partitions would differ quite substantially or would be difficult to balance manually
• Range partitioning would cause the data to be undesirably clustered
• Performance features such as parallel DML, partition pruning, and partition-wise joins are important

The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.

Hash Partitioning Example

CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (data1, data2, data3, data4);

The preceding statement creates a table sales_hash, which is hash partitioned on salesman_id field. The tablespace names are data1, data2, data3, and data4.

Composite Partitioning

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.

Composite Partitioning Range-Hash Example

CREATE TABLE sales_composite 
(salesman_id  NUMBER(5), 
 salesman_name VARCHAR2(30), 
 sales_amount  NUMBER(10), 
 sales_date    DATE)
PARTITION BY RANGE(sales_date) 
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE data1,
SUBPARTITION sp2 TABLESPACE data2,
SUBPARTITION sp3 TABLESPACE data3,
SUBPARTITION sp4 TABLESPACE data4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

This statement creates a table sales_composite that is range partitioned on the sales_date field and hash subpartitioned on salesman_id. When you use a template, Oracle names the subpartitions by concatenating the partition name, an underscore, and the subpartition name from the template. Oracle places this subpartition in the tablespace specified in the template. In the previous statement, sales_jan2000_sp1 is created and placed in tablespace data1 while sales_jan2000_sp4 is created and placed in tablespace data4. In the same manner, sales_apr2000_sp1 is created and placed in tablespace data1 while sales_apr2000_sp4 is created and placed in tablespace data4. Figure 11-4 offers a graphical view of the previous example.

Figure 11-4 Composite Range-Hash Partitioning

Text description of the illustration cncpt157.gif

Composite Partitioning Range-List Example

CREATE TABLE bimonthly_regional_sales
(deptno NUMBER, 
 item_no VARCHAR2(20),
 txn_date DATE, 
 txn_amount NUMBER, 
 state VARCHAR2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE(


SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)

( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')) );

This statement creates a table bimonthly_regional_sales that is range partitioned on the txn_date field and list subpartitioned on state. When you use a template, Oracle names the subpartitions by concatenating the partition name, an underscore, and the subpartition name from the template. Oracle places this subpartition in the tablespace specified in the template. In the previous statement, janfeb_2000_east is created and placed in tablespace ts1 while janfeb_2000_central is created and placed in tablespace ts3. In the same manner, mayjun_2000_east is placed in tablespace ts1 while mayjun_2000_central is placed in tablespace ts3.Figure 11-5 offers a graphical view of the table bimonthly_regional_sales and its 9 individual subpartitions.

Figure 11-5 Composite Range-List Partitioning

Text description of the illustration cncpt167.gif

When to Partition a Table

Here are some suggestions for when to partition a table:

• Tables greater than 2GB should always be considered for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read-only.

Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes).

See Also: Oracle9i Data Warehousing Guide for more information about partitioned indexes

Local Partitioned Indexes

Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments.

Figure 11-6 offers a graphical view of local partitioned indexes.

Figure 11-6 Local Partitioned Index

Text description of the illustration cncpt161.gif

Global Partitioned Indexes

Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH) 
COALESCE (HASH) 
DROP 
EXCHANGE 
MERGE 
MOVE 
SPLIT 
TRUNCATE 

These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

• The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
• The index doesn't have to be rebuilt after the operation.

Example:

ALTER TABLE DROP PARTITION P1 UPDATE GLOBAL INDEXES

Note: This feature is supported only for heap organized tables.

See Also: Oracle9i SQL Reference for more information about the UPDATE GLOBAL INDEX clause

Figure 11-7 offers a graphical view of global partitioned indexes.

Figure 11-7 Global Partitioned Index

Text description of the illustration cncpt160.gif

Global Nonpartitioned Indexes

Global nonpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.

Figure 11-8 offers a graphical view of global nonpartitioned indexes.

Figure 11-8 Global Nonpartitioned Index

Text description of the illustration cncpt159.gif

Partitioned Index Examples

Example of Index Creation: Starting Table Used for Examples

CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
 last_name VARCHAR2(10), 
 department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE part1, 
 PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE part2, 
 PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE part3);

Example of a Local Index Creation

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

Example of a Global Index Creation

CREATE INDEX employees_global_idx ON employees(employee_id);

Example of a Global Partitioned Index Creation

CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(5000),
 PARTITION p2 VALUES LESS THAN(MAXVALUE));

Example of a Partitioned Index-Organized Table Creation

CREATE TABLE sales_range
(
salesman_id   NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE, 
PRIMARY KEY(sales_date, salesman_id)) 
ORGANIZATION INDEX INCLUDING salesman_id 
OVERFLOW TABLESPACE tabsp_overflow 
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
 OVERFLOW TABLESPACE p1_overflow, 
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
 OVERFLOW TABLESPACE p2_overflow, 
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
 OVERFLOW TABLESPACE p3_overflow, 
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
 OVERFLOW TABLESPACE p4_overflow);

Miscellaneous Information about Creating Indexes on Partitioned Tables

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

Using Partitioned Indexes in OLTP Applications

Here are a few guidelines for OLTP applications:

• Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.
• Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.

Using Partitioned Indexes in Data Warehousing and DSS Applications

Here are a few guidelines for data warehousing and DSS applications:

• Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.
• Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.

Partitioned Indexes on Composite Partitions

Here are a few points to remember when using partitioned indexes on composite partitions:

• Only range partitioned global indexes are supported.
• Subpartitioned indexes are always local and stored with the table subpartition by default.
• Tablespaces can be specified at either index or index subpartition levels.

Partitioning to Improve Performance

Partitioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons are:

• Partition Pruning
• Partition-wise Joins
• Parallel DML

Partition Pruning

The Oracle server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

For each SQL statement, depending on the selection criteria specified, unneeded partitions or subpartitions can be eliminated. For example, if a query only involves March sales data, then there is no need to retrieve data for the remaining eleven months. Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance.

If the optimizer determines that the selection criteria used for pruning are satisfied by all the rows in the accessed partition or subpartition, it removes those criteria from the predicate list (WHERE clause) during evaluation in order to improve performance. However, the optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column (with the exception of the TO_DATE function). Similarly, the optimizer cannot use an index if the SQL statement applies a function to the indexed column, unless it is a function-based index.

Pruning can eliminate index partitions even when the underlying table's partitions cannot be eliminated, but only when the index and table are partitioned on different columns. You can often improve the performance of operations on large tables by creating partitioned indexes that reduce the amount of data that your SQL statements need to access or modify.

Equality, range, LIKE, and IN-list predicates are considered for partition pruning with range or list partitioning, and equality and IN-list predicates are considered for partition pruning with hash partitioning.

Partition Pruning Example

We have a partitioned table called orders. The partition key for orders is order_date. Let's assume that orders has six months of data, January to June, with a partition for each month of data. If the following query is run:

SELECT SUM(value) 
FROM orders 
WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98'

Partition pruning is achieved by:

• First, partition elimination of January, February, May, and June data partitions. Then either:
  • An index scan of the March and April data partition due to high index selectivity
    
    or
  • A full scan of the March and April data partition due to low index selectivity

Partition-wise Joins

A partition-wise join is a join optimization that you can use when joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that they minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data distribution.

See Also: Oracle9i Data Warehousing Guide for more information about partitioning methods and partition-wise joins

Parallel DML

Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.

The semantics and restrictions for parallel DML sessions are the same whether you are using index-organized tables or not.

hope it helps to your findings.

Functions in SQL/Oracle

Functions in SQL / Oracle:

Functions are programs that perform a certain task. A function has a name and may receive values. The function always returns one value of output. The are pre-defined functions in Oracle that can be used for commonly performed tasks.

The different types of pre-defined functions are listed on this slide:

• Character Functions
• Numeric Functions
• Date Functions
• Conversion Functions
• Null Functions

The DUAL table is very useful when testing the outcome of single-row functions. This pre-defined table is accessible by all users of the database and is made up of one row and one column. It is used to see the result of a function once on the screen.

---

Character Functions

Given below are a list of the character functions that will be discussed.

• UPPER
• LOWER
• INITCAP
• SUBSTR
• CONCAT
• LENGTH

The UPPER function:

Is used to convert a character string to uppercase.

In the example:
SELECT UPPER('hello') FROM DUAL;
Will display the word HELLO in uppercase.

The statement:
SELECT UPPER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in uppercase.

The LOWER function:

Is used to convert a character string to lowercase.

In the example:
SELECT LOWER('HELLO') FROM DUAL;
Will display the word HELLO in lowercase.

The statement:
SELECT LOWER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in lowercase.

The INITCAP function:

Is used to convert the first letter of every new word to uppercase.

In the example:
SELECT INITCAP('hello world') FROM DUAL;
Will display the string 'hello world' in sentence case => Hello World

The statement:
SELECT INITCAP(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in Sentence Case.

The LENGTH function :

Is used to display the length (number of characters) of a string.

In the example:
SELECT LENGTH('hello') FROM DUAL;
Will display the length of the word HELLO => 5.

The statement:
SELECT LENGTH(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the length of the job titles that begin with the letter M.

The SUBSTR function:

Is used to extract a portion of a string from another string.

In the example:
SELECT SUBSTR('HELLO',2,2) FROM DUAL;
Will extract 2 characters from the string hello, starting at the second character position -> EL

The statement:
SELECT UPPER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will extract 10 characters from the job titles beginning with the letter M, starting from the third character position.

The CONCAT function:

Is used to combine two strings to create a resultant string.

In the example:
SELECT CONCAT('SQL','CLASS') FROM DUAL;
Will combine the two strings to create a single string.=> SQLCLASS

The statement:
SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM EMPLOYEES
WHERE JOB_ID='AD_VP';
Will combine the first name and last name of employees who hold the job ID of AD_VP.

---

Numeric Functions                                                                                              Back to Top

Given below are a list of the numeric functions that will be discussed.

• ROUND
• TRUNC
• ABS
• FLOOR
• CEIL
• MOD

The ROUND function:

Syntax : ROUND(n,integer)

Returns a number 'n' (the first argument) rounded to a certain number of integer places (second parameter) to the right of the decimal point

If you don't specify a second parameter, the number will be rounded to zero decimal places.

In the example:
SELECT ROUND(27.5663,2) FROM DUAL;
The third digit after the decimal point is 6. As this number is greater than 5, the number rounded to 2 decimal places becomes: 27.57

In the example:
SELECT ROUND(27.5663) FROM DUAL;
The first digit after the decimal point is 5. As this number is equal to 5, the number rounded to 0 decimal places becomes: 28

The TRUNC function:

Syntax:

TRUNC(n1,n2)

Returns n1 truncated to n2 decimal places.

If you don't specify a second parameter, the number will be truncated to zero decimal places.

In the example:
SELECT TRUNC(27.5663,2) FROM DUAL;
The number is truncated to 2 decimal places: 27.56

In the example:
SELECT TRUNC(27.5663) FROM DUAL;
The number is truncated to 0 decimal places becomes: 27

The ABS function:

Syntax:
ABS(n)

Returns an absolute value of n. The absolute of a number (positive or negative) is the positive value of the number.

In the example:
SELECT ABS(-9) FROM DUAL;
The positive value of -9 is 9.

In the example:
SELECT ABS(9) FROM DUAL;
The positive value of 9 is 9.

The FLOOR function:

Syntax:
FLOOR(n)
Returns the largest integer equal or less than n.

In the example:
SELECT FLOOR(38.4) FROM DUAL;
The largest integer value equal or less than 38.4, is 38

In the example:
SELECT FLOOR(-38.4) FROM DUAL;
The largest integer value equal or less than -38. is -39

The CEIL function:

Syntax:
CEIL(n)
Returns the smallest integer greater than or equal to n.

In the example:
SELECT CEIL(38.4) FROM DUAL;
The largest integer value equal or greater than 38.4, is 39

In the example:
SELECT CEIL(-38.4) FROM DUAL;
The largest integer value equal or greater than -38 is -38

The MOD function:

Syntax:
MOD(n1,n2)
Returns the remainder obtained by dividing n1 by n2. Returns n1 if n1 is zero.

In the example:
SELECT MOD(21,4) FROM DUAL;
The remainder obtained after dividing 21 by 4 is 1.

In the example:
SELECT MOD(0,4) FROM DUAL;
When the first argument is 0, the result of the mod function is 0.

Note: To evaluate the result of a MOD function. Oracle uses the following expression:

MOD(m,n) = m - n * FLOOR(m/n)

A function similar to MOD is the REMAINDER function. However when evaluating the result of the REMAINDER function Oracle uses the formula:
MOD(m,n) = m - n * ROUND(m/n)

---

Date Functions                                                                                                      Back to Top

Given below are a list of the date functions that will be discussed.

• ADD_MONTHS
• MONTHS_BETWEEN

The ADD_MONTHS function:

Syntax:
ADD_MONTHS(date,n)
Returns a date, that is n months before (if n is negative) or after (if n is positive) the date.

In the example:
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
You are adding three months to the current date. If the current date Aug 03, 2009, the output is 03-NOV-09.

The MONTHS_BETWEEN function:

Syntax:
MONTHS_BETWEEN(date1, date2)
Returns a number representing the number of months between date1 and date2.

In the example:
SELECT FIRST_NAME, MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
FROM EMPLOYEES
WHERE JOB_ID='AD_VP';
For employees whose JOB ID is AD_VP, the first name and the number of months that have elapsed since they were hired are being retrieved.

---

Conversion Functions

Given below are a list of the conversion functions that will be discussed.

• TO_CHAR
• TO_NUMBER
• TO_DATE

The TO_CHAR function:
Syntax:
TO_CHAR(str,'fmt')

Fmt -> can be a string created by date format element or numeric format elements.
The TO_CHAR function is used to convert a date type value or a numeric value into a character string. This is often used when you want to display a date in a format other than a DD-MON-YY format. For e.g. If you want to display the date : 05-25-10 in the format May 5th, 2010 you would need to convert it into a character string. Similarly if you want to display a numeric value like 50678.34 in the format $50,678.34 you will need to convert it into a character string.

Format Element	Description	Example
YYYY	Full year in numbers	2010
YEAR	Year spelled out	Two thousand Nine
MM	Two digit value for month	09
MONTH	Full name of month	APRIL
MON	Three letter abbreviation of month	APR
DY	Three letter abbreviation of day of week	TUE
DAY	Full name of the day of the week	TUESDAY
DD	Numeric day of the month	25

In the example:
SELECT TO_CHAR(SYSDATE,'DDth "of" MONTH, YYYY')
FROM DUAL;

We are displaying the current date in a format that will have a two digit day of month as an ordinal number, followed by the word of , followed by the Month (spelled out), followed by a comma, followed by a four digit year.

SELECT TO_CHAR(SYSDATE,'fmDDth "of" MONTH, YYYY')
FROM DUAL;

We are displaying the current date in a format similar to the previous example. In this case the difference is created by the characters fm that start the format string. The fm characters are used to remove unnecessary spaces in the output.

Format Element	Description	Format	Example
9	Represents a number	9999.99	Number: 345.6 Output: 345.60
0	Forces a zero to be displayed	0000.00	Number: 345.6 Output: 0345.60
$	Places a floating dollar sign	$999.99	Number: 34.5 Output : $34.50
.	Decimal Point	$99.9	Number : 38.99 Output : $38.99
,	Thousands Indicator	$99,999.99	Number: 15042.5 Output: $15,042.50

The TO_DATE function:

TO_DATE(str,'fmt')
Converts a string representing a date that is in a non default date to a date value in the default date format.

The TO_DATE function is used to convert a character string that represents a date into a date value.
In the example:
SELECT TO_DATE('3 of June, 2009','DD "of" Month, YYYY')
FROM DUAL;

The character string '3 of June, 2009' needs to be converted to a date as in 06-03-09. The second argument in the TO_DATE function tells Oracle how to read the first argument. It defines the format that the first argument is in. This tells Oracle how to read the first argument, to identify the individual date elements from the character string.

The TO_NUMBER function:

TO_NUMBER(str,'fmt')

Converts a string representing a numeric value with format to a number without formatting. The TO_NUMBER function is used to convert a character string into a numeric value.

In the example:
SELECT TO_NUMBER('$45.67','$99.99')
FROM DUAL;
The first argument is a formatted numeric value with a dollar sign. The TO_NUMBER function is used to convert this string into a number as in 45.67. The second argument tells Oracle how to interpret the first argument. Using the second argument Oracle is able to identify the number without the formats.

---

NULL functions                                                                                                    Back to Top

The following null functions will be discussed in this article.

• NVL
• NVL2
• NULLIF

The NVL function:

Syntax:
NVL(value,default)
Substitutes the second argument when the first argument's value is null. The datetype of the second argument must match that of the first argument.

In the example:
SELECT NVL(COMMISSION_PCT,0)
FROM EMPLOYEES
WHERE JOB_ID IN ('AD_VP','SA_MAN');

You are displaying the commission percent for all those employees whose JOB ID is either AD_VP or SA_MAN. For those employees whose COMMISSION_PCT value is NULL , a 0 will be substituted in the output.

The NVL2 function:

Syntax:
NVL2(value,default1,default2)
Substitutes the second argument when the first argument's value is not null. Substitutes the third argument when the first argument is null.

In the example:
SELECT NVL2(COMMISSION_PCT, 'COMMISSION','NO COMMISSION')
FROM EMPLOYEES
WHERE JOB_IN IN ('AD_VP','SA_MAN');

The COMMISSION_PCT column in the table takes either null values or non-null numeric values. The NVL2 function has substitute values for both the nulls and not-nulls. When the column's value is a null, the string 'NO COMMISSION' is displayed, when the value is a number, the string 'COMMISSION' is displayed.

The NULLIF function:

NULLIF(expr1,expr2)
NULLIF function compares expr1 and expr2.  If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

In the example:
SELECT LENGTH(FIRST_NAME), LENGTH(LAST_NAME),
NULLIF(LENGTH(FIRST_NAME), LENGTH(LAST_NAME))
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '30-JUN-97' AND '30-DEC-97';

The NULLIF function will return a null value when the length of the FIRST_NAME is equal to the length of the LAST_NAME.

I hope this will helps to you.