Re: Optimizing Queries Against UNIONed View

On Jan 23, 6:35 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Valentin Minzatu wrote:
> > I do not think you can stop accessing both tables, but I do not see why
> > it would do FTS on any of them - I tried your scenario and it does use
> > indexes on both tables. The only way I can think of not to access both
> > tables would be to use list partitioning, but that is an option to the
> > enterprise edition (additional money for the license).I can't find or recall the original query but just making hopefully
> reasonable assumptions it should access both tables ... but not
> necessarily both indexes.
>
> It depends on what the optimizer assumes to be the cost. Hint the
> table, run an explain plan, and see why the optimizer thinks is the
> cost of using the index.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

I assumed the query is: SELECT * FROM CODE_LOOKUP WHERE CODE_TYPE = 'CODE_TYPE2' AND CODE_NAME
= 'CD5'; as per OP's first post. CODE_NAME is PK and the optimizer will use it primarily over the other indexes due to ... uniqueness Received on Wed Jan 24 2007 - 09:15:54 CST