From: Pratap on
On May 28, 10:28 pm, "Jonathan Lewis" <jonat...(a)>
> "Pratap" <pratap.deshm...(a)> wrote in message
> news:1d9841d6-9a79-4b0f-b49e-cf2e83e02ade(a)
> > Hi,
> > Oracle
> > What is the relation between partitioned bitmap indexes and number of
> > recursive calls shown by Autotrace? I have seen that as the number of
> > partitions on a table increase, for a query using bitmap indexes, the
> > number of recursive calls also increase. When the number of partitions
> > on the table are reduced, the same query shows lower recursive calls.
> > I have discounted query parsing by running the queries 3 times before
> > observing the recursive calls.
> Have you tried enabling SQL trace and seeing if those recursive
> calls appear as SQL in the trace file ?
> --
> Regards
> Jonathan Lewis

On top of what I reported about $OR$IDX$PART$NUM, when the query is
fired for the first time, I see lot of recursive calls to indsubpart$

select obj#, dataobj#, subpart#, hiboundlen, hiboundval, flags, ts#,
block#, pctfree$, initrans, maxtrans, analyzetime, samplesize,
blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare2,
length(bhiboundval), bhiboundval
indsubpart$ where pobj# = :1 order by subpart#

This query executes 1008 times and takes 76.47


NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
seg$ where ts#=:1 and file#=:2 and block#=:3

This query executes 93010 times and takes 19.49 seconds overall.

The actual non-recursive query takes just 6 seconds!