Re: index skip scan
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 6 Jan 2009 09:54:21 -0800 (PST)
Message-ID: <52e8113e-a42a-475f-b0ff-914121f9c156_at_r15g2000prh.googlegroups.com>
On Jan 6, 12:47 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> Note that there are fewer leaf blocks in the INV_TRANS_QTY_IND3 index,
> which is the two column index, which probably means that the
> INV_TRANS_QTY_IND2 index experienced a greater percentage of 50/50
> block splits than did the INV_TRANS_QTY_IND3 index.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Date: Tue, 6 Jan 2009 09:54:21 -0800 (PST)
Message-ID: <52e8113e-a42a-475f-b0ff-914121f9c156_at_r15g2000prh.googlegroups.com>
On Jan 6, 12:47 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> Note that there are fewer leaf blocks in the INV_TRANS_QTY_IND3 index,
> which is the two column index, which probably means that the
> INV_TRANS_QTY_IND2 index experienced a greater percentage of 50/50
> block splits than did the INV_TRANS_QTY_IND3 index.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
In addition to 50/50 block splits possibly explaining the difference between the number of leaf blocks between the two indexes, there is also the possibility that the indexes are in tablespaces with different block sizes, or that some/many rows were deleted from the tables, leaving partially filled leaf blocks. I probably missed another possible explanation.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Jan 06 2009 - 11:54:21 CST