What is Oracle Wallet

There are often situations where we have to give database access credentials in a shell script. This can be a security issue as database connection details are explicitly written in these scripts. Oracle provides an option of creating an external password storage mechanism. 

Wallet is simply a directory in database server where passwords are written in an encrypted form. The wallet location is configured in SQLNET.ora file  and a stored password can be retrieved/used by referencing a TNS alias configured in TNSNAMES.ORA file. Now instead of using explicit db connection details, one can use this TNS alias in shell scripts to connect to database.

What is the difference between an Oracle User and an Oracle Schema?

An Oracle schema is a collection of database objects like tables, functions, indexes etc A user "owns" a schema. When a user is created, it is given access to a schema with same name. A user can be given access to objects of other schema.

For all intents and purposes you can consider a user to be a schema and a schema to be a user.

Cursor and Ref Cursor

A cursor is a name for a structure in memory called a private SQL area, which the server allocates at run time for each SQL statement. When ever an SQL statement is executed, a Oracle database server automatically allocates a cursor that will hold the context(info about the SQL statement like variables used, memory addesses of those variables, parsed version of SQL statement etc) of that SQL statement. This is known as implicit cursor.

Cursors can also be defined explicitly to handle more than 1 row in a program. They are declared, opened, fetched and closed in a PL/SQL program and can be used for static SQL queries.

e.g
 declare
emp_name varchar2(15);
CURSOR my_cur is select name from emp;
BEGIN
OPEN my_cur;
LOOP
FETCH my_cur INTO emp_name;
EXIT WHEN my_cur%NOTFOUND;
dbms_output.put_line('The employee name is: '||emp_name);
end loop;
CLOSE my_cur;
END;
/

REF CURSOR is used in case of dynamic SQL queries. REF CURSOR is not a cursor. Instead, its a data type that will,at runtime, hold a pointer to a place in memory where a cursor really lives. So basically, its a pointer to a cursor or a reference to a cursor. Its declared as follows:

DECLARE
TYPE refcur IS REF CURSOR;
my_ref refcur;
emp_name varchar2(15);
BEGIN
OPEN my_ref FOR 'select name from emp' ;
LOOP
FETCH my_ref INTO emp_name;
EXIT WHEN my_ref%NOTFOUND;
dbms_output.put_line('The employee name is: '||emp_name);
end loop;
CLOSE my_ref;
END;
/

Unusable Indexes

To load data in a table efficiently, we can make Indexes as UNUSABLE. However, the TRUNCATE operation should be done before making the indexes unusable. If table is truncated after the indexes are made unusable, then it will have no effect. The TRUNCATE will automatically make the index USABLE. so the correct order to load data will be as follows:

• truncate the table
• set the indexes to unusable
• load the data
• rebuild the indexes

All about TNS and Making a connection request to an Oracle Database

What happens when an Oracle client issues a request to connect to a database e.g

sqlplus scott/tiger@ora10g.localdomain

Here SQLPLUS is the client program, scott/tiger is the username/password and ora10g.localdomain is a TNS service name. TNS stans for Transparent Network Substrate and is a foundation software built into Oracle client to handle remote connections and allowing peer-to-peer communication. The TNS connection string tells the Oracle software how to connect to the remote database. Generally, the client software will read a configuration file "tnsnames.ora" which contains the details such as hostname, port number and the service name of the database on the host to which we wish to connect. A sample TNS entry:

gaurav_ora =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora10g)

    )

  )

Here host machine is localhost and "ora10g" is the database service name. 1521 is the port number on which a "TNS listener" process will accept connections.

So now, our client software knows where to connect to. It will open a TCP/IP socket connection to the server with the hostname localhost on port 1521. The TNS Listener process inspects this request and using its own configuration file either accepts or rejects the request.

How to find out the referenced table and column name if the foreign key constraint name is given

The following query will give the desired result:

select
ac.table_name child_tab,
acc1.column_name child_col,
ac.constraint_name foreign_key,
acc.constraint_name primary_key,
acc.table_name parent_tab,
acc.column_name parent_col
from
all_constraints ac,
all_cons_columns acc,
all_cons_columns acc1
where
ac.owner=acc.owner
and acc.constraint_name=ac.r_constraint_name
and ac.constraint_name=acc1.constraint_name
and ac.constraint_name= :foreign_key_constraint_name

Constraints

In Oracle, constraints are a facility to enforce rules to make sure that only allowable data values are stored in the database(i.e to make sure the data integrity in database). All constraints have a name. The developer who defines the table or adds a constraint can name it at that time. However, if the name is not supplied, Oracle will assign a system generated name that will uniquely identify the constraint. System generated names are pre-fixed with SYS_C (for "system constraint") followed by a 7 digit integer.

Types of Constraints:

1. NOT NULL: If as per business logic, a column or a set of columns in a table can not allow NULL values, then NOT NULL constraint can be used to enforce this rule.

e.g

alter table SALES modify (cust_id NOT NULL);

2. UNIQUE: If as per business logic, a colum or a set of columns in a table need to store unique values, then, UNIQUE constraint can be used to enforce this rule.

e.g

create table test (col1 number UNIQUE);

UNIQUE constraints allow NULL values to be stored.

3. PRIMARY KEY : Primary Key constraint is a combination of NOT NULL and UNIQUE constraints. The column or the set of columns on which Primary Key is defined, will allow only unique and not null values. There can only be 1 (and only 1) primary key in an Oracle table.

e.g

create table test (col1 number PRIMARY KEY);

4. FOREIGN KEY:  It is frequenly required that data in one table should be validated by comparing it to data in other table.(e.g if you add a new order in your ORDERS table, you must cross check that a valid product corresponding to this order is present in your PRODUCTS table). To achieve this kind of data integrity, foeign key constrained is used. This type of validation is also known as referential integrity. A foreign key constraint always makes refrence to a Primary key or a unique constraint of other table. The table that has foreign key defined is called referencing table. The table that has Primary key or Unique constraint defined is called referenced table.

e.g

create table orders
(
  order_no number primary key,
  customer_name varchar2(10),
  constraint cons_prod_fk foreign key(prod_no) references product(prod_no)
);

> If you define the foreign key constraint with 'ON DELETE CASCADE' option, then if any rows are deleted from the referenced table, then the corresponding rows will also be deleted from the referencing table.

> NULL values are allowed in Foreign Key columns.

5. CHECK:  Check constraints are used to enforce one or more conditions to be checked for the data row.

e.g

alter table customers add constraint customer_credit_limit CHECK (credit_limit <= 1000)

Some additional information regarding Constraints:

• Constraint names can be found in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.

• Constraints can, at any time, be either enabled or disabled. When you create, disable or enable a constraint, you may speify some other information regarding how the constraint behaves. An ebabled constraint can have two options VALIDATE and NOVALIDATE. VALIDATE will validate the existing data in the table while NOVALIDATE will not validate the existing data afyter the constraint is enabled.

• When you create or enable a Primary key or Unique constraint, Oracle will create a unique index on the columns of that constraint. Foreign key constraints do not enforce an automatic creation of index.However it is worthwhile to build an index on the columns of each foreign key constraint. Without an index on the corresponding columns in the child table, Oracle is forced to take out a table lock on the child while it performs the DELETE on the parent. If an index is existing, Orcale uses it to identify and lock just the necessary rows in the child while the parent row is deleted.

Oracle BULK Loader

SQL *Loader (sqlldr) is a bulk loader utility to load data from external files into Oracle database. To use this utility, a control file is required which specifies how data should be loaded into the database and a data file is required which specifies what data should be loaded.  A sample control file is as follows:

LOAD DATA

INFILE "datafile"

APPEND INTO TABLE "tablename"

FIELDS TERMINATED BY "separater"

("list of all attribute names to be loaded")

 

A sample data file can be of following form:

 

1,'Gaurav'

2, 'ABC'

3,'PQR'

SQL, PL/SQL and DataWarehousing Interview Questions

Q1. What are collections? What are the advantages of using Collections?

PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays.Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection. When declaring collections, you use a TYPE definition. The adantage of collections is that Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.You can use collections to move data into  and out of database tables using high-performance language features known as bulk SQL.

Q2. Tell some new features of Oracle 10g from a developer's point of view.

1. UNIX-style regular expressions  can be used  while performing queries and string manipulations. You use the REGEXP_LIKE operator in SQL queries, and the REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR functions anywhere you would use INSTR, REPLACE, and SUBSTR.

2. Flashbach query functions introduced. The functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN let you translate between a date and time, and the system change number that represents the database state at a point in time.

3. Nested tables defined in PL/SQL have many more operations than previously. You can compare nested tables for equality, test whether an element is a member of a nested table, test whether one nested table is a subset of another, perform set operations such as union and intersection, and much more.

4. New datatypes BINARY_FLOAT and BINARY_DOUBLE represent floating-point numbers in IEEE 754 format. These types are useful for scientific computation where you exchange data with other programs and languages that use the IEEE 754 standard for floating-point.

5. Staring with Oracle 10g release 1, the BINARY_INTEGER datatype was changed to be identical to PLS_INTEGER so the datatypes can be used interchangeably.

Q3. What is high water mark?

Each table's data is stored in its own data segment. The high water mark is the boundary between used and unused space in a segment. When a table is deleted, the high water mark remains unchanged but when a table is truncated, the high water mark drops.

Q4. What are mutating tables?

Mutating means changing.A mutating table is a table that is currently being modified by an update, delete or insert statement. When a trigger tries to reference a table that is in state of flux(being changed), it is considered mutating and raises an error since oracle should never return inconsistenet data.

Q5 How does PL/SQL offers performance benefits over SQL.

Without PL/SQL, Oracle must process SQL statements one at a time. Programs that

issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead. With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application.You can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution

Q6 Define %TYPE and %ROWTYPE

Ans The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named last_name in a table named employees. To declare a variable named v_last_name that has the same datatype as column title, use dot notation and the %TYPE attribute, as follows:

v_last_name employees.last_name%TYPE;

Declaring v_last_name with %TYPE has two advantages. First, you need not know the exact datatype of last_name. Second, if you change the database definition of last_name, perhaps to make it a longer character string, the datatype of v_last_name changes accordingly at run time.

%ROWTYPE

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

Q7. Define NOT NULL constraint

Ans:  NOT NULL constraint puts a constraint on the value of a varaible. It can't be NULL. If NULL is tried to be inserted into such a variable, PL/SQL raises the predefined exception VALUE_ERROR.

Q8 Whats the difference between RANK() and DENSE_RANK() functions?
Ans RANK() will leave a gap in the ranking sequence when there are ties e.g rather than 1,2,2,3 RANK() will return 1,2,2,4.

Q9 Whats the difference between CASE and DECODE?

DECODE works only on equality operator. CASE can handle inquality operators as well  e.g <,>,<=,>=,!=


Q10 Take us through the life-cycle of an SQL query when fired against the database.


These are the following steps during the execution of the SQL query:

• A cursor is established to hold the context of the SQL statement. A cursor is a connection to a specific area in the Program Global Area (PGA) that contains information about the specific SQL statement.
• The SQL statement is now parsed. Parsing is the process of checking an SQL statement to ensure that it is valid(syntax checking), as well as creating an optimal execution plan for the statement.
• Preparation for the return of result set is done.
• Bind variables are supplied with run time values.
• The final statement is now executed using the execution plan that has been prepared.
• Oracle returns the result set.

Q11 What is an Oracle Instance?


An oracle instance is a software service that act as an intermediary between application requests and Oracle database. It is a set of some background processes and  shared memory. An Oracle instance is either started as part of the process of booting a server or can be started explicitly with commands. There are 3 steps in the start-up process:

• Starting the instance process itself
• Mounting the database, which consist of opening the control files for the instance
• Opening the database which makes the database available for user requests.

An instance can mount and open only a single database ever. However, a database can ne mounted and opened by one or more instances(using Real Application Clusters)

(I like to imagine instance as a 'database armed and ready for action)


Q12 Define SGA and PGA


These are memory areas used by an Individual oracle instance.


SGA:
System Global Area is an area of memory that is accessible to all user processes of an Oracle instance. 3 main areas use by SGA are :

• Redo log buffer which holds information used for recovery until the information can be written to the redo log
• The shared pool which holds information that can be shared across user processes such as execution plans for SQL statements, compiled stored procedures and information retrieved from the Oracle data dictionary.
• The database buffer pools which are used to hold data blocks.

PGA:

Program Global Area is an area of memory that is just available to a single server process. PGA contains items like user varaibles and cursor information for an individual user's SQL statement, such as number of rows that have been retreived so far. The PGA is also used for sorting data for an individual user process.

Q13 What is hard and soft parsing?

During parsing, after validating an SQL statement, the Oracle database computes a hash alogorithm to check whether a version of statement exists in the shared pool of SGA. If Oracle can not find the statement in shared pool, the instance prepares the execution plan for it. This is called hard parsing. If Oracle finds the statement in the shared pool, it simply retrieves the execution plan for the statment. This is called soft parsing. Performance is improved by increasing the chances for soft parsing

Q14 What are bind variables and how they improve performance of a SQL code?

A bind variable is a value in an SQL statement that is not known until the statement is run. In an SQL statement , the bind variables act as a placeholder for this subsequent data. In an Oracle SQL syntax, a bind variable is indicated by a colon (:) before the variable name.

e.g Select ename from emp where emp_id = :n_empid

The use of bind variables helps Oracle to do soft parsing of the SQL statements which enhances performance. 

Consider following 2 statements :

Select ename from emp where emp_id = 7

Select ename from emp where emp_id = 5

Both these statements should use identical optimizer plans. If Oracle finds the execution plan for first statement, it should use the same for the second. However it will not since, the hash algorithm generates the execution plans based on characters in the SQL statement. Using bind variables, this problem can be resolved.

Select ename from emp where emp_id = :n_empid

Using this syntax, the execution plan can be retrieved for n number of such statements.

Q15 What is the difference between 'DELETE' and 'TRUNCATE' command?

DELETE is used to delete the rows from a table. However it retains the rows to be deleted in redo log buffers and if ROLLBACK is issued, the rows restored back into the table. Only when COMMIT is executed after DELETE, the rows are permanently deleted.

TRUNCATE requires no COMMIT command to delete the rows. As soon as TRUNCATE is fired, rows are permnently deleted. Thats why its also called as DDL statement. TRUNCATE causes the movement of High Water Mark.

TRUNCATE is faster than DELETE since TRUNCATE do not requires to hold the rows in redo log buffers.


Q16 What is a surrogate key? How it is different from a natural key? What are the advantages of a surrogate key?


A surrogate key (also known as artificial or identity key) is a system generated key with no business value. A surrogate key is a substitution for the natural primary key. 
It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table. Data warehouses typically use a surrogate key for the dimension tables primary keys.

A natural key is a value that has a business meaning and people use them e.g SSN, Customer Id 

Advantages of Surrogate Keys:

• Natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.And in many cases natural primary key is a combination of more than 1 columns. In such cases, the usage of surrogate key simplifies the design.
• Surrogate keys are numeric and hence indexing on them is faster.
• Proper tracking the slowly changing dimension. e.g if an employee E1 belongs to business unit BU1 on 1st June 2009 but migrates to Business Unit BU2 on 1st November 2009. If natural key Employee_Id E1 is used, every other attribute will now be moved to BU2. In this case, if surrogate key is used, a new record can be created for E1 to show new data that belongs to BU2.

Q17 What are external tables?

External Tables are a way to query data in a flatfile as if the file were an Oracle table. Thus, it provides a convenient way to move data into/out of database. No DML operations(other than CREATE TABLE) are permitted on External tables. Hence, we can not create indexes on External Tables.

Internally External table uses ORACLE_LOADER access driver to move data from flat file into database. It uses DATA PUMP access driver to move data out of the db into a file. 

How does one eliminate duplicate rows in an Oracle Table?

Method 1:

DELETE from table_name A
where rowid > (select min(rowid) from table_name B where A.key_values = B.key_values);

Method 2:

create table table_name2 as select distinct * from table_name1;
drop table table_name1;
rename table table_name2 as table_name1;

In this method, all the indexes,constraints,triggers etc have to be re-created.

Method 3:

DELETE from table_name t1
where exists (select 'x' from table_name t2 where t1.key_value=t2.key_value
                      and t1.rowid > t2.rowid)

Method 4:

DELETE from table_name where rowid not in (select min(rowid) from my_table group by key_value )

PL/SQL : Lets program our way into Database

* PL/SQL is Oracle Corporation's procedural extension to SQL. Its a 4th generation language which provides the fancy and powerful features of software engineering like overloading,data-encapsulation,collection types, exceptions and information hiding.

--------------

* The basic unit of PL/SQL program is Block. There are 2 types of blocks : Anonymous and Named. Subprograms fall in the category of named blocks. The basic structure of a block is :

DECLARE

-- Declaration block (optional)

BEGIN

-- Programming logic

EXCEPTION

-- Exception-handling (optional)

END

----------

* Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has 2 types of subprograms:procedures and functions. Generally, a function is used to calculate a value while procedure is used to perform an action. Procedures act like new statements while functions act like new expressions or operators.

--------

* Package is a database object that bundles logically related data-types , variables , cursors , and subprograms together. It defines a simple clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages usually have 2 parts : a specification and a body. The specification defines the application programming interface ; it defines the types, constants, variables, exceptions, cursors and subprograms. The body fills in the SQL queries for cursors and the code for subprograms. Packages are stored in databases where they can be shared by many applications. When a packaged subprogram is called for the first time, the whole of package gets loaded and cached into the memory thereby saving lots of time on disk I/O for subsequent calls.

-----

* Difference between CHAR and VARCHAR2 Datatypes:

When you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Information about trailing blanks in the original value is lost. If the character value is longer than the declared length of the CHAR variable, PL/SQL aborts the assignment and raises the predefined exception VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks. However , when you assign a character value to a VARCHAR2 variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are assigned intact, so no information is lost. If the character value is longer than the declared length of the VARCHAR2 variable, PL/SQL aborts the assignment and raises VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks.

--

* PL/SQL Collections:

A collection is an ordered group of elements all of the same type. Its a general concept that covers lists , arrays and other datatypes used in programming algorithms. PL/SQL offers 3 types of collections:

1. Nested Tables

2. Associative Arrays(Index-by Tables)

3. Varrays

Variables whose type is either nested table or Varray must be initialized with a constructor before they can be used.

-

* Differences between Nested Table and Array:

a> Nested Tables do not have a declared number of elements, while arrays have a predefined number.

b> Nested tables may not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initailly, nested tables are dense, but they can become parse.

-

*Constructor: Constructors are used to initialize an object. It is a system-defined function whose name is same as that of its object. While an ordinary function returns some type, a constructor returns self as result. In PL/SQL , a constructor is used to initialize a nested table or a varray. Until a nested table or varray is initialized , it is automatically null. (The collection itself is null and not its elements)

-

*Transaction: A transaction is a series of SQL DML statements that does a logical unit of work. The statements either fail or suceed in a group. e.g two update statements might credit one bank account and debit another one. It is important not to allow one operation to succeed while the other one fails.

Factorial and Rownumbers (SQL Puzzle 3)

Lets do some mathematics!!

We need to calculate factorial of numbers (say from 1 to 10). One of the useful queries can be as follows:


SELECT
rownum,
EXP(SUM(LN(rownum)) OVER (ORDER BY ROWNUM)) Factorial
FROM all_objects
WHERE rownum < 10

(Note : This query was given to me by my friend and mentor Mr. Bucchibabu).

I hope it helps in some way or other!!

Who is more Popular?? (SQL Puzzle 2)

I have a table with 1 column called 'Name' . The values in the table are as follows:

select * from table

Name
--------
Gaurav Goel
Nitin Jain
Gaurav Sharma
Gaurav Kapoor
Sanjeev Sinha
Sanjeev kapoor
Nishi Kant
Nitin Agrawal

Now I have to retrieve data in order of popularity. The popularity of any name is defined as follows:

Extract the first names for all entries. The one with more popular first names have to be shown first. In case of ties, the name which comes first in the table has to be displayed first. In the above sample values, the required output is :

Gaurav Goel
Gaurav Sharma
Gaurav Kapoor
Nitin Jain
Sanjeev Sinha
Sanjeev kapoor
Nitin Agrawal
Nishi Kant

The query written is :


select name from
(
select rownum rn,name,
substr(name,1,instr(name,' ')) first_name,
count(substr(name,1,instr(name,' '))) over(partition by substr(name,1,instr(name,' '))) occurence
from names
)
order by occurence desc,rn

It seems to work!! Can anybody plz give a better solution??

Number 0f working days between any 2 given dates (SQL Puzzle 1)

Problem: Write a query to calculate number of working days between any 2 given dates.

The working days here refer to the count of days excluding Saturdays and Sundays. Let us take the given 2 dates as '26-Aug-2007' and SYSDATE. (26th August is my Birthday :-D ).

The query looks like:

select count(val) Number_of_working_days from
(
select
to_number(to_char(to_date('26-aug-07')+rownum , 'D')) val,
to_char(to_date('26-aug-07')+ rownum) date1,
to_char(to_date('26-aug-07') + rownum, 'Day') day1
from
all_objects
where
to_date('26-aug-07')+rownum<= sysdate
)
where val not in (6,7)

I genuinely feel that a better query can be written for this purpose. Please provide your valuable inputs!!!

The Magic of 'Start With Connect By' Clause

In Oracle , 'Start With..Connect By' Clause is a powerful way to select data that has hierarchical relationship. (like Parent -> Child or Manger->Employee). We will explore it with the help of an example. First, create a table named "Entities".

CREATE TABLE ENTITIES

(

PARENT_ENTITY VARCHAR2(20 BYTE),

CHILD_ENTITY VARCHAR2(20 BYTE)

);

Now insert some sample values in it.

Insert into ENTITIES (CHILD_ENTITY) Values ('a');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('a', 'af');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('a', 'ab');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('a', 'ax');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('ab', 'abc');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('ab', 'abd');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('ab', 'abe');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('abe', 'abes');
Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('abe', 'abet');
Insert into ENTITIES (CHILD_ENTITY) Values ('b');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('b', 'bg');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('b', 'bh');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('b', 'bi');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('bi', 'biq');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('bi', 'biv');

COMMIT;

This data heirarchy looks like :

(Click on the image to enlarge)

Now we will try to understand the result of the following query:

Main Query:

select level,parent_entity,child_entity
from entities
start with parent_entity is null
connect by prior child_entity=parent_entity

The execution is something like this:
1. First look at your base result i.e
select * from parent_entity. (Keep this in mind)

2. Look at the start by condition...start with parent_entity is null. The startwith condition is used to identify the root of the tree. The 'prior' operator is used to specify the direction in which the query traverses the tree(down from root or up from branches)
The 'data to be scanned' is : (select * from entities where parent_entity is null)
Parent_Entity..............Child_Entity
........NULL............................a

........NULL............................b
So it searches those child_entities where parent_entity is null i.e top of the tree...

The main query result till this stage is:
Level........Parent_Entity............Child Entity
....1....................... NULL.........................a

3. It will now search for allthe childs of 'a'.
The childs of a are : af , ab and ax (this is done as a result of connect by prior child_entity=parent_entity clause )
It will now search for the childs of af. It will show the result in main query. (There are no childs of af)
The main query result till this stage is:
Level........ Parent_Entity....... Child Entity
....1...................... NULL......................a
....2......................... a........................af
It will now search the childs of ab. It will show the result in main query. (There are 3 childs of ab : abc,abd,abe)
Level........ Parent_Entity........ Child Entity
....1........................ NULL.................... a
....2........................... a........................af
....2........................... a........................ab
Now taking 1 child at a time of ab ; it will dig into abc,abd and abe. There are no childs for abc and abd but abe has two childs : abes and abet.

So the result of main query now is:
Level............... Parent_Entity................ Child Entity
....1 .................................NULL............................... a
....2...................................... a...................................af
....2...................................... a...................................ab
....3..................................... ab..................................abc

....3..................................... ab..................................abd
....3..................................... ab..................................abe
....4 .....................................abe................................abes
....4..................................... abe................................abet
It will now serch for childs of abes. There are no childs. It will now search for the childs of abet. There are no childs. So now the it will trace back the loop where it had left i.e at "ax" (3rd child of a) .
4. It will show the row corresponding to ax and serach for its childs (There are no childs for ax).
Result of main query now is:
Level.......... Parent_Entity.......... Child Entity
....1......................... NULL.......................... a
....2............................. a...............................af
....2............................. a...............................ab
....3............................ ab..............................abc
....3............................ ab..............................abd
....3............................ ab..............................abe
....4.............................abe............................abes
....4............................ abe............................abet
....2............................ a................................ax

5. Since there are no childs of ax, it will now trace back to the result set of our "start with" condition and start looking for data corresponding to 'b' ...(read point no. 2).
It will follow the same steps for this level also.

6. The final main query result set will be :
Level........... Parent_Entity.............. Child Entity
....1............................ NULL............................. a
....2................................ a............................af
....2................................ a ...........................ab
....3................................ ab..........................abc
....3................................ ab..........................abd
....3................................ ab..........................abe
....4................................ abe........................abes
....4................................ abe........................abet
....2................................ a............................ax
....1.............................. NULL........................... b
....2................................ b............................bg
....2................................ b............................bh
....2................................ b............................bi
....3................................ bi...........................biq
....3................................ bi...........................biv

Thats it!!!!!
You can alter your tree traversal by changing your "start with" condition.
For example if I write

select level,parent_entity,child_entity
from entities
start with parent_entity = 'a'
connect by prior child_entity=parent_entity

It will cut down the 'b' branch and will show only 'a' hierarchy. :-)) This process is called 'pruning'.

I hope u find it helpful.