RE: query hanged in parsing

As opposed to the use of the term “orphan” regarding “keys” that point to corrupt blocks. (In which case I was wondering why that might affect the CBO and perhaps not caring much about plans and optimization operating on corrupt tables, thinking that the next proper step would be to repair the corrupions.)  

The “clean up the index later” with notes for skip me I don’t exist, possibly could get peeked at by the CBO, which might explain mismatched plans for different children branched out by partition (though one would hope that if “gone partition” is peeked then that entire branch is skipped rather than re-planned. I think our friend, the retired Foote, wrote something about that.  

I think your notion of dynamic stats on ignored partitions is quite possible with such peeking, but I had thought there was an indication of child execution plans not matching.  

Not knowing the purpose of the global index, it still begs the question of whether a similar local index (possibly missing some columns, possibly adding some columns) would be worthwhile to add to service this particular query or set of queries. IF partition wise joins could be serviced by such local indexes it is a question of the actual texture of the data whether that might be the superior solution, IF ALSO the update processing can handle it.  

Unless, of course, the OP is talking about orphans due to corruption, in which case, just fix that first.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, May 16, 2024 7:31 AM
To: Oracle L
Subject: Re: query hanged in parsing    

In more recent versions of Oracle, when you drop a partition from a partitioned table with global indexes the index entries for that partition may not be deleted immediately and there is a procedure (which can be called manually, or could be left to happen in the auto cleanup job) to delete the entries later. Such entries are referred to a Orphan entries.  

At run time, when using the index, the runtime engine has a note of which partitions have been dropped and therefore has a note of which index entries (which include the parittions data object id in their rowid) should be skipped.  

This makes me wonder if the "65K partition" table has a "large" number of dropped partitions which have deliberately not had their orphan entries deleted and the parse time may be Oracle trying to work out what the statistics of index access would be if those index entries were to be ignored.  

Regards

Jonathan Lewis    

On Thu, 16 May 2024 at 12:02, Mark W. Farnham <mwf_at_rsiz.com> wrote:

What do you mean by “orphan entries in indexes?”  

mwf    

--
http://www.freelists.org/webpage/oracle-l