Oracle SQL :- Connect by Level concept in queries

Developers aware about clause Connect by Level in Oracle SQL. I have read several bogs, posts, reviews etc to find out the exact use of it. Mostly writes or blog owners are very firm in their approach to define but what exactly I have been experienced likes to share with you in brief.

Purpose to use clause:-

Connect by Level clause mainly used for following

1. Make tree level hierarchy.

2. Use Calendar inside the database.

3. There should be another scenarios to which this support.

Performance:-

Do not use with data tables directly as it is slow down the performance of query.

When to use

1. Create view with the help of clause in database, and compare it with using scalar query

2. Independent query with one table or in other words small data table.

Example:- 

Let assume this little example

Table 1                                                                        

MainId          SerialNo           Date From            Date To

1                       1                     01-Aug- 2017        05-Aug-2017

1                       2                     11-Aug-2017         20-Aug-2017

How many days against Main Id definitely 15 days. To get this 15 days in one row it is not possible to take max and min date.

Therefore we create following view

Create or replace view days_year

SELECT

  (to_date('01/01/2000','DD-MM-YYYY') - level + 1) AS day

FROM

  dual

CONNECT BY LEVEL <= (to_date(sysdate,'DD-MM-YYYY') - to_date('01/01/2000','DD-MM-YYYY') + 1);

I hope this above will help you out during said type of scenarios.

If you have any query feel free to comments.