Scribd
Upload
2K views3 pages

Oracle Partitioning Interview Questions and Answers

This document discusses Oracle partitioning, including: 1. Partitioning separates data into logical subsets for improved administration and performance. Tables and indexes can be partitioned. 2. Advantages of partitioning include improved administration, ability to drop unneeded partitions, and improved query performance when reading specific partitions. 3. The document provides examples of Oracle syntax for creating partitioned tables and indexes using range, list, and hash partitioning methods.

Uploaded by

sai_balaji_8
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2K views3 pages

Oracle Partitioning Interview Questions and Answers

This document discusses Oracle partitioning, including: 1. Partitioning separates data into logical subsets for improved administration and performance. Tables and indexes can be partitioned. 2. Advantages of partitioning include improved administration, ability to drop unneeded partitions, and improved query performance when reading specific partitions. 3. The document provides examples of Oracle syntax for creating partitioned tables and indexes using range, list, and hash partitioning methods.

Uploaded by

sai_balaji_8
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Oracle partitioning Interview Questions and Answers

1. What is partitioning?
Partitioning is the process of separating data into logical subsets. In the same way as normal tables
and indexes, partitions are physical segments for which extents are allocated (see Note 666061).
2. Which objects can be partitioned?
Tables and indexes can be partitioned. All combinations are theoretically possible:
 Table not partitioned, relevant index partitioned
 Table partitioned, relevant index not partitioned
 Table partitioned, relevant index partitioned
Up to 64,000 partitions are possible. Objects with LONG fields and LONG RAW fields cannot be
partitioned.
3. What are the advantages of partitioning?
 Improved administration because many operations can be carried out on separate partitions (data
loads, index creation and rebuilding, backup/recovery and so on).
 Partitions that are no longer required can simply be dropped (no reorganization to recover disk
space and no time consuming DELETEs are necessary).
 You can improve the performance of queries if only specific partitions have to be read.
 Partitioning is transparent for the application and does not require any change to SQL queries and
DML statements.
 Avoid redo log data when you delete old data because, unlike a DELETE, a DROP or TRUNCATE
does not generate redo log data.
4. What problems can occur in connection with partitioning?
ORA-014508:
Validation of partitioned objects with VALIDATE STRUCTURE encounters an ORA-14508 error if the
utlvalid.sql script was not executed for the executing database user. To avoid the error, see Note
514178 and execute utlvalid.sql for all relevant users.
ORA-14400:
ORA-14400 occurs if you want to insert an entry in the table that does not match the value range of
any partition. In such a case, you must compare the value of the entry with the definitions of the
partitions to determine the cause of the error.
Queries that do not contain the partitioning criterion in the WHERE clause, must scan ALL
partitions. In these cases, the effort increases in proportion to the number of partitions.
5. How many types of partitioning exist?
Range partitioning
Each partition includes data from a specific value range, for example:
Column YEAR: JAN 01 2002 - DEC 31 2002 -> Partition 1
Column YEAR: JAN 01 2003 - DEC 31 2003 -> Partition 2
Column YEAR: JAN 01 2004 - DEC 31 2004 -> Partition 3
List partitioning
For each partition, there is a list specifying the values to be assigned to this partition, for example:
Column COUNTRY: "Germany", "France" -> Partition 1 (Europe)
COLUMN COUNTRY: "USA", "Mexico" -> Partition 2 (America)
If you specify a default partition, all records that do not match any list are included in this partition.
Hash partitioning
A hash algorithm is used to distribute the records at random to the individual partitions.
Combined partitioning: Several of the above partitioning types can be combined in the form of
PARTITION and SUBPARTITION (for example, range hash or range list).
The following different partitioning methods are used in relation to indexes:
Local partitioning: There is a 1:1 relationship between the table and index partitions. Each index
partition contains the values of exactly one related table partition.
Global partitioning: The index partitioning is independent of the table partitioning in question. An
index partition can contain values from different table partitions.
6. What is the Oracle syntax for creating partitioned objects?
Example of range partitioning:

CREATE TABLE MONTHS (MONTH NUMBER, DAYS NUMBER)


PARTITION BY RANGE (MONTH)
( PARTITION WINTER VALUES LESS THAN (4),
PARTITION SPRING VALUES LESS THAN (7),
PARTITION SUMMER VALUES LESS THAN (10),
PARTITION AUTUMN VALUES LESS THAN (13));
Example of list partitioning:

CREATE TABLE MONTHS (MONTH VARCHAR(20), DAYS NUMBER)


PARTITION BY LIST (MONTH)
( PARTITION WINTER VALUES ('January', 'February', 'March'),
PARTITION SPRING VALUES ('April', 'May', 'June'),
PARTITION SUMMER VALUES ('July', 'August', 'September'),
PARTITION AUTUMN VALUES ('October', 'November', 'December'));
Example of hash partitioning:

CREATE TABLE MONTHS (MONTH VARCHAR(20), DAYS NUMBER)


PARTITION BY HASH(MONTH)
PARTITIONS 4;
7. How can I find out which objects are partitioned?
The following statement provides all objects with partitions at Oracle level:

SELECT TABLE_OWNER OWNER, 'TABLE' TYPE, TABLE_NAME NAME


FROM DBA_TAB_PARTITIONS
GROUP BY TABLE_OWNER, TABLE_NAME
UNION
SELECT INDEX_OWNER, 'INDEX', INDEX_NAME
FROM DBA_IND_PARTITIONS
GROUP BY INDEX_OWNER, INDEX_NAME;
8. How do I determine all the partitions of a partitioned object?
You can receive the partitions of a table as follows:

SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '<table_name>';


You can determine the partitions of an index as follows:

SELECT * FROM DBA_IND_PARTITIONS WHERE INDEX_NAME = '<index_name>';


9. Where does Oracle store administrative information for partitions?
Oracle stores administrative information for the partitions in the DBA_PART_TABLES and
DBA_PART_INDEXES views, for example:
 PARTITIONING_TYPE: Partitioning type
 PARTITION_COUNT: Number of partitions
 DEF_*: Default values for memory and configuration parameters (see Note 666061).
 The DBA_PART_KEY_COLUMNS view contains the columns to which an object is partitioned:
 NAME: Name of the partitioned object
 COLUMN_NAME: Column name
 COLUMN_POSITION: Position of the column during partitioning
 The DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS views contain the high value of the
partition (in other words, during range partitioning, the value that was specified with LESS THAN):
 HIGH_VALUE: high value of the partition
10. Are objects partitioned by default in the R/3 environment?
Partitioned objects are not delivered in normal SAP R/3 Systems. On the other hand, many InfoCube
tables in the Business Warehouse area (BW) are partitioned.
11. Can I partition additional R/3 objects depending on demand?
As of 4. 6B, R/3 DDIC supports a simple range partitioning of tables based on exactly one column.
This functionality is required when you use BW.
Note 742243 describes the prerequisites and possibilities in Release 4.6C or higher for creating
objects as range, list or hash partitioned. However, no options will be available for defining
subpartitions. Indexes can only ever be partitioned in the same way as the underlying tables (or not
at all). See also Note 105047.
If you want to use partitioning that is not supported by your R/3 Release, you can also partition the
objects manually at Oracle level. Note, however, that this partitioning can disappear if you perform
certain actions (such as table conversions).
12. Are objects partitioned by default in the Oracle DDIC?
As of Oracle9i, multiple objects belonging to the Logminer (see Note 701562) and whose names
start with LOGMNR or LOGSTDBY are partitioned when created under the SYSTEM user, for
example:

LOGMNR_TS$
LOGMNR_TYPE$
LOGMNR_USER$
LOGSTDBY$APPLY_PROGRESS
As of Oracle 10g, STREAMS$_APPLY_SPILL_MSGS_PART and a large number of tables from the
workload repository (WRH$_...) are also partitioned.
13. Can I deactivate partitioning in BW?
If you use BW functions, then partitioned objects are an essential prerequisite for good database
performance. Therefore deactivation of the Oracle partitioning option is not permitted in this case.
However, if BW is automatically installed when you install another product (such as NetWeaver04),
but you do not actually use BW, you can deactivate the partitioning option to reduce the license
costs for a database that is directly licensed with Oracle (for example). For more information, see
Note 859841.
14. Where can I find more information about partitioning?
Oracle9i Online Documentation:

Oracle9i Database Concepts


-> 11 Partitioned Tables and Indexes

Common questions

Powered by AI

The DEFAULT partition in list partitioning captures all records that do not match any specified list values for the existing partitions. This plays a crucial role in ensuring that all data entries are accommodated within the partitioned structure, preventing insertion errors that could occur if an item does not fit predefined lists. It acts as a catch-all for unexpected data values, ultimately ensuring the robustness and flexibility of the partitioning strategy, particularly in dynamic datasets where full pre-specification of values is impractical .

Manually partitioned objects in Oracle can present challenges, especially if unsupported by certain R/3 releases. One major issue is the potential for partitioning to be lost during operations like table conversions, requiring meticulous management to preserve partition structures. Incompatible partitioning methods may necessitate complex manual interventions at the Oracle level, demanding significant expertise to ensure data integrity and performance stability through such changes. Additionally, the complexity of manual tasks raises the risk of errors and inefficiencies in database management .

Transparency in application-level integration with partitioned databases is significant because it allows applications to function without any modifications to existing SQL queries or DML operations, maintaining application stability and performance. This transparency ensures that partitioning can be implemented or adjusted without affecting application logic, providing seamless scalability and manageability of data over time, and enabling businesses to optimize database performance without raising application complexity or imposing additional development burdens .

A common error in Oracle partitioning is ORA-14400, which occurs when an attempt is made to insert an entry into a table that doesn't match any defined partition's value range. To address this error, the entry's value needs to be compared against the partitions' definitions to determine the mismatch's cause. This ensures that data accurately fits within the partitioned structure. Additionally, queries without the partitioning criterion in the WHERE clause may scan all partitions, increasing the effort proportionally to the partition number, emphasizing the importance of properly structured queries .

Oracle handles the partitioning of indexes through Local and Global partitioning methods. Local partitioning creates a 1:1 correspondence between the partitions of the table and its indexes; each index partition covers one table partition. This method simplifies management and ensures index partitions align with their respective table partitions. Global partitioning allows the index partitions to cover multiple table partitions, providing flexibility in how indexes are distributed across the partitions .

Administrative data regarding partitions in Oracle databases can be accessed through specific Oracle views. DBA_PART_TABLES and DBA_PART_INDEXES views store key administrative data like the type of partitioning used and partition count, while DBA_PART_KEY_COLUMNS lists the columns involved in the partitioning process. Additionally, DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS provide details such as the high value specified in range partitioning, which aids in managing and verifying partition structures .

Partitioning offers several advantages in database administration and performance, such as simplifying data management by allowing operations like data loading, index creation, and backup to be performed on individual partitions. Unneeded partitions can be dropped quickly without extensive reorganization. Partitioning also improves query performance since only specific partitions need to be read for certain queries, reducing the amount of data scanned. Additionally, partitioning supports transparent integration with applications, requiring no change to SQL queries or DML statements. Drop or truncate operations on partitions do not generate redo log data, unlike DELETEs, enhancing performance .

The main types of partitioning in Oracle are Range, List, Hash, and Combined partitioning. Range partitioning is used when data is distributed across partitions based on a range of values, such as dates. List partitioning categorizes data based on a list of discrete values, useful for categorizing data by specific attributes like country names. Hash partitioning uses a hash algorithm to evenly distribute data across partitions, beneficial for balancing data load. Combined partitioning applies multiple partitioning strategies to optimize complex requirements, like range-hash or range-list, allowing for nuanced data organization .

Disabling partitioning in a Business Warehouse (BW) environment could severely detriment performance because partitioned objects are crucial for managing large volumes of data efficiently, minimizing query execution time, and enhancing load distribution. However, disabling partitioning might be permissible if BW functionalities are not utilized and are simply part of a larger package like NetWeaver04. In such scenarios, deactivating partitioning helps reduce unnecessary database license costs when Oracle is directly licensed, but for functional BW systems, partitioning must remain active to support optimal performance .

The Oracle syntax for creating a table with range partitioning involves defining the partitioning criteria and the value ranges. For example: `CREATE TABLE MONTHS (MONTH NUMBER, DAYS NUMBER) PARTITION BY RANGE (MONTH) (PARTITION WINTER VALUES LESS THAN (4), PARTITION SPRING VALUES LESS THAN (7), PARTITION SUMMER VALUES LESS THAN (10), PARTITION AUTUMN VALUES LESS THAN (13));`. This syntax specifies that the table `MONTHS` is partitioned based on the `MONTH` column, dividing the table into partitions named WINTER, SPRING, SUMMER, and AUTUMN, where each partition includes months falling into specified ranges. These partitions help manage data effectively by splitting them based on logical time periods .

You might also like