Home » RDBMS Server » Performance Tuning » Getting Data in one pass
Getting Data in one pass [message #65694] Tue, 07 December 2004 07:15 Go to next message
Ben V
Messages: 2
Registered: December 2004
Junior Member
My inventory table has approximately 30 million records,

 

It has these columns.

ITEM_NUMBER

INVENTORY_DATE

QTY_AVAILABLE

 

There are no indexes on this table. 

I  would like to fetch following info from this table for a given item number,

<OL style="MARGIN-TOP: 0in" type=1>
<LI class=MsoNormal style="MARGIN: 0in 0in 0pt; mso-list: l0 level1 lfo1; tab-stops: list .5in">Max(QTY_AVAILABLE)
<LI class=MsoNormal style="MARGIN: 0in 0in 0pt; mso-list: l0 level1 lfo1; tab-stops: list .5in">Min(QTY_AVAILABLE)
<LI class=MsoNormal style="MARGIN: 0in 0in 0pt; mso-list: l0 level1 lfo1; tab-stops: list .5in">AVG(QTY_AVAILABLE)
<LI class=MsoNormal style="MARGIN: 0in 0in 0pt; mso-list: l0 level1 lfo1; tab-stops: list .5in">MAX(INVENTORY_DATE),
<LI class=MsoNormal style="MARGIN: 0in 0in 0pt; mso-list: l0 level1 lfo1; tab-stops: list .5in">QTY_AVAILABLE when the INVENTORY_DATE = MAX(INVENTORY_DATE)</OL>

I could write two queries, one would fetch first 4 values, the second would fetch the 5 the value.   However this approach would require two full table scans.

Can anybody suggest me a sql (or PL/SQL script) that wouldl select all 5 items with in one pass thru the data in the table.

I am using Oracle 9i. Version 9.2.05.0

 

Thanks in advance
Re: Getting Data in one pass [message #65695 is a reply to message #65694] Tue, 07 December 2004 07:47 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Here's where I stole the following idea from:
----------------------------------------------------------------------
SQL> CREATE TABLE inventory_table (
  2      item_number         NUMBER
  3  ,   inventory_date      DATE
  4  ,   qty_available       NUMBER
  5  )
  6  /
 
Table created.
 
SQL> INSERT INTO inventory_table
  2  SELECT FLOOR(DBMS_RANDOM.VALUE(1,6))
  3  ,      DBMS_RANDOM.VALUE(1,365)
  4         +
  5         TO_DATE('20031231','YYYYMMDD')
  6  ,      FLOOR(DBMS_RANDOM.VALUE(1,100))
  7  FROM   sys.all_users
  8  WHERE  ROWNUM <= 30
  9  /
 
30 rows created.
 
SQL> SELECT   invt.item_number
  2  ,        TO_CHAR(invt.inventory_date
  3           ,       'fmMM/DD/YYYY HH:fmMI:SS AM') inventory_dt
  4  ,        invt.qty_available
  5  FROM     inventory_table                       invt
  6  ORDER BY invt.item_number
  7  ,        invt.inventory_date
  8  /
 
ITEM_NUMBER INVENTORY_DT           QTY_AVAILABLE
----------- ---------------------- -------------
          1 3/21/2004 10:10:46 AM             40
          1 5/9/2004 10:19:50 AM              87
          1 6/21/2004 1:06:52 AM              65
          1 8/30/2004 6:10:14 AM              74
          1 12/17/2004 5:18:09 PM             95
          2 4/12/2004 7:11:06 AM              77
          2 7/10/2004 7:07:14 AM              48
          2 8/13/2004 12:44:34 AM             38
          2 11/26/2004 9:41:40 AM             79
          3 2/18/2004 7:50:18 PM              38
          3 8/9/2004 11:46:47 PM              63
          3 10/1/2004 4:58:14 PM              58
          3 10/30/2004 10:45:40 AM            77
          4 1/4/2004 4:19:21 AM               89
          4 1/15/2004 7:19:24 AM              12
          4 2/10/2004 9:29:49 PM              13
          4 5/9/2004 3:55:18 PM               88
          4 5/14/2004 7:51:47 PM              72
          4 5/17/2004 9:44:45 AM              11
          4 5/22/2004 12:05:31 PM             27
          4 8/6/2004 4:52:48 PM               68
          4 10/5/2004 2:10:25 PM               1
          4 11/5/2004 4:17:14 AM              12
          4 11/19/2004 9:29:28 PM             92
          4 12/16/2004 8:29:21 AM             89
          5 3/18/2004 8:42:33 PM              45
          5 5/12/2004 6:52:46 AM              74
          5 5/30/2004 6:07:00 AM              86
          5 8/10/2004 3:35:24 AM               2
          5 8/16/2004 1:32:11 AM              17
 
30 rows selected.
 
SQL>
SELECT   grpd.item_number
  2  
,        grpd.max_qty_avail
  3  
,        grpd.min_qty_avail
  4  
,        grpd.avg_qty_avail
  5  
,        TO_CHAR(TO_DATE(SUBSTR(grpd.max_date_plus_qty,1,14)
  6  
                 ,       'YYYYMMDDHH24MISS')
  7  
         ,       'fmMM/DD/YYYY HH:fmMI:SS AM')      max_inventory_date
  8  
,        TO_NUMBER(SUBSTR(grpd.max_date_plus_qty
  9  
                   ,      15))                      qty_during_max_inv_date
 10  
FROM    (SELECT   invt.item_number
 11  
         ,        MAX(invt.qty_available)           max_qty_avail
 12  
         ,        MIN(invt.qty_available)           min_qty_avail
 13  
         ,        AVG(invt.qty_available)           avg_qty_avail
 14  
         ,        MAX(TO_CHAR(invt.inventory_date
 15  
                      ,       'YYYYMMDDHH24MISS')
 16  
                      || TO_CHAR(invt.qty_available
 17  
                         ,       'fm000000000'))    max_date_plus_qty
 18  
         FROM     inventory_table                   invt
 19  
         GROUP BY invt.item_number) grpd
 20  
ORDER BY grpd.item_number
 21  
/
 
ITEM_NUMBER MAX_QTY_AVAIL MIN_QTY_AVAIL AVG_QTY_AVAIL MAX_INVENTORY_DATE     QTY_DURING_MAX_INV_DATE
----------- ------------- ------------- ------------- ---------------------- -----------------------
          1            95            40          72.2 12/17/2004 5:18:09 PM                       95
          2            79            38          60.5 11/26/2004 9:41:40 AM                       79
          3            77            38            59 10/30/2004 10:45:40 AM                      77
          4            92             1    47.8333333 12/16/2004 8:29:21 AM                       89
          5            86             2          44.8 8/16/2004 1:32:11 AM                        17
 
SQL>

----------------------------------------------------------------------
Re: Getting Data in one pass [message #65696 is a reply to message #65695] Tue, 07 December 2004 09:36 Go to previous message
Ben V
Messages: 2
Registered: December 2004
Junior Member
Great Idea. Thanks
Previous Topic: Again Execution Plan Of Query For Second Name Oracle is not using INdex
Next Topic: Again Execution Plan after Analyzing the table
Goto Forum:
  


Current Time: Thu Oct 01 10:28:45 CDT 2020