Given a partition name of a table how fetch the corresponding data?

Suppose we have a partitioned table and we need gathering data from a specific partition named P_200.
Here is the statement:

SELECT * FROM TABLE_NAME PARTITION (P_200) T
WHERE T.FIELD > 1000

Notes:
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 data types.

Partitioning Methods

Range Partitioning

Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition.

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.

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.

 

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)
);

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.

 

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.

 

Union VS Union all in terms of performance

Many times  unskilled or novice computer programmers when are under pressure and the time is running fast towards the end, usually  they copy the code from a place and paste it into another place, without the full understanding of the implications.

Let's take a look at the differences between UNION and UNION ALL operators.

Pivot my table

Let's take a look to a simple example of how create a pivot table, useful when you work with reports and summary statistics.

The table employee contains 10 records with the employees details (Name, salary, role and year of reference)

How get single items from a list of values

Sometimes when we develop procedures or functions we have to handle list of values, passed as parameters (for example).
Let's see a simple script helping us to split in single values the list of values, using the powerful WITH clause.

Do you need to import a file into a table? do it simple with External Table.

Very often we work with environments that cannot be linked directly (with a DB link for example) and we have to acquire data by text files.
In order to reach that goal I have seen developers write procedures with many lines of code, variables end complicating their lives with loops.
Just do it simple, with "ET".