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: 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>

----------------------------------------------------------------------
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 May 01 11:04:02 CDT 2025