Home » RDBMS Server » Performance Tuning » Getting Data in one pass
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>
----------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Thu May 01 11:04:02 CDT 2025
|