Get first 100 records in a table

Question

I have a huge database with millions of rows with several tables included in my query. I want to test my query so that I know if my query is working fine.

If I run my query, its going to take hours to give the output of the query and after reading about Rownum in oracle I tried that but rownum executes only after the query has executed.

Is there any quick way to test my query so that I can display first 100 rows.

select 
p.attr_value product,
m.attr_value model,
u.attr_value usage,
l.attr_value location
    from table1 t1 join table2 t2 on t1.e_subid = t2.e_subid
                   join table4 t4 on t4.loc_id = t1.loc_id
                   join table3 p  on t2.e_cid = p.e_cid 
                   join table3 m  on t2.e_cid = m.e_cid 
                   join table3 u  on t2.e_cid = u.e_cid 
  Where
      t4.attr_name = 'SiteName' 
      and p.attr_name  = 'Product'
      and m.attr_name  = 'Model'
      and u.attr_name  = 'Usage'
      order by product,location;

attempt1: To get the result of the query for top 100

select 
p.attr_value product,
m.attr_value model,
u.attr_value usage,
l.attr_value location
    from table1 t1 join table2 t2 on t1.e_subid = t2.e_subid
                   join table4 t4 on t4.loc_id = t1.loc_id
                   join table3 p  on t2.e_cid = p.e_cid 
                   join table3 m  on t2.e_cid = m.e_cid 
                   join table3 u  on t2.e_cid = u.e_cid 
  Where
      ROWNUM <= 100 
      and t4.attr_name = 'SiteName' 
      and p.attr_name  = 'Product'
      and m.attr_name  = 'Model'
      and u.attr_name  = 'Usage'
      order by product,location;

I did try the above and I am getting some result in few minutes of time but not sure if that is the correct way to do...what do you think?

Answer 1

Try this to get the 100 records:

  select 
p.attr_value product,
m.attr_value model,
u.attr_value usage,
l.attr_value location
    from table1 t1 join table2 t2 on t1.e_subid = t2.e_subid
                   join table4 t4 on t4.loc_id = t1.loc_id
                   join table3 p  on t2.e_cid = p.e_cid 
                   join table3 m  on t2.e_cid = m.e_cid 
                   join table3 u  on t2.e_cid = u.e_cid 
  Where
      t4.attr_name = 'SiteName' 
      and p.attr_name  = 'Product'
      and m.attr_name  = 'Model'
      and u.attr_name  = 'Usage'
      and ROWNUM <= 100
      order by product,location;

Also note that Oracle applies rownum to the result after it has been returned.

However you may try to check if the value exists in the table using this:

select case 
            when exists (select 1
        from table1 t1 join table2 t2 on t1.e_subid = t2.e_subid
                       join table4 t4 on t4.loc_id = t1.loc_id
                       join table3 p  on t2.e_cid = p.e_cid 
                       join table3 m  on t2.e_cid = m.e_cid 
                       join table3 u  on t2.e_cid = u.e_cid 
      Where
          t4.attr_name = 'SiteName' 
          and p.attr_name  = 'Product'
          and m.attr_name  = 'Model'
          and u.attr_name  = 'Usage'
          order by product,location;
) 
    then 'Y' 
            else 'N' 
        end as rec_exists
from dual;