Depending on the size and how much data was deleted or updated could
produce the behavior you describe... Lets say your table has 10,000
blocks of data and 100,000 rows. And you delete 90% of the data leaving
10,000 rows in the table... These 10,000 rows could be distributed among
the 10,000 blocks. 1 per row... So to access the table using a full table
scan would still require access to all the 10,000 blocks of the table...
essentially no change in performance.

Updates can cause similar problems with row chaining. If an update
extends past it's original block, additional blocks are chained to the
first.. requiring additional block reads to get at all the data... Both
these operations also are compounded when the indexes become "fragmented"
.

Rebuilding the indexes in place can help solve some of the performance
hit... Rebuilding the table or schema can correct more...

Also be sure to update statistics following your "delete" or "update"
operations if they are significant an out of data stats would cause oracle
to possibly choose inefficient access paths. In the above, stats might
show that the table still has 100,000 rows and pick an access path
accordingly.



John Best - Project Manager (PMP)
Caterpillar Global IT
Distributed Factory & Procurement Systems Development




"ed " <oracledba-ezmlmshield-x69414514.[Email address protected]
06/02/2005 07:08 AM


To
To
"LazyDBA Discussion" <[Email address protected]
cc




Subject
slow perfomance after deleting




Caterpillar: Confidential Green Retain Until: 07/02/2005 Retention
Category: G90 - General Matters/Administration


why, after deleting of rows on two tables and updating another tables,
next process which only select data from same tables is so slow;
how to improve that perfomance, like that there is not been deleting and
updating; what to check
thanks in advance


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html



Oracle LazyDBA home page