How do `PARTITION BY` and `KEEP` clauses differ in Oracle's data partitioning?

PARTITION BY with and without KEEP in Oracle

When partitioning data in Oracle, there are two main options available: PARTITION BY and KEEP. Both options can be used to group data into smaller subsets, but they have different effects on the resulting data.

PARTITION BY

The PARTITION BY clause is used to divide a table into smaller partitions based on one or more columns. The data in each partition is stored separately from the data in other partitions. This can improve performance for certain types of queries, such as queries that filter data by a specific column value.

For example, the following query uses the PARTITION BY clause to divide the empl table into partitions based on the deptno column:

SELECT empno,
       deptno,
       sal,
       MIN(sal) OVER (PARTITION BY deptno) AS "Lowest",
       MAX(sal) OVER (PARTITION BY deptno) AS "Highest"
FROM empl;

This query will return the lowest and highest salary for each department. The data for each department will be stored in a separate partition, which will improve performance for queries that filter data by department.

KEEP

The KEEP clause is used to specify which rows to keep from each partition. The KEEP clause can be used with either the PARTITION BY clause or the DISTINCT clause.

When used with the PARTITION BY clause, the KEEP clause specifies which rows to keep from each partition. For example, the following query uses the KEEP clause to keep only the first row from each partition:

SELECT empno,
       deptno,
       sal,
       MIN(sal) OVER (PARTITION BY deptno) AS "Lowest",
       MAX(sal) OVER (PARTITION BY deptno) AS "Highest"
FROM empl
KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno);

This query will return the lowest and highest salary for each department, but it will only return the first row from each partition. This can be useful for queries that need to return a limited number of rows, or for queries that need to return data in a specific order.

Difference between PARTITION BY and KEEP

The main difference between PARTITION BY and KEEP is that PARTITION BY divides the data into smaller partitions, while KEEP specifies which rows to keep from each partition. PARTITION BY can be used to improve performance for certain types of queries, while KEEP can be used to limit the number of rows returned by a query or to return data in a specific order.

The above is the detailed content of How do `PARTITION BY` and `KEEP` clauses differ in Oracle's data partitioning?. For more information, please follow other related articles on the PHP Chinese website!