Re: Trigger presence KILLS performance

Offline -

Just added a check on the behaviour
with two indexes. The behaviour is exactly the same. It's just that the variation in numbers appears smaller for the second index because the number of index entries per block for any choice of second index will be much smaller, so the multi-row delete optimisation has much less visibility.

Without the trigger you get:

        delete all relevant table rows
        delete all relevant index 1 entries
        delete all relevant index 2 entries

With the trigger you get:

    delete one table row
    delete one index 1 entry
    delete one index 2 entry

        etc.

Is it possible that this is not visible in 8.1.6 because it doesn't have the same optimisation on the indexes, rather than it being a bug in 8.1.7 ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Thomas Kyte wrote in message ...


>

>(if we index some other column, NOT owner, you will not observe this

behavior,


>both deletes will have the same number of consistent gets, you must be

doing an


>index range scan on the delete itself).

>

>I believe we've identified the issue and it doesn't happen in 816 and

before and


>*shouldn't* happen in 8173 and above -- i have to patch my testbed to see

that


>it's fixed in 8173 as I think..... Unless someone out there with 8173 wants

to


>test for us?

>