This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,043 Users
  • 2,269,775 Discussions


Queries have different plan with UNION



  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    983327 wrote:
    **At first, thank you so much Jonathan, for your detailed answer and opinions. You asked me for the 3rd execution plan, i und what you mean but you can count it is same with the other individual query's sql_plan.
    **yes , the only difference is name and name2 columns but there are indexes for both columnns for both MWs.

    I'm aware i added many hints but the cst_customer views include 3 different views and those views are using materalized views. I had to use QB_NAME to give specific hints for each nested views.

    Also i tested index full scan and table scan, though full scan shows better costs, index full scan has much more better run times. I think obsolete statics can be the reason for this.
    Your original question was: "I have two queries which use plan X when I run them separately but do something completely different when I stick them together with a UNION". My point about the hints and the degree to which your hints are correct or not was that the UNION introduces a new level of query blocks, and your hints (or some of them) have therefore (probably) gone out of scope and are no longer addressing the query blocks that they need to address. In other words, the new plan may well be the plan that you would get if you deleted all the hints.

    Jonathan Lewis
  • I want to ask sth lastly.

    I know that there is "baseline" with 11g insted of outlines in 10g. I tested baseline on my local database but it is not enough to solve my problem.

    For example;

    I create baseline for the following sql before creating index;

    select name from emp where emp_id=123;

    i checked it is using full table scan.

    Then i create an index for the table and when i query same sql, it is not using index according to created baseline. Everything is ok till here.

    But, when i change my sql like this:

    select name from emp where emp_id=234;

    it is using the index. I'm aware that sql is changed and baseline is not referenced for this query.

    My question is:

    is there anyway to force this query not to use index with different emp_id?
  • >
    My question is:

    is there anyway to force this query not to use index with different emp_id?
    Since that is a new question you need to create a new thread.
This discussion has been closed.