SQL tuning, long running query + rownum

I have million record in database table having account no, address and many more columns. I want 100 rows in sorting with desc order, I used rownum for this, but the query is taking a long time to execute, since it scans the full table first make it in sorted order then apply the rownum.

What is the solution to minimize the query execution time?

For example:

select * 
from 
    (select 
         acc_no, address 
     from 
         customer 
     order by 
         acc_no desc) 
where 
    ROWNUM <= 100;   

---

From past experience I found that the TOP works best for this scenario.

Also you should always select the columns you need only and avoid using the all card (*)

SELECT TOP 100 [acc_no], [address] FROM [customer] ORDER BY [acc_no] DESC

Useful resources about TOP, LIMIT and even ROWNUM.

https://www.w3schools.com/sql/sql_top.asp

---

Make sure you use index on acc_no column.

If you have an index already present on acc_no, check if that's being used during query execution or not by verifying the query execution plan.

To create a new index if not present, use below query :

Create index idx1 on customer(acc_no); -- If acc_no is not unique
Create unique index idx1 on customer(acc_no); -- If acc_no is unique. Note: Unique index is faster.

If in explain plan output you see "Full table scan", then it is a case that optimizer is not using the index.

Try with a hint first :

select /*+ index(idx1) */ * from 
(select 
     acc_no, address 
 from 
     customer 
 order by 
     acc_no desc) 
where 
ROWNUM <= 100;   

If the query with hint above returned results quickly, then you need to check why optimizer is ignoring your index deliberately. One probable reason for this is outdated statistics. Refresh the statistics.

Hope this helps.

---

Consider getting your top account numbers in an inner query / in-line view such that you only perform the joins on those 100 customer records. Otherwise, you could be performing all the joins on the million+ rows, then sorting the million+ results to get the top 100. Something like this may work.

    select .....
    from customer
    where customer.acc_no in (select acc_no from
                        (select inner_cust.acc_no
                         from   customer inner_cust
                         order by inner_cust.acc_no desc
                        )
                    where rownum <= 100)
       and ...

Or, if you are using 12C you can use FETCH FIRST 100 ROWS ONLY

    select .....
    from customer
    where customer.acc_no in (select inner_cust.acc_no
                              from   customer inner_cust
                              order by inner_cust.acc_no desc
                              fetch first 100 rows only
                            )
       and ...

链接地址: http://www.djcxy.com/p/60724.html

上一篇: http头文件发送到html5 <音频>

下一篇: SQL调优，长时间运行查询+ rownum