Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009

    Unanswered: Query optimization with materilized view

    Hello everybody!

    I have a question about an article which was published in the May
    issue of Oracle magazine by Tom Kyte.
    Ask Tom: On Constraints, Metadata, and Truth

    My problem is with the last example:

    He issues a 'select count(*) from emp' and compares the performance
    depending on some existing/non existing constraints. And there is also
    a materialized view involved, which is used to optimize the query in
    the second case.

    What I don't understand is that how can the optimizer use the mat view
    instead of the table? I mean the mat view might contain outdated data
    and when we are querying 'emp', we are curious about the live data,
    aren't we?

    What if I create the mat view, then I add more lines to the 'emp'
    table and then run the 'select count(*) from emp' query? If it goes to
    the mat view, it will return wrong row number for the 'emp' table.

    Do I misunderstand something?

    Thanks for any help

  2. #2
    Join Date
    Jun 2004
    Liverpool, NY USA
    His MVIEW was updated on commit automatically. It was always correct.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by fabitamas

    Do I misunderstand something?
    No, you don't. Materialized views, as many other things, are a trade-off between performance and reliability.

    - if you do not have an MV, your query has to perform complex operations every time it runs, and its performance suffers;
    - if you have an MV with immediate propagation (update on commit) your query benefits from reading precomputed results, however, inserts, updates, and deletes against the base table suffer since they wait for the propagation to occur;
    - if you have an MV with delayed propagation there is only a positive effect on the performance, but at the price of slightly outdated data, which in many cases quite acceptable. For example, MVs are common in data warehouse environments where all data are at least one day old anyway.
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2009
    Thanks for the answers!

    To sum up, the optimizer uses the mat view because it knows that it always contains 'live' data.

    If we would have n_i's third case, the optimization would not occur, right?

Posting Permissions

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