Re: Why does using two indexes slow down my query?

I expected that it would have used existing indexes itself. Can you try giving the hint
/*+ RULE */? The reason is, it could have uses indexes independently and did a AND-EQUAL processing by getting a intersection of rowids matching each of the conditions? Why waste space on additional index? Any comments?

raghuvir

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in article <01bd93e6$ea351370$0300a8c0_at_WORKSTATION>...
>
> The reason for the slowdown is probably:
> a) Counting on condition (1) uses access to the index only.
> total physical reads = a few leaf blocks for 9,000 rows
> b) Counting on condition (2) uses access to the index only
> total physical reads = a few dozen leaf blocks for 80,000 rows
> c) Counting on both conditions requires access to table rows
> 9,000 rows to read, on a poor sample space is 1 row in ca. 550
> which might be about 1 block in 8 say. If your
> db_file_multiblock_read_count
> is set to 32 Oracle will choose between:
> using 1 read for every 8th block
> or using 1 read to scan 32 blocks (and getting 4 relevant blocks)
> A tablescan is 'clearly' the better option.
>
> The two pieces of advice you have been given in other posts are both
> valid.
> The two-column index is more likely to work for this specific query,
> but
> getting better stats is generically a better approach.
>
>
>
Received on Tue Jun 09 1998 - 23:23:47 CDT