begin
delete with first predicate
if less than 1000 rows deleted then
delete with second predicate
end if
end
The goal is too avoid doing a second access to the
table.
- "Carle, William T (Bill), NBSO" <wcarle_at_att.com> a
écrit : > I want to do the first option.
>
> Bill
>
> -----Original Message-----
> From: Thomas L. Harleman
> [mailto:tharleman_at_iquest.net]
> Sent: Wednesday, August 09, 2000 2:55 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: tkprof question
>
> What do you really want to do?
>
> DELETE rpt_metric
> WHERE ( ntts < 1566858236 ) OR ( ntts =
> 1566858236 AND
> ntseq <= 749223 )
> AND ROWNUM < 1001
>
> or
>
> DELETE rpt_metric
> WHERE ( ntts < 1566858236 OR ntts = 1566858236 )
> AND ntseq <= 749223
> AND ROWNUM < 1001
>
> Tom Harleman
> 11080 Willowmere Dr.
> Indianapolis, IN 46280
> 317-844-2884 Home
> 317-843-9122 Home Office
>
>
> -----Original Message-----
> William T (Bill), NBSO
> Sent: Wednesday, August 09, 2000 9:54 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
>
> What am I doing wrong here? I have an index on
> ntts and
> ntseq. This is a
> 10 million row table.
>
> Bill Carle
>
>
> TKPROF: Release 8.0.6.0.0 - Production on Wed Aug
> 9 08:37:29
> 2000
> © Copyright 1999 Oracle Corporation. All rights
> reserved.
> Trace file: ora_5058.trc
> Sort options: default
>
>
>
> ****
> count = number of times OCI procedure was
> executed
> cpu = cpu time in seconds executing
> elapsed = elapsed time in seconds executing
> disk = number of physical reads of buffers
> from disk
> query = number of buffers gotten for consistent
> read
> current = number of buffers gotten in current
> mode (usually
> for update)
> rows = number of rows processed by the fetch
> or execute
> call
>
>
> ****
>
> DELETE rpt_metric
> WHERE
> (ntts < 1566858236 OR ntts = 1566858236 AND ntseq
> <= 749223)
> AND ROWNUM <
> 1001
>
>
> call count cpu elapsed disk
> query
> current
> rows
> ------- ------ -------- ---------- ----------
> ----------
> ----------
> ----------
> Parse 2 0.00 0.00 0
> 0
> 0
> 0
> Execute 2 81.16 81.82 191119
> 3738052
> 14383
> 0
> Fetch 0 0.00 0.00 0
> 0
> 0
> 0
> ------- ------ -------- ---------- ----------
> ----------
> ----------
> ----------
> total 4 81.16 81.82 191119
> 3738052
> 14383
> 0
> Misses in library cache during parse: 0
> Optimizer goal: RULE
> Parsing user id: 19 (NECOM)
>
> Rows Row Source Operation
> -------
> ---------------------------------------------------
> 0 DELETE RPT_METRIC
> 1000 COUNT
> 0 CONCATENATION
> 419223 FILTER
> 419223 TABLE ACCESS BY INDEX ROWID
> RPT_METRIC
> 419224 INDEX RANGE SCAN (object id 1973)
> 1436876 FILTER
> 1436876 TABLE ACCESS BY INDEX ROWID
> RPT_METRIC
> 1436877 INDEX RANGE SCAN (object id 1973)
>
>
> Rows Execution Plan
> -------
> ---------------------------------------------------
> 0 DELETE STATEMENT GOAL: RULE
> 0 DELETE OF 'RPT_METRIC'
> 1000 COUNT
> 0 CONCATENATION
> 419223 FILTER
> 419223 TABLE ACCESS GOAL: ANALYZED (BY
> INDEX ROWID)
> OF
> 'RPT_METRIC'
> 419224 INDEX (RANGE SCAN) OF
> 'IX_RPT_METRIC'
> (UNIQUE)
> 1436876 FILTER
> 1436876 TABLE ACCESS GOAL: ANALYZED (BY
> INDEX ROWID)
> OF
> 'RPT_METRIC'
> 1436877 INDEX (RANGE SCAN) OF
> 'IX_RPT_METRIC'
> (UNIQUE)
>
>
>
>
>
>
> ****
>
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> call count cpu elapsed disk
> query
> current
> rows
> ------- ------ -------- ---------- ----------
> ----------
> ----------
> ----------
> Parse 2 0.00 0.00 0
> 0
> 0
> 0
> Execute 2 81.16 81.82 191119
> 3738052
> 14383
> 0
> Fetch 0 0.00 0.00 0
> 0
> 0
> 0
> ------- ------ -------- ---------- ----------
> ----------
> ----------
> ----------
> total 4 81.16 81.82 191119
> 3738052
> 14383
> 0
> Misses in library cache during parse: 0
>
> OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> call count cpu elapsed disk
> query
> current
> rows
>
=== message truncated ===
Stephane Paquette
Received on Thu Aug 10 2000 - 05:34:37 CDT
