it depends on how the code is written ... maybe it is doing row
operations ... care to show the code (at-least pseudo code)
??
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot
com
All Views expressed in this email
are strictly personal.
QOTD: Any clod
can have facts, having an opinion is an art !
This statement
is from a batch program within a pl/sql procedure. (Also, I have many similar
ones within the process) The policy table has approximately 6.2 million
rows. The procedure is to incrementally(daily) build an extract table
from multiple tables. The extract table is then used for reporting
purposes. The statement performs well per policy, however it is being executed
43,000+ times. Is there a design option available to me to reduce the
number of executions and be more scaleable? I am considering the
creation of an index to incorporate both the policy_number and the
pol_eff_date hopefully eliminating the table access.
We are currently on 8.1.7.
***************************************************************************************
SELECT MIN(P.POL_EFF_DATE)
FROM
PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1
call count cpu
elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse
1 0.00 0.01
0 0
0 0
Execute 43814 1.95
1.57 0
0 0
0
Fetch 43814
55.88 599.11 408248 568098
0 43814
------- ------ -------- ---------- ----------
---------- ---------- ----------
total
87629 57.83 600.69
408248 568098 0
43814
Misses in library cache
during parse: 1
Optimizer goal:
CHOOSE
Parsing user id: 547 (RPTADM)
(recursive depth: 1)
Rows
Execution Plan
-------
---------------------------------------------------
0 SELECT STATEMENT
GOAL: CHOOSE
0
SORT (AGGREGATE)
0
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'POLICY'
0
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE)
American Express made the following
annotations on 01/22/2004 10:24:24
AM
------------------------------------------------------------------------------
******************************************************************************
"This
message and any attachments are solely for the intended recipient and may
contain confidential or privileged information. If you are not the intended
recipient, any disclosure, copying, use, or distribution of the information
included in this message and any attachments is prohibited. If you have
received this communication in error, please notify us by reply e-mail and
immediately and permanently delete this message and any attachments. Thank
you."
******************************************************************************
==============================================================================
**************************************************************************************
This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
**************************************************************************************4
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Thu Jan 22 2004 - 11:54:25 CST
