| Getting Data in one pass [message #65694] |
Tue, 07 December 2004 07:15  |
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  |
Art Metzer
Messages: 2480
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>
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
