Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Guidance on Creating a Partitioned and Indexed Materialized View

    Hi all,

    I wonder if you can help. I'm at the beginning stages of my career with oracle so a lot of this is new to me, but at the moment I am trying to create a materlized view that I would like to be both partitioned and indexed (if this is possible).

    I'm gathering examples from all over but won't to get my syntax correct before I start putting it into action... (I can't test just yet because I'm waiting for allocated tablespace).

    My materialized view will contain shop data for the previous week, and I want to partition based on the dayofweek, and index on the dayofweek (yyyymmdd), shopid (0000), and productid (0000).

    Code:
    
    CREATE MATERIALIZED VIEW U_CHRISTYXO.WEEK_DATA_MV 
    PARTITION BY RANGE (DAYOFWEEK)
      ( PARTITION DAY1 VALUES in (1)
    ,   PARTITION DAY2 VALUES in (2)
    ,   PARTITION DAY3 VALUES in (3)
    ,   PARTITION DAY4 VALUES in (4)
    ,   PARTITION DAY5 VALUES in (5)
    ,   PARTITION DAY6 VALUES in (6)
    ,   PARTITION DAY7 VALUES in (7)
    )
    
    CREATE
      INDEX "U_CHRISTYXO"."DAYOFWEEK" ON "U_CHRISTYXO"."WEEK_DATA_MV "
      (
        "DAYOFWEEK"
      )
      TABLESPACE "TSD_CHRISTYXO"
    
    CREATE
      INDEX "U_CHRISTYXO"."SHOPID" ON "U_CHRISTYXO"."WEEK_DATA_MV "
      (
        "SHOPID"
      )
      TABLESPACE "TSD_CHRISTYXO"
    
    CREATE
      INDEX "U_CHRISTYXO"."PRODUCTID" ON "U_CHRISTYXO"."WEEK_DATA_MV "
      (
        "PRODUCTID"
      )
      TABLESPACE "TSD_CHRISTYXO"
    
    ENABLE QUERY REWRITE AS 
    (
    SELECT  DAYOFWEEK, SHOPID, PRODUCTID, 
    FROM    whouse.shop_data
    WHERE  ...
    );
    Could someone give me pointers as to whether I'm going in the right direction or offer best practice?

    Christy

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >offer best practice?
    avoid using double quote marks around Oracle object names!

    If you look closely, you can see an extraneous space character to the right of "WEEK_DATA_MV "; which will likely cause a problem.
    Based upon what testing did you decide that partitioned & indexed Material View was the optimal solution?
    What problem are you trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Thanks. In my actual query I won't have the double quotes. Unfortunately the Oracle derived SQL statement behind my original table had these in place...

    Essentially I will be creating a table for analysis within PowerPivot. They are only interested in the data for the previous week and this needs to be refreshed every Monday (I will be including a Months worth of data regardless).

    Our shop data contains about 17,000 rows of data every day (or about 476,000 per month) which is not a huge amount of data but given the amount of usage that it will be getting and the fact that I'll be refreshing every Monday, I want to ensure that the table I create is as efficient as it can be.

    Note that I also have plans to use this model on a much larger scale (90,000,000 rows of data) so I want to ensure that what I do is correct. It is this 90,000,000 rows of data that are forcing me to consider the partitioning and indexing of the view but not the immediate issue I need to resolve.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It is this 90,000,000 rows of data that are forcing me to consider the partitioning and indexing of the view
    No it is just voices between your ears that cause you to over engineer this Rube Goldberg "solution".
    You have no evidence that any problem really exists.

    post SQL & EXPLAIN PLAN against plain table populated with 90,000,000 rows against a VIEW of only the last 7 days data
    Last edited by anacedent; 07-09-15 at 00:04.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I'm beginning to remember why I stopped using this forum. Lets go back to the original question please;

    My problem is that I WANT to create a materialized view, that I want to partition and index, and I would like to know the best method of doing so. If someone can help me with that, I would appreciate it.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    They are trying to help you learn good habits. I agree that you WANT to use a MV but is that the best method? Since your only filling the table once a week, why not a standard table unpartitioned but indexed. WI e have tables that are over 200 million rows and get instant access using the index in the query. And a simple truncate will empty it out for the next refill.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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