Prev: Understanding this query
Next: Our customer websites are modern, fresh and accessible.
From: Pratap on 28 May 2010 07:13
Hi,

Oracle 10.2.0.4

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.

Regards,
Pratap
From: Jonathan Lewis on 28 May 2010 17:28

"Pratap" <pratap.deshmukh(a)gmail.com> wrote in message
news:1d9841d6-9a79-4b0f-b49e-cf2e83e02ade(a)c22g2000vbb.googlegroups.com...
> Hi,
>
> Oracle 10.2.0.4
>
> 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
http://jonathanlewis.wordpress.com


From: joel garry on 28 May 2010 17:53
On May 28, 4:13 am, Pratap <pratap.deshm...(a)gmail.com> wrote:
> Hi,
>
> Oracle 10.2.0.4
>
> 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.
>
> Regards,
> Pratap

I don't know much about it, but after looking at
http://www.jlcomp.demon.co.uk/06_bitmap_2.doc I guess it is
recursively loading temporary tables. You don't have to guess, see
how to tell in the doc.

jg
--
@home.com is bogus.
http://arstechnica.com/science/news/2010/05/when-science-clashes-with-belief-make-science-impotent.ars
From: Pratap on 1 Jun 2010 07:55
Hi,

I had a look at the trace file and there are calls to TBL$OR$IDX$PART
$NUM with different parameters. It seems the recursive calls are due
to sub-query pruning.

Regards,
Pratap
From: Pratap on 1 Jun 2010 07:56
I have enabled alter session set star_transformation_enabled =
'temp_disable'; So it is unlikely that the recursive calls are due to
loading of temporary tables.
 |  Next  |  Last
Pages: 1 2
Prev: Understanding this query
Next: Our customer websites are modern, fresh and accessible.