Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Wrong optimizer plan for the view

    Hi, everyone. Please, help me deal with the problem.

    I have the view table_client_view which performs the following SELECT statement from another, materialized view table_client:

    Code:
    SELECT 
    	MAX(a.id) AS id, 
    	SUM (a.n_active) AS n_active,
    	SUM (a.n_suspended) AS n_suspended,
    	SUM (a.n_canceled) AS n_canceled,
    	a.client_no AS client_no,
    	MAX (a.name) AS name,
    	MAX (a.parent) as parent
    FROM table_client a
    GROUP BY a.client_no, a.parent
    Materialized view has two indexes on id and parent columns.
    The problem is: if I try to select the data from table_client_view like this:

    Code:
    -- CASE 1 (non-working)
    select * from table_client_view a
    where ((a.parent = 123) AND (a.id != -2))
    The optimizer builds execution plan without INDEX RANGE SCAN on parent column, ignoring the index. But, if I perform the query with outer SELECT as follows:

    Code:
    -- CASE 2 (working)
    SELECT * FROM (
    SELECT 
    	MAX(a.id) AS id, 
    	SUM (a.n_active) AS n_active,
    	SUM (a.n_suspended) AS n_suspended,
    	SUM (a.n_canceled) AS n_canceled,
    	a.client_no AS client_no,
    	MAX (a.name) AS name,
    	MAX (a.parent) as parent
    FROM table_client a
    GROUP BY a.client_no, a.parent
    ) b
    WHERE ((b.parent = 123) AND (a.id != -2))
    everything is okay and optimizer performs INDEX RANGE SCAN using index for parent column. I don't understand why it does this. I tried using /*+ INDEX */ hint for CASE 1, but no success.

    Does anybody have an idea, why the plans are different? Thanks in advance.
    Last edited by damascus86; 09-22-09 at 08:15.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    have statistics been gathered?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Quote Originally Posted by beilstwh
    have statistics been gathered?
    Yes, statistics have been gathered, but the plans are different though

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •