Translate

ORACLE Table Locking

ORACLE Table Locking

Locking protects a table when several users are accessing the same table. Locking is a concurrency control technique in oracle. It helps in data integrity while allowing maximum concurrency access to data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.

Handling PL/SQL Errors

How do we avoid abnormal termination of code in ORACLE? What are the different ways of handling Exceptions?

Program flow when the Exception has not been handled


Program flow when the Exception has been trapped and handled



ORACLE BUILT-IN PROCEDURE

How RAISE_APPLICATION_ERROR ( ) used in ORACLE? How we define Custom Error messages in ORACLE?


RAISE_APPLICATION_ERROR ( ) allows users to create custom error messages. 
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Exception Trapping Functions

Exception Trapping Using: SQLCODE and SQLERRM
Oracle uses two built in functions for catching exceptions and getting its information, SQLCODE and SQLERRM.

WHERE/Having Clause Alternative

Few cases where these clauses should be used differently

Try to avoid operations on database objects referenced in the WHERE clause.

ORACLE Triggers

What are Triggers and types of Triggers in ORACLE?
  • Triggers are Reusable PL/SQL programs.
  • It cannot be called on its own- they are associated with Tables.
  • Triggers are fired implicitly on the tables/views on which they are created.

Identify columns having all NULLs

How to find Columns having all NULLs in a Table?

There can be different ways and you can have a look on below code will identify columns having all NULLs. Similarly, you can write it to Delete such columns from Table if you don't want them in your table.

Table Vs. Materialized View

VIEWS in ORACLE

What are different Types of Views in ORACLE? What is the difference between Simple View and Complex View?
  • View is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables. A view contains no data itself.
  •  A view is simply any SELECT query that has been given a name and saved in the database.  For this reason, a view is sometimes called a named query or a stored query.
Benefits of using Views
  • Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing.
  • Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.
The syntax for the CREATE VIEW Statement in Oracle/PLSQL is:
CREATE VIEW View_Name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

Feature
Simple Views
Complex Views
Number of tables
One
One or more
Contain functions
No
Yes
Contain groups of data
No
Yes
DML operations through a view
Yes
Not always

Synonyms In ORACLE

Why we use Synonyms in ORACLE?

A Synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package.
  • A Public Synonym is owned by the user group PUBLIC and every user in a database can access it.
  • A Private Synonym is in the schema of a specific user who has control over its availability to others.

How Index stored in DB and Improve query Performance?

SELECT * FROM Employee
WHERE Employee_Name = 'Jesus'

For more details on Indexes and it's TypesClick here.

What is Local and Global Indexes?

Local and Global Index
Local and Global index usage comes during the Partitioning of the table.
When using Oracle partitioning, you can specify the "global" or "local" parameter in the create index syntax.

What is Clustered and Non-Clustered Index?

Clustered and Non-Clustered INDEX

Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. In case of non-clustered index, the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

ORACLE Indexes

What are Indexes in ORACLE, Type of indexes and Advantages/Disadvantages of Indexes?
  • An index is a memory object basically used to speed up the performance of queries & allowing faster retrieval of records. Basically helps in improving the Execution Plan of the Query. To read more about Execution Plan - Click here.
  • Index is a physical structure contains pointers to the data. To know how Indexes are stored in DB and improve Query Performance- Click here.
  • An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
Advantages of Indexes:
  •   Avoid Full Table Scan and also used to avoid a table access altogether.
  •   Indexes Speed up Select. Their use in queries usually results in much better performance.
  •   They can be used for sorting. A post-fetch-sort operation can be eliminated.
Disadvantages of Indexes:
  •  Indexes slow down INSERT, UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field. INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.
  • Indexes take additional disk space. Space increases with the number of fields used and the length of the fields.
We have mainly two different Indexes

Implicit index and Explicit index.

Open Parameterized Cursor in Different Ways

To Understand about Cursor and it's Types, you can check the below post first- 
http://tipsfororacle.blogspot.in/2016/09/cursors-and-cursor-types.html

Now Let’s try opening Parameterized Cursor in below different ways-

Sub Queries and Types of Sub Queries

What are Sub-Queries Scalar and Co-Related Sub Queries in ORACLE? 
Sub Query (Query within another Query)
Subquery executes only once and gives the output to outer query then outer query executes). So subquery returning one output value and based on that output value outer query is going to be executed.

COMMIT Inside Trigger

How PRAGMA AUTONOMOUS_TRANSACTION is used in Trigger?Can we have COMMIT inside Trigger?

Yes, you can commit inside the trigger.

PRIMARY Vs. UNIQUE Key

What is the difference between PRIMARY Key and UNIQUE Key?

Primary key and unique are Entity integrity constraints
  • Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.
  • Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values. (In oracle, one null is not equal to another null).

Difference Between %TYPE and %ROWTYPE

%TYPE Vs. %RowType
  • %TYPE  provides  the data type of a variable or a database column to that variable. 
  • %ROWTYPE  provides the record type that represents a entire row of a table or view or columns selected in the cursor. 

WITH Clause in ORACLE

What is WITH Clause in Oracle? (The WITH clause to simplify complex SQL)

  • Oracle With Clause is similar to temporary tables, where you store the data once and read it multiple times in your sql query.
  • Oracle With Clause is used when a sub-query is executed multiple times.

DECODE Vs. CASE


What is the Difference Between DECODE and CASE? Which one is useful and when?
DECODE and CASE statements in Oracle both provide a conditional construct, of this form:
if A = n1 then A1
else if A = n2 then A2
else X

ROWNUM Vs. ROW_NUMBER()

What is the Difference between ROWNUM And ROW_NUMBER()?
·       ROWNUM is a "Pseudocolumn" that assigns a number to each row returned by a query.
·       ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows.

ROWNUM Vs. ROWID

What is the difference between ROWNUM and ROWID? Does Oracle make a distinction between a ROWID and ROWNUM?  

Rownum (Numeric) = Generated Sequence Number of your output. For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. So you can limit the number of rows in your result.

Rowid (Hexadecimal) =
Generated automatically at the time of insertion of row. For each row in the database, the ROWID pseudo column returns the address of the row.

INSERT And DELETE EXECUTION Plan

Why Insert is faster than Delete?
  •  If we are deleting any records Oracle have to search the values which we are deleting from the tablespaces, indexes etc. but while we are inserting any record oracle just place that record into the table name specified.

GTT in ORACLE

What are Global Temporary Tables (GTT) in ORACLE and how to use them?

  •  Data stored temporarily. Data stored as long as the session or transaction lasts and is private for each              session.
  •  The Definition is visible to all sessions.

CHAR NCHAR VARCHAR VARCHAR2 NVARCHAR

Difference between CHAR NCHAR VARCHAR VARCHAR2 NVARCHAR

  • nchar and nvarchar can store Unicode characters.
  • char and varchar cannot store Unicode characters.
  • char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.

UNION Vs.UNION ALL(SET Operators)

What is the difference between UNION and UNION ALL?

UNION and UNION ALL are set operators used to combine result set from different set of queries. Let's understand the difference between each of them.

UNION
UNION combines the result set of two or more queries into a single result set. This result set includes all the rows that belong to all queries in the UNION.
The following points need to be considered when using the UNION operator:

How Cursor Works Internally?

Why Do we need Cursor and How Cursor Works Internally?
Before understanding the process let’s check why do we need Cursor.

Why Do we need Cursor?

Cursors and Cursor Types

Why we need Cursors? What are the advantages of using different types of Cursors?

  •  Cursor-Is a memory object that will be stored inside your RAM.
  •  Whenever Cursor executed inside the PL/SQL block memory allocated/deallocated automatically based on     the type of cursor.

Separate Numeric/Non Numeric/Date values


There are different ways to separate Numeric and character values from a Column. We will understand this with the help below different ways.

Analytic Functions Vs. Aggregate Functions

What is the Difference Between Analytical Functions and Aggregate Functions?

Like aggregate functions, analytic functions return aggregate results, but analytics do not group the result set. Instead, they return the group value multiple times with each record, allowing further analysis. Let’s understand this in detail.

Joins in ORACLE

What are different types of Joins in ORACLE?

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement. There are different types of Oracle joins:

NOT IN Vs. NOT Exists

What is the Difference Between Not IN and Not Exists Operator?

In Oracle, a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.
“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately.

IN and EXISTS Operator

What is the Difference Between IN and EXISTS Operator and how they can be used for different purposes?

Oracle IN operator and EXISTS operator work for the same purpose i.e. they both check for record correlation between the main query and the sub query. We can compare the working of IN and EXISTS operator. Let's see some differences below :

How Count() Function behaves with Different Parameters

SQL COUNT() Function
The COUNT() function returns the number of rows that matches a specified criteria. It is an aggregate function that counts the number of rows accessed in an expression

Delete Vs. Truncate Vs. Drop

What is the Difference between TRUNCATE, DELETE and DROP Commands?











Find nth Highest/Minimum Salary from Table

Write a Query to get Highest/Minimum Salary from Table. Also, a query to find nth  Highest/Minimum Salary where n can be any number?

Maximum/Minimum/Average Salary from Table- 

Easiest way to solve this problem is to use Aggregate Functions. This can be done by using MAX/MIN/AVG Functions available in Oracle. MAX function will return the Maximum value for the column used in function and MIN will return the Minimum value for that column.

How to Identify and Remove Duplicate Records from Table?

Reason -  We made a mistake, and allowed to insert duplicate values. Now we need to remove/change the duplicates from the Database permanently.  So, we need to find them first.

MUTATING Table Error and How to Resolve it?

Mutating table Error Occurs when Trigger is Querying or Modifying a “Mutating Table”

Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger. So basically we are trying to select records in the trigger from the table that owns the trigger. This creates inconsistency and Oracle throws a mutating error.