Below is a sumary of some testing I did on the new LEAD operator that I sent to our
Apps developers.
Any feedback from "you're brain dead" to "wow" would be appreciated.
Thanks, Ron
[EMAIL PROTECTED]
--------------------------------------------------------------------
Class is in session.
This is really for Donn cause I'm sure he recognizes the following snippet. We've had
to do this
countless times because the MTL_ITEM_REVISION table is not truely date tracked like
HR. A revision
is effective from the effective date of the current record until the effective date of
the next
record.
So, for example, to find when 010004-011, revision H's end date was we did the
following:
SELECT MIN(effectivity_date) - 1/86400
FROM INV.MTL_Item_Revisions
WHERE organization_id = 201 AND -- 201 is master org
inventory_item_id = 6753 AND -- 6753 is the inventory item id for 010004-011
effectivity_date > (
SELECT effectivity_date
FROM INV.MTL_Item_Revisions
WHERE organization_id = 201 AND
inventory_item_id = 6753 AND
revision = 'H' ) ;
What a mess. What about Oracle 8.1.7 method of the new LEAD operator?
For an example,
SELECT
revision revision,
effectivity_date start_effective,
LEAD(effectivity_date-1/86400, 1, TO_DATE('12/31/4712','mm/dd/yyyy') )
OVER ( ORDER BY effectivity_date ) end_effective
FROM
INV.MTL_Item_Revisions
WHERE
inventory_item_id = 6753 AND -- '010004-011'
organization_id = 201
Statistics
----------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
1 sorts (memory)
Gives:
REV START_EFFECTIVE END_EFFECTIVE
--- ----------------- -----------------
00 97/01/27 09:32:23 97/02/27 15:44:11
01 97/02/27 15:44:12 97/03/03 14:30:56
A 97/03/03 14:30:57 97/04/14 14:45:59
B 97/04/14 14:46:00 97/05/14 11:02:59
B1 97/05/14 11:03:00 97/10/22 08:42:59
C 97/10/22 08:43:00 98/04/28 16:44:59
D 98/04/28 16:45:00 98/07/29 17:49:59
E 98/07/29 17:50:00 98/10/12 09:54:59
F 98/10/12 09:55:00 98/12/07 18:00:59
G 98/12/07 18:01:00 99/02/02 17:23:59
H 99/02/02 17:24:00 99/02/03 08:56:59
J 99/02/03 08:57:00 99/05/19 17:59:59
K 99/05/19 18:00:00 99/07/07 09:41:59
K1 99/07/07 09:42:00 99/12/15 17:59:59
L 99/12/15 18:00:00 01/04/17 10:31:59
M 01/04/17 10:32:00 01/07/20 16:29:59
N 01/07/20 16:30:00 12/12/31 00:00:00
17 rows selected.
Cool. So you might be tempted to get the end_effectivity of rev H with the following:
SELECT
revision revision,
effectivity_date start_effective,
LEAD(effectivity_date-1/86400, 1, TO_DATE('12/31/4712','mm/dd/yyyy') )
OVER ( ORDER BY organization_id,inventory_item_id,effectivity_date ) end_effective
FROM
INV.MTL_Item_Revisions
WHERE
inventory_item_id = 6753 AND -- '010004-011'
organization_id = 201 AND
revision = 'H'
Statistics
----------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
1 sorts (memory)
REV START_EFFECTIVE END_EFFECTIVE
--- ----------------- -----------------
H 99/02/02 17:24:00 12/12/31 00:00:00
Oops, what happened. Since you constrained the query to just return the record for
the H revision,
the was no following record and the value returned for the end date was the default (
parameter 3 of
the LEAD function ).
In order to do this with the LEAD function, you need to code this as an inline view as
follows:
SELECT
T.revision,
T.start_effective,
T.end_effective
FROM
(
SELECT
revision revision,
effectivity_date start_effective,
LEAD(effectivity_date-1/86400, 1, TO_DATE('12/31/4712','mm/dd/yyyy') )
OVER ( ORDER BY organization_id,inventory_item_id,effectivity_date )
end_effective
FROM
INV.MTL_Item_Revisions
WHERE
inventory_item_id = 6753 AND -- '010004-011'
organization_id = 201 AND
revision >= 'H'
) T
WHERE
revision = 'H'
Statistics
----------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
1 sorts (memory)
Starting to look kind a ugly again. Compare this with the following to return the
same data:
SELECT
MIR.revision revision,
MIR.effectivity_date start_effective,
NVL(MIN(MIR2.effectivity_date) - 1/86400,TO_DATE('12/31/4712','mm/dd/yyyy') )
end_effective
FROM
INV.MTL_Item_Revisions MIR,
INV.MTL_Item_Revisions MIR2
WHERE
MIR.organization_id = 201 AND
MIR.inventory_item_id = 6753 AND
MIR.revision = 'H' AND
MIR2.organization_id (+) = MIR.organization_id AND
MIR2.inventory_item_id (+) = MIR.inventory_item_id AND
MIR2.effectivity_date (+) > MIR.effectivity_date
GROUP BY
MIR.revision,
MIR.effectivity_date
Statistics
-----------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
1 sorts (memory)
This is works but will cause great pain when joined to other tables, such as
INV.MTL_System_Items
because of the group by. Also notice the outer join to handle the last revision on
the item.
To make this buzard joinable, it needs to rewritten as follows:
SELECT
MIR.revision revision,
MIR.effectivity_date start_effective,
DECODE(MIR2.effectivity_date,
MIR.effectivity_date,TO_DATE('12/31/4712','mm/dd/yyyy'),
MIR2.effectivity_date - 1/86400 ) end_effective
FROM
INV.MTL_Item_Revisions MIR,
INV.MTL_Item_Revisions MIR2
WHERE
MIR.organization_id = 201 AND
MIR.inventory_item_id = 6753 AND
MIR.revision = 'H' AND
MIR2.organization_id = MIR.organization_id AND
MIR2.inventory_item_id= MIR.inventory_item_id AND
MIR2.effectivity_date = (
SELECT NVL(MIN(MIR3.effectivity_date),MIR.effectivity_date)
FROM INV.MTL_Item_Revisions MIR3
WHERE MIR3.organization_id = MIR.organization_id AND
MIR3.inventory_item_id = MIR.inventory_item_id AND
MIR3.effectivity_date > MIR.effectivity_date )
Statistics
----------------------------
0 recursive calls
0 db block gets
11 consistent gets
3 physical reads
0 sorts (memory)
So, where does this leave us. Well, for a final test, lets create a view based on the
LEAD operator
and test.
CREATE OR REPLACE VIEW MIR
AS
SELECT
organization_id,
inventory_item_id,
revision revision,
effectivity_date start_effective,
LEAD(effectivity_date-1/86400, 1, TO_DATE('12/31/4712','mm/dd/yyyy') )
OVER ( ORDER BY organization_id,inventory_item_id,effectivity_date ) end_effective
FROM
INV.MTL_Item_Revisions
/
SELECT
revision,
start_effective,
end_effective
FROM
MIR
WHERE
organization_id = 201 AND
inventory_item_id = 6753
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=535564 Bytes=25171508)
1 0 VIEW OF 'MIR' (Cost=826 Card=535564 Bytes=25171508)
2 1 WINDOW (BUFFER)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_REVISIONS' (Cost=826
Card=535564
Bytes=8569024)
4 3 INDEX (FULL SCAN) OF 'MTL_ITEM_REVISIONS_N1' (NON-UNIQUE) (Cost=26
Card=535564)
Statistics
----------------------------------------------------------
1961 recursive calls
375 db block gets
335558 consistent gets
71235 physical reads
53112 redo size
0 sorts (memory)
1 sorts (disk)
Can you say: F'ing ugly! And the statistics are just as bad when you additionally
specify a
revision in the where clause.
It this point in the research, I'd recommend staying away from the LEAD operator
unless you have a
specific application for it and you test the hell out of it.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Thomas
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
