| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> New 8.1.7 LEAD operator
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
rthomas_at_hypercom.com
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
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
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
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
--- ----------------- -----------------
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
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
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
MIR.organization_id = 201 AND MIR.inventory_item_id = 6753 AND MIR.revision = 'H' AND MIR2.organization_id (+) = MIR.organization_id ANDMIR2.inventory_item_id (+) = MIR.inventory_item_id AND MIR2.effectivity_date (+) > MIR.effectivity_date GROUP BY
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,
MIR2.effectivity_date - 1/86400 ) end_effective
FROM
MIR.organization_id = 201 AND MIR.inventory_item_id = 6753 AND MIR.revision = 'H' AND MIR2.organization_id = MIR.organization_id ANDMIR2.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
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=826Card=535564
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: rthomas_at_hypercom.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Tue Dec 04 2001 - 18:03:12 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).
 |
 |