Improve query that does order by on millions of rows.

From: James Williams <willjamu_at_mindspring.com>
Date: Thu, 01 Nov 2001 01:18:55 GMT
Message-ID: <3be0a2e2.55591012@nntp.mindspring.com>

Performance is great without the order by. When I add the order by the order by column is also indexed. Can this query be improved on the order by.  

1 SELECT CIS_BILL_ACCT.KY_PREM_NO as KY_PREM_NO, CIS_BILL_ACCT.KY_CUST_NO
as KY_CUST_NO FROM CIS_BILL_ACCT,CIS_SERVICE_PT

     2 WHERE CIS_BILL_ACCT.KY_PREM_NO = CIS_SERVICE_PT.KY_PREM_NO AND CIS_SERVICE_PT.CD_SPT_TYPE = 100
     3 order by CIS_BILL_ACCT.KY_CUST_NO ~

SQL> select count(*) from cis_service_pt;  

  COUNT(*)

---

   1836990  

SQL> select count(*) from CIS_BILL_ACCT;  

  COUNT(*)

---

   8523376  

optimizer_mode=first_rows  

ID Query Plan

---

---

         6               TABLE ACCESS CIS_BILL_ACCT
         6           TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT
         4           INDEX CIS_SERVICE_PT_IND04
         6               TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT
         6               TABLE ACCESS CIS_BILL_ACCT

 

Have index on KY_CUST_NO. Trying to improve order by performance.  

SQL> show parameter sort  

NAME                                 TYPE    VALUE


------------------------------------ -------

------------------------------

nls_sort                             string
sort_area_retained_size              integer 0
sort_area_size                       integer 100000000
sort_multiblock_read_count           integer 2

SQL>   Total System Global Area 1358577824 bytes

Fixed Size                    73888 bytes
Variable Size             166453248 bytes
Database Buffers         1187840000 bytes
Redo Buffers                4210688 bytes

SQL> Received on Wed Oct 31 2001 - 19:18:55 CST