RE: Bitmap index not used when joining tables
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Mon, 2 Jun 2014 17:45:42 -0700
Message-ID: <BLU179-W684AF158E3CA4C754A07BCEB230_at_phx.gbl>
If you want the index to be used consistently, why not use a hint? No optimizer is perfect and directives such as Oracle’s hints provide the simplest workaround in situations in which the optimizer has chosen a suboptimal plan. Hints are useful tools not just to remedy an occasional suboptimal plan, but also for users who want to experiment with access paths, or simply have full control over the execution of a query. Iggy > I've been testing using bitmap indexes on 11.2.0.2 EE. When joining two > tables on a column with a BTree index, the index is used in the > execution plan. If this index is changed to a bitmap index, Oracle no > longer uses the index when executing the query! It assigns a higher > cost to using the bitmap index when joining, despite this being a > cheaper approach (in terms of consistent gets). > > Why does this happen? Is this a bug or expected behaviour? -- http://www.freelists.org/webpage/oracle-l Received on Tue Jun 03 2014 - 02:45:42 CEST
Date: Mon, 2 Jun 2014 17:45:42 -0700
Message-ID: <BLU179-W684AF158E3CA4C754A07BCEB230_at_phx.gbl>
If you want the index to be used consistently, why not use a hint? No optimizer is perfect and directives such as Oracle’s hints provide the simplest workaround in situations in which the optimizer has chosen a suboptimal plan. Hints are useful tools not just to remedy an occasional suboptimal plan, but also for users who want to experiment with access paths, or simply have full control over the execution of a query. Iggy > I've been testing using bitmap indexes on 11.2.0.2 EE. When joining two > tables on a column with a BTree index, the index is used in the > execution plan. If this index is changed to a bitmap index, Oracle no > longer uses the index when executing the query! It assigns a higher > cost to using the bitmap index when joining, despite this being a > cheaper approach (in terms of consistent gets). > > Why does this happen? Is this a bug or expected behaviour? -- http://www.freelists.org/webpage/oracle-l Received on Tue Jun 03 2014 - 02:45:42 CEST