Tag Archives: DML

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:

ALTER SESSION ENABLE PARALLEL DML;

to force parallel behaviour, regardless of the parallel degree you have placed on an object:

ALTER SESSION FORCE PARALLEL DML;

For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:

ALTER SESSION ENABLE PARALLEL DML;

2. Run the parallel operation by specifying HInts in the statements:

INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;

UPDATE /*+ PARALLEL(EMP,4) */ EMP SET SAL = SAL*1.01 WHERE DEPTNO=10;

DELETE /*+ PARALLEL(EMP,4) */ FROM EMP WHERE DEPTNO=10;

If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints

Advertisements

INTRODUCTION TO DBMS & RDBMS

INTRODUCTION TO DBMS & RDBMS

A DBMS (Database Management System) is a software program used to manage a database. These programs enable users to access and modify database.It basically control the storage management and fetching of data from database.
 
A DBMS includes four main components, which are:
-Modeling Language,
-Data Structures,
-DB Query Language and Report Writer,
-Transaction Mechanism.
 
RDBMS
RDBMS is relational database management system. Basically, we can say that RDBMS Is an extension of DBMS systems.
• Main difference is that RDBMS (Relational database management system) applications store data in a tabular form, while DBMS applications store data as files.
• Normalization concept is added to RDBMS. whereas not present in DBMS system.
• DBMS systems used file to store data so that there is no relation between data. But in case of RDBMS relations are maintained between various tables using keys.
• RDBMS define integrity constraint where DBMS not.
• RDBMS support distributed databases where DBMS does not support.
 
TYPES OF LANGUAGE:
 
DDL – DATA DEFINITION LANGUAGE
The SQL sentences that are used to create these objects are called DDL’s or Data Definition Language. The SQL provides various commands for defining relation schemas, deleting relations, creating indexes and modify relation schemas. DDL is part of SQL which helps a user in defining the data structures into the database. Following are the various DDL commands are
• Alter table & Create table & drop table
• Create index & drop index
• Create view & drop view
 
DML – DATA MANIPULATION LANGUAGE
The SQL sentences used to manipulate data within these objects are called DML’s or Data Manipulation Language. It is language that enables users to access or manipulate data as organized by appropriate data model. By data manipulation we have:
• Retrieval of information stored in database.
• Insertion of new information into database.
• Deletion of information from database.
• Modification of data stored in database.
 
FOLLOWING ARE DML COMMANDS ARE:
• Select
• Update
• Delete
• Insert
 
DCL – DATA CONTROL LANGUAGE
The SQL sentences, which are used to control the behavior of these objects, are called DCL’s or Data Control Language. It is language used to control data and access to the database. Following are some DCL commands are
• Commit
• Rollback
• Save point
• Set transaction
 
A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database.
Examples of DCL commands include:
• GRANT to allow specified users to perform specified tasks.
• REVOKE to cancel previously granted or denied permissions.
 
The following privileges can be GRANTED TO or REVOKED FROM a user or role:
• CONNECT
• SELECT
• INSERT
• UPDATE
• DELETE
• EXECUTE
• USAGE