oracle partion

Question 1

what is interval partitioning (11g)

Answer 1

system is able to create new partitions as they are required.

• Interval partitioning is restricted to a single partition key that must be a numerical or date range.
• A MAXVALUE partition cannot be defined for an interval partitioned table.

Question 2

Reference Partitioning(11g)

Answer 2

Reference partitioning allows tables related by foreign keys to be logically equi-partitioned
*

Question 3

hash partition

Answer 3

when partion key isn’t there

* you can also use partition-wise joins, parallel index access, and parallel DML.

Question 4

what is partition pruning

Answer 4

Partition Pruning. … In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access li
* s functionality enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement.

Question 5

dw init.ora

Answer 5

COMPATIBLE

The COMPATIBLE parameter identifies the level of compatibility that the database has with earlier releases. To benefit from the latest features, set the COMPATIBLE parameter to your database release number.

OPTIMIZER_FEATURES_ENABLE

To benefit from advanced cost-based optimizer features such as query rewrite, make sure this parameter is set to the value of the current database version.

DB_BLOCK_SIZE

The default value of 8 KB is appropriate for most data warehousing needs. If you intend to use table compression, consider a larger block size.

DB_FILE_MULTIBLOCK_READ_COUNT

The DB_FILE_MULTIBLOCK_READ_COUNT parameter enables reading several database blocks in a single operating-system read call. Because a typical workload on a data warehouse consists of many sequential I/Os, make sure you can take advantage of fewer large I/Os as opposed to many small I/Os. When setting this parameter, take into account the block size as well as the maximum I/O size of the operating system, and use the following formula:

DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE =

Maximum operating-system I/O sizes vary between 64 KB and 1 MB.

PARALLEL_MAX_SERVERS

The PARALLEL_MAX_SERVERS parameter sets a resource limit on the maximum number of processes available for parallel execution. Parallel operations need at most twice the number of query server processes as the maximum degree of parallelism (DOP) attributed to any table in the operation.

Oracle Database sets the PARALLEL_MAX_SERVERS parameter to a default value that is sufficient for most systems. The default value for PARALLEL_MAX_SERVERS is as follows:

(CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5)

This might not be enough for parallel queries on tables with higher DOP attributes. Oracle recommends users who expect to run queries of higher DOP to set PARALLEL_MAX_SERVERS as follows:

2 x DOP x NUMBER_OF_CONCURRENT_USERS

For example, setting the PARALLEL_MAX_SERVERS parameter to 64 will allow you to run four parallel queries simultaneously, assuming that each query is using two slave sets with a DOP of eight for each set.

If the hardware system is neither CPU-bound nor I/O bound, then you can increase the number of concurrent parallel execution users on the system by adding more query server processes. When the system becomes CPU- or I/O-bound, however, adding more concurrent users becomes detrimental to the overall performance. Careful setting of the PARALLEL_MAX_SERVERS parameter is an effective method of restricting the number of concurrent parallel operations.

PARALLEL_ADAPTIVE_MULTI_USER

The PARALLEL_ADAPTIVE_MULTI_USER parameter, which can be TRUE or FALSE, defines whether or not the server will use an algorithm to dynamically determine the degree of parallelism for a particular statement depending on the current workload. To take advantage of this feature, set PARALLEL_ADAPTIVE_MULTI_USER to TRUE.

QUERY_REWRITE_ENABLED

To take advantage of query rewrite against materialized views, you must set this parameter to TRUE. This parameter defaults to TRUE.

QUERY_REWRITE_INTEGRITY

The default for the QUERY_REWRITE_INTEGRITY parameter is ENFORCED. This means that the database will rewrite queries against only fully up-to-date materialized views, if it can base itself on enabled and validated primary, unique, and foreign key constraints.

In TRUSTED mode, the optimizer trusts that the data in the materialized views is current and the hierarchical relationships declared in dimensions and RELY constraints are correct.

STAR_TRANSFORMATION_ENABLED

To take advantage of highly optimized star transformations, make sure to set this parameter to TRUE.

Question 6

Composite Partitioning Tables

Answer 6

Composite partitioning allows range partitions to be hash subpartitioned on a different key