Hello Guys,
Welcome to my blogs first quiz (or rather I will say Technique).
Row Generator is a very important SQL technique when writing complex queries.
It helps us to generate Rows from nowhere.
For example you need to create a dummy table for your sql practice.
What most of the people will do is to write a create table query and then run multiple insert statements
to populate some data. But Guys belive me there is a smarter way to do it.
Suppose you need a test table students to quickly tryout your hands on some query.
here is how you can do it ....
create table students is
select level STUDENT_ID ,
dbms_random.string('A',5) STUDENT_NAME
,Trunc(dbms_random.value(20, 100)) GRADE
FROM dual connect by level < 100
This query create a table student with 100 Rows in it ..
There are various ways to generate rows from Nowhere.....
1. Using some Data Dictionary views
There will mostly be enough rows in your database dictionary views which you can utilize to generate rows.
for ex
Select rownum from all_tables where rownum < NUMOFROWS;
Drawback of this technique is that if you want to generate large no of rows (like 100+) you cannot
guarantee that there will be enough rows in the (all_table) data dictionary. Also the performance
of generating rows this way is not good
2. Using Oracle CUBE function.
A Cube function generates 2power(N) no of rows with N parameters
Ex
SELECT ROWNUM FROM (
SELECT 1 FROM dual GROUP BY cube(1,2,3,4)
)
This query will generate 16 Rows.. So for generating say 100 Rows we can write query
SELECT rownum FROM (
SELECT 1 FROM dual GROUP BY cube(1,2,3,4,5,6,7)
) where rownum <= 100
But the CUBE method has worst performance .
3. Using Connect BY
Connect By (Hierarchical) Query we can emulate the rows.
Ex
SELECT level FROM dual CONNECT BY LEVEL <= 100
Connect By is the best way to generate Rows.
Thanks,
Idris
