Results for VLDB


21 to 80 of 4391 results. Prev  Next
Results per page:   20  | 40 | 60


VLDB and Partitioning Guide, Release 19c

Setting the Degree of Parallelism

Adaptive ParallelismThe adaptive multiuser algorithm reduces the degree of parallelism as the load on the system increases. When using Oracle Database adaptive parallelism capabilities, the database uses an algorithm at SQL execution time to determine whether a parallel operation should receive the requested DOP or have its DOP lower to ensure the system is not overloaded. In a system that makes aggressive use of parallel

VLDB and Partitioning Guide, Release 19c

Index Partitioning

Management of Global Partitioned IndexesManagement of global partitioned indexes presents several challenges. Global partitioned indexes are harder to manage than local indexes because of the following: When the data in an underlying table partition is moved or removed ( SPLIT, MOVE, DROP, or TRUNCATE ), all partitions of a global index are affected. Consequently global indexes do not support partition independence. When an underlying table

VLDB and Partitioning Guide, Release 19c

Recommendations for Choosing a Partitioning Strategy

When to Use Hash PartitioningHash partitioning is useful for randomly distributing data across partitions based on a hashing algorithm, rather than grouping similar data. There are times when it is not obvious in which partition data should reside, although the partitioning key can be identified. Rather than group similar data, there are times when it is desirable to distribute data such that it does not correspond to a business

VLDB and Partitioning Guide, Release 19c

Tips for Tuning Parallel Execution

When to Increase INITRANSYou should increase the value of INITRANS under certain situations. If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header

VLDB and Partitioning Guide, Release 19c

About Initializing and Tuning Parameters for Parallel Execution

PARALLEL_MAX_SERVERSThe PARALLEL_MAX_SERVERS parameter specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle Database increases the number of processes from the number created at instance startup up to this value. For example, setting the value to 64 enables you to run four parallel queries simultaneously, if each query is using two slave

VLDB and Partitioning Guide, Release 19c

Partition-Wise Operations

Querying a Full Partition-Wise JoinYou can query using a full partition-wise join. Consider a large join between a sales table and a customer table on the column cust_id, as shown in Example 3-4. The query \"find the records of all customers who bought more than 100 articles in Quarter 3 of 1999\" is a typical example of a SQL statement performing such a join. Such a large join is typical in data warehousing environments. In this case,

VLDB and Partitioning Guide, Release 19c

Parallel Execution Concepts

Multiple ParallelizersEach parallel execution (PX) coordinator in an execution plan is called a parallelizer. The number of PX servers used by a SQL statement is determined by the statement degree of parallelism (DOP) and the number of parallelizers. Because the number of PX server sets for a parallelizer is limited to two, the number of PX servers for most statements is DOP*2. Some statements can have more than one parallelizer.

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

About Adding Partitions and SubpartitionsThis section introduces how to manually add new partitions to a partitioned table and explains why partitions cannot be specifically added to most partitioned indexes. This section contains the following topics: Adding a Partition to a Range-Partitioned Table Adding a Partition to a Hash-Partitioned Table Adding a Partition to a List-Partitioned Table Adding a Partition to an Interval-Partitioned

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Adding a Partition to a *-Range Partitioned TableThe example in this topic shows how to add a new partition to a [range | list | interval]-range partitioned table. The database automatically creates interval partitions for an interval-range partitioned table when data is inserted in a specific interval. You can specify a SUBPARTITION clause for naming and providing ranges for specific subpartitions. If no SUBPARTITION clause is specified, then the

VLDB and Partitioning Guide, Release 19c

Specifying Partitioning When Creating Tables and Indexes

About Creating Hash Partitioned Tables and Global IndexesThe PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the individual partitions and their tablespaces. The only attribute you can specify for hash partitions

VLDB and Partitioning Guide, Release 19c

Managing Data in Oracle Database With ILM

Partitioning for ILMPartitioning involves physically placing data according to a data value, and a frequently used technique is to partition information by date. Figure 5-1 illustrates a scenario where the orders for Q1, Q2, Q3, and Q4 are stored in individual partitions and the orders for previous years are stored in other partitions. Figure 5-1 Allocating Data Classes to a Partition Description of \"Figure 5-1 Allocating

VLDB and Partitioning Guide, Release 19c

Index Partitioning

Local Nonprefixed IndexesA local index is nonprefixed if it is not partitioned on a left prefix of the index columns or if the index key does not include the subpartitioning key. You cannot have a unique local nonprefixed index unless the partitioning key is a subset of the index key. Figure 3-5 illustrates an example of a local nonprefixed index. Figure 3-5 Local Nonprefixed Index Description of \"Figure 3-5 Local Nonprefixed

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Using ALTER TABLE to Rebuild an Index PartitionThe REBUILD UNUSABLE LOCAL INDEXES clause of ALTER TABLE MODIFY PARTITION enables you to rebuild an unusable index partition. However, the statement does not allow you to specify any new attributes for the rebuilt index partition. The following example finds and rebuilds any unusable local index partitions for table scubagear, partition p1. ALTER TABLE scubagear MODIFY PARTITION p1 REBUILD UNUSABLE

VLDB and Partitioning Guide, Release 19c

Partitioning Concepts

List PartitioningList partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way. For a table with a region column as the partitioning key, the East Sales Region partition might

VLDB and Partitioning Guide, Release 19c

Implementing an ILM Strategy With Heat Map and ADO

Enabling and Disabling Heat MapYou can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP clause. For example, the following SQL statement enables Heat Map tracking for the database instance. ALTER SYSTEM SET HEAT_MAP = ON; When Heat Map is enabled, all accesses are tracked by the in-memory activity tracking module. Objects in the SYSTEM and SYSAUX

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Merging Partitions in a *-Range Partitioned TableWhen you merge two *-range partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then a single subpartition with an upper boundary MAXVALUE is created for the new partition. For interval-range partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition

VLDB and Partitioning Guide, Release 19c

Types of Parallelism

Concurrent Execution of Union AllSet operators like UNION or UNION ALL consist of multiple queries (branches) combined to a single SQL statement. Traditionally, set operators are processed in a sequential manner. Individual branches can be processed in serial or parallel, but only one branch at a time, one branch after another. While this approach satisfies many use cases, there are situations where the processing of multiple branches

VLDB and Partitioning Guide, Release 19c

Storage Management for VLDBs

Oracle ASM Settings Specific to VLDBsConfiguration of Oracle Automatic Storage Management for VLDBs is similar to Oracle ASM configuration for non-VLDBs. Certain parameter values, such as the memory allocation to the Oracle ASM instance, may need a higher value. Oracle Database supports Oracle ASM variable allocation units. Large variable allocation units are beneficial for environments that use large sequential I/O operations. VLDBs

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

About Modifying Attributes of Tables, Partitions, and SubpartitionsThe modification of attributes of tables, partitions, and subpartitions is introduced in this topic. About Modifying Default Attributes About Modifying Real Attributes of Partitions

VLDB and Partitioning Guide, Release 19c

Managing Data in Oracle Database With ILM

Data RetentionThe retention policy describes how the data is to be retained, how long it must be kept, and what happens after data life. An example of a retention policy is a record must be stored in its original form, no modifications are allowed, it must be kept for seven years, and then it may be deleted. Using Oracle Database security, it is possible to ensure that data remains unchanged and that only authorized

VLDB and Partitioning Guide, Release 19c

About Initializing and Tuning Parameters for Parallel Execution

PARALLEL_SERVERS_TARGETThe PARALLEL_DEGREE_POLICY parameter specifies the number of parallel server processes allowed to run parallel statements before statement queuing is used. When PARALLEL_DEGREE_POLICY is set to AUTO, statements that require parallel execution are queued if the number of parallel processes currently in use on the system equals or is greater than PARALLEL_SERVERS_TARGET. This is not the maximum number

VLDB and Partitioning Guide, Release 19c

Partitioning Concepts

Collections in XMLType and Object DataPartitioning when using XMLType and object tables and columns offers the standard advantages of partitioning, such as enabling tables and indexes to be subdivided into smaller pieces, thus enabling these database objects to be managed and accessed at a finer level of granularity. When you partition an XMLType table or a table with an XMLType column using list, range, or hash partitioning, any ordered

VLDB and Partitioning Guide, Release 19c

Index Partitioning

Summary of Partitioned Index TypesA summary of partitioned index types is provided in this topic. Table 3-1 summarizes the types of partitioned indexes that Oracle supports. The key points are: If an index is local, then it is equipartitioned with the underlying table. Otherwise, it is global. A prefixed index is partitioned on a left prefix of the index columns. Otherwise, it is nonprefixed. Table 3-1 Types of Partitioned Indexes

VLDB and Partitioning Guide, Release 19c

Using Partitioning in an Online Transaction Processing Environment

Impact of a Partition Maintenance Operation on a Partitioned Table with Local IndexesWhen a partition maintenance operation takes place, Oracle Database locks the affected table partitions for any DML operation, except in the case of an ONLINE MOVE. Data in the affected partitions, except a DROP or TRUNCATE operation, is still fully accessible for any SELECT operation. Because local indexes are logically coupled with the table (data) partitions, only the local index partitions of

VLDB and Partitioning Guide, Release 19c

Using Partitioning in an Online Transaction Processing Environment

An Online Transaction Processing OLTP system is a common data processing system in today's enterprises.…Partitioning features are very useful for OLTP systems. Due to the explosive growth of online transaction processing (OLTP) systems and their user populations, partitioning is particularly useful for OLTP systems in addition to data warehousing environments Partitioning is often used for OLTP systems to reduce contention while supporting a very large user population. It also helps to address regulatory

VLDB and Partitioning Guide, Release 19c

Setting the Degree of Parallelism

Determining Degree of Parallelism in Auto DOPWith automatic degree of parallelism (DOP), the optimizer automatically determines the DOP for a statement based on the resource requirements of that statement. The optimizer uses the cost of all scan operations, such as a full table scan or index fast full scan, and the cost of all CPU operations in the execution plan to determine the necessary DOP. However, the optimizer limits the actual maximum

VLDB and Partitioning Guide, Release 19c

Using Partitioning in an Online Transaction Processing Environment

Deciding Whether to Partition IndexesDue to the selectivity of queries and high concurrency of OLTP applications, the choice of the right index strategy is indisputably an important decision for the use of partitioning in an OLTP environment. With less contention, the application can support a larger user population. The following basic rules explain the main benefits and trade-offs for the various possible index structures: A nonpartitioned

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Splitting a *-List PartitionYou can split a list partition with the ALTER TABLE SPLIT PARTITION statement. Splitting a partition of a *-list partitioned table is similar to the description in Splitting a Partition of a List-Partitioned Table. No subpartition literal value list can be specified for either of the new partitions. The new partitions inherit the subpartition descriptions from the original partition being split. The

VLDB and Partitioning Guide, Release 19c

Partitioning Concepts

Composite Range-Range PartitioningComposite range-range partitioning enables logical range partitioning along two dimensions. An example of composite range-range partitioning is partition by order_date and range subpartition by shipping_date.

VLDB and Partitioning Guide, Release 19c

Storage Management for VLDBs

Scalability and ManageabilityStorage scalability and management is an important factor in a VLDB environment. A very important … characteristic of a VLDB is its large size. The large size introduces the following challenges … components. A small relative growth in a VLDB may amount to a significant absolute growth, resulting in

VLDB and Partitioning Guide, Release 19c

Specifying Composite Partitioning When Creating Tables

Creating Composite Interval-Range Partitioned TablesTo define range subpartitions for future interval-range partitions, you must use the subpartition template. If you do not use the subpartition template, then the only subpartition that is created for every interval partition is a range subpartition with the MAXVALUE upper boundary. Example 4-19 shows the sales table, interval partitioned using daily intervals on time_id, with range subpartitions by

VLDB and Partitioning Guide, Release 19c

Controlling the Validity and Visibility of Data in Oracle Database

Limitations and Restrictions With In-Database Archiving and Temporal ValidityThis topic lists the limitations and restrictions associated with In-Database Archiving and Temporal Validity. The limitations and restrictions include: ILM is not supported with OLTP table compression for Temporal Validity. Segment-level ILM and compression is supported if partitioned on the end-time columns. ILM is not supported with OLTP table compression for in-database archiving. Segment-level

VLDB and Partitioning Guide, Release 19c

Index Partitioning

Global Partitioned IndexesIn a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table partitions or subpartitions. A global index can be range or hash partitioned, though it can be defined on any type of partitioned table. A global index is created by specifying the GLOBAL attribute. The database administrator is responsible for defining the initial partitioning

VLDB and Partitioning Guide, Release 19c

Using Partitioning in a Data Warehouse Environment

Removing Data from TablesData warehouses commonly keep a time window of data. For example, three years of historical data is stored. Partitioning makes it very easy to purge data from a table. You can use the DROP PARTITION or TRUNCATE PARTITION statements to purge data. Common strategies also include using a partition exchange load to unload the data from the table and replacing the partition with an empty table before dropping

VLDB and Partitioning Guide, Release 19c

Using Partitioning in a Data Warehouse Environment

Partitioning for Performance in a Data WarehouseGood performance is a requirement for a successful data warehouse. Analyses run against the database should return within a reasonable amount of time, even if the queries access large amounts of data in tables that are terabytes in size. Partitioning increases the speed of data access and application processing, which results in successful data warehouses that are not prohibitively expensive. This

VLDB and Partitioning Guide, Release 19c

Using Partitioning in an Online Transaction Processing Environment

Performance in an Online Transaction Processing EnvironmentPerformance in OLTP environments heavily relies on efficient index access, thus the choice of the most appropriate index strategy becomes crucial. The following sections discuss best practices for deciding whether to partition indexes in an OLTP environment. Deciding Whether to Partition Indexes How to Use Partitioning on Index-Organized Tables

VLDB and Partitioning Guide, Release 19c

Types of Parallelism

FOREIGN KEY (Referential Integrity)Restrictions for referential integrity occur whenever a DML operation on one table could cause a recursive DML operation on another table. These restrictions also apply when, to perform an integrity check, it is necessary to see simultaneously all changes made to the object being modified. Table 8-1 lists all of the operations that are possible on tables that are involved in referential integrity

VLDB and Partitioning Guide, Release 19c

Introduction to Very Large Databases

Partitioning provides support for very large tables and indexes by subdividing them into smaller and…… Partitioning is a key component of the VLDB strategy. Modern enterprises frequently run mission … (VLDB), and must devise methods to meet those challenges. This chapter contains the following … sections: Introduction to Partitioning VLDB and Partitioning Partitioning As the Foundation for

VLDB and Partitioning Guide, Release 19c

Partitioning Concepts

System PartitioningSystem partitioning enables application-controlled partitioning without having the database controlling the data placement. The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application. For example, an attempt to insert into a system partitioned

VLDB and Partitioning Guide, Release 19c

Managing Data in Oracle Database With ILM

Immutabilityis concerned with proving to an external party that data is complete and has not been modified. Cryptographic or digital signatures can be generated by Oracle Database and retained either inside or outside of the database, to show that data has not been altered.

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Dropping Index PartitionsYou cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table. If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

About Modifying List Partitions: Dropping ValuesList partitioning enables you to optionally drop literal values from the defining value list. This section contains the following topics: Dropping Values from a List Partition Dropping Values from a List Subpartition

VLDB and Partitioning Guide, Release 19c

Storage Management for VLDBs

RAID 5 StripingRAID 5 configurations spread data across the available devices in the RAID group using a hardware-specific stripe size. Consequently, multiple devices and channels are used to read and write data. Due to its more complex parity calculation, not all storage devices support RAID 5 configurations.

VLDB and Partitioning Guide, Release 19c

Backing Up and Recovering VLDBs

Oracle Backup and RecoveryIn general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the database after any kind of data loss. A backup is a representative copy of data. This copy can include important parts of a database such as the control file, archived redo logs, and data files. A backup protects data from application error and

VLDB and Partitioning Guide, Release 19c

Partitioning Concepts

Global Nonpartitioned IndexesGlobal nonpartitioned indexes behave just like local nonpartitioned indexes. Figure 2-8 offers a graphical view of global nonpartitioned indexes. Figure 2-8 Global Nonpartitioned Index Description of \"Figure 2-8 Global Nonpartitioned Index\"

VLDB and Partitioning Guide, Release 19c

Backing Up and Recovering VLDBs

Best Practice 6: Plan for NOLOGGING Operations in Your Backup/Recovery StrategyIn general, a high priority for a data warehouse is performance. Not only must the data warehouse provide good query performance for online users, but the data warehouse must also be efficient during the extract, transform, and load (ETL) process so that large amounts of data can be loaded in the shortest amount of time. One common optimization used by data warehouses is to execute bulk-data operations

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Truncating a Partition Containing Data and Referential Integrity ConstraintsIf a partition contains data and has referential integrity constraints, then you cannot truncate the partition. However, if no other data is referencing any data in the partition to remove, then you can use one of several methods. Choose either of the following methods (method 1 or 2) to truncate the table partition. Method 1 Disable the integrity constraints, run the ALTER TABLE TRUNCATE PARTITION

VLDB and Partitioning Guide, Release 19c

Tips for Tuning Parallel Execution

Optimizing Performance by Creating and Populating Tables in ParallelTo optimize parallel execution performance for queries that retrieve large result sets, create and populate tables in parallel. Oracle Database cannot return results to a user process in parallel. If a query returns a large number of rows, execution of the query might indeed be faster. However, the user process can receive the rows only serially. To optimize parallel execution performance for queries

VLDB and Partitioning Guide, Release 19c

Parallel Execution Concepts

Partition GranulesWhen partition granules are used, a parallel execution (PX) server works on an entire partition or subpartition of a table or index. Because partition granules are statically determined by the structure of the table or index when a table or index is created, partition granules do not give you the flexibility in executing an operation in parallel that block granules do. The maximum allowable degree

VLDB and Partitioning Guide, Release 19c

Types of Parallelism

NOT NULL and CHECKThe integrity constraints for NOT NULL and CHECK are discussed in this topic. NOT NULL and CHECK integrity constraints are allowed. They are not a problem for parallel DML because they are enforced on the column and row level, respectively.

VLDB and Partitioning Guide, Release 19c

Specifying Partitioning When Creating Tables and Indexes

Using Table Compression with Partitioned TablesFor heap-organized partitioned tables, you can compress some or all partitions using table compression. The compression attribute can be declared for a tablespace, a table, or a partition of a table. Whenever the compress attribute is not specified, it is inherited like any other storage attribute. Example 4-13 creates a range-partitioned table with one compressed partition costs_old. The compression

VLDB and Partitioning Guide, Release 19c

Types of Parallelism

When to Use Parallel DMLParallel DML is useful in a decision support system (DSS) environment where the performance and scalability of accessing large objects are important. Parallel DML complements parallel query in providing you with both querying and updating capabilities for your DSS databases. The overhead of setting up parallelism makes parallel DML operations not feasible for short OLTP transactions. However, parallel

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Splitting a Partition of a Range-Partitioned TableYou can split a range partition using the ALTER TABLE SPLIT PARTITION statement. In the SQL statement, you must specify values of the partitioning key column within the range of the partition at which to split the partition. You can optionally specify new attributes for the partitions resulting from the split. If there are local indexes defined on the table, this statement also splits the matching

VLDB and Partitioning Guide, Release 19c

Types of Parallelism

Process RecoveryRecovery from the failure of a parallel execution coordinator or parallel execution server is performed by the PMON process. If a parallel execution server or a parallel execution coordinator fails, then PMON rolls back the work from that process and all other processes in the transaction roll back their changes.

VLDB and Partitioning Guide, Release 19c

Implementing an ILM System Manually Using Partitioning

Implementing an ILM System Manually Using PartitioningYou can manually implement an Information Lifecycle Management (ILM) system using partitioning. Example 5-12 illustrates how to manually create storage tiers and partition a table across those storage tiers and then setup a virtual private database (VPD) policy on that database to restrict access to the online archive tier data. See Also: Oracle Database SQL Language Reference for information about

VLDB and Partitioning Guide, Release 19c

Storage Management for VLDBs

Stripe and Mirror Everything (SAME)The stripe and mirror everything (SAME) methodology has been recommended by Oracle for many years and is a means to optimize high availability, performance, and manageability. To simplify the configuration further, a fixed stripe size of 1 MB is recommended in the SAME methodology as a good starting point for both OLTP and data warehouse systems. Oracle ASM implements the SAME methodology and adds

VLDB and Partitioning Guide, Release 19c

Maintenance Operations for Partitioned Tables and Indexes

Splitting Index PartitionsYou cannot explicitly split a partition in a local index. A local index partition is split only when you split a partition in the underlying table. However, you can split a global index partition as is done in the following example: ALTER INDEX quon1 SPLIT PARTITION canada AT ( 100 ) INTO PARTITION canada1..., PARTITION canada2...); ALTER INDEX quon1 REBUILD PARTITION canada1; ALTER INDEX quon1 REBUILD

VLDB and Partitioning Guide, Release 19c

Types of Parallelism

Creating Intermediate Summary TablesIn a DSS environment, many applications require complex computations that involve constructing and manipulating many large intermediate summary tables. These summary tables are often temporary and frequently do not need to be logged. Parallel DML can speed up the operations against these large intermediate tables. One benefit is that you can put incremental results in the intermediate tables and perform

VLDB and Partitioning Guide, Release 19c

About Initializing and Tuning Parameters for Parallel Execution

Monitor Memory Usage After Processing BeginsWhether you are using automated or manual tuning, you should monitor usage on an on-going basis to ensure the size of memory is not too large or too small. The formulas in this section are just starting points. To ensure the correct memory size, tune the shared pool using the following query: SELECT POOL, NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL LIKE '%pool%' GROUP BY ROLLUP (POOL, NAME); Your output

VLDB and Partitioning Guide, Release 19c

List of Tables

List of Tables3-1        Types of Partitioned Indexes 3-2        Comparing Prefixed Local, Nonprefixed Local, and Global Indexes 4-1        ALTER TABLE Maintenance Operations for Table Partitions 4-2        ALTER TABLE Maintenance Operations for Table Subpartitions 4-3        ALTER INDEX Maintenance Operations for Index Partitions 4-4        Views With Information Specific to Partitioned Tables and Indexes 5-1        Cost





Was this page helpful?

Integrated Cloud Applications & Platform Services