获取表中的前100条记录

debugcn 发表于 Dev

用户名

我有一个庞大的数据库，其中包含数以百万计的行，并且查询中包含多个表。我想测试我的查询，以便知道我的查询是否工作正常。

如果我运行查询，它将花费数小时才能给出查询的输出，并且在oracle中了解到Rownum之后，我尝试了该操作，但rownum仅在查询执行后才执行。

有什么快速的方法来测试我的查询，以便我可以显示前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
      t4.attr_name = 'SiteName' 
      and p.attr_name  = 'Product'
      and m.attr_name  = 'Model'
      and u.attr_name  = 'Usage'
      order by product,location;

尝试1：获取前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;

我确实尝试了上述方法，但在几分钟内就得到了一些结果，但是不确定这是否是正确的方法...您怎么看？

拉胡尔·特里帕蒂（Rahul Tripathi）

尝试此操作以获取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
      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;

还要注意，Oracle在返回结果后将rownum应用于结果。

但是，您可以尝试使用以下方法检查表中是否存在该值：

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;