Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    63

    Unanswered: Data partition view

    Hi ,
    I have question regard data partition view .

    Please see below sample from BOL + sample of execution plane .

    I would like to ask what is the way to avoid the optimizer scan tables out of the scope (I would expect that the only table for this query will be SUPPLY1)

    Thanks,
    Eyal




    --This example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4, which correspond to the supplier tables from four offices, located in different countries/regions.
    USE tempdb
    GO


    --create the tables and insert the values
    CREATE TABLE SUPPLY1 (
    supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
    supplier CHAR(50)
    )
    CREATE TABLE SUPPLY2 (
    supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
    supplier CHAR(50)
    )
    CREATE TABLE SUPPLY3 (
    supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
    supplier CHAR(50)
    )
    CREATE TABLE SUPPLY4 (
    supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
    supplier CHAR(50)
    )
    GO
    --create the view that combines all supplier tables
    CREATE VIEW all_supplier_view
    AS
    SELECT *
    FROM SUPPLY1
    UNION ALL
    SELECT *
    FROM SUPPLY2
    UNION ALL
    SELECT *
    FROM SUPPLY3
    UNION ALL
    SELECT *
    FROM SUPPLY4
    GO

    INSERT all_supplier_view VALUES ('1', 'CaliforniaCorp')
    INSERT all_supplier_view VALUES ('5', 'BraziliaLtd')
    INSERT all_supplier_view VALUES ('231', 'FarEast')
    INSERT all_supplier_view VALUES ('280', 'NZ')
    INSERT all_supplier_view VALUES ('321', 'EuroGroup')
    INSERT all_supplier_view VALUES ('442', 'UKArchip')
    INSERT all_supplier_view VALUES ('475', 'India')
    INSERT all_supplier_view VALUES ('521', 'Afrique')

    GO
    /* */
    SELECT * FROM all_supplier_view WHERE supplyID BETWEEN 1 and 150
    Attached Thumbnails Attached Thumbnails executionplan.jpg  

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First

    THAT'S A GREAT POST

    Second

    While you see the optimizer plan, if you look at the number of executes for the other three, you'll see 0. It never did anywork....

    And you got Index seeks going on in parallel...

    pretty damn effecient, no?

    Don't do sample tests in tempdb though, and provide clean up DROPs with the sample...otherwise PERFECT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Posts
    63
    Thanks on the compliments.

    I have send only sample.
    I have almost the same design in a production environment with ~ 1-20 million records per each physical table.

    I need to data from only one table with around 3 million recodes in most of the queries.
    but I have other table with ~20 million recodes. It means that most on the execution time is useless.

    I have done test with direct query to the correct physical table and got the result in 10% of the time!! Vs query the partition view .

    I though that partition data view was planed to deal with such cases .

    Thanks a lot ,
    Eyal

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you saying you need to return 3 million records?

    I must be misinterpreting...

    What does the plan say for the large volume?

    Does it show the number of executes other than 0 for the other three?

    Are these the only columns in the table?

    It doesn't say it, but because the other column is not in the index, it has to go to the data page to get the other column...make a non unique index on the other column...

    I'll test it out and see what it does....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2002
    Posts
    63
    1.I mean that the number of records in the target physical table are ~ 3 melon records vs. ~ 20 million records in other physical table (each physical table means other quarter). The output of the tested query was 7,000 rows.
    2.The plans show 1 as number of executes for the other tree tables
    3.In my tables there are 5 primary keys ( the DataID is one of it , this one is used as partition constraint) and other 10 measurement columns

    eyal

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, ok then....

    what are you going to do with 7,000 rows? can't be OLTP....are doing batch work against them?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2002
    Posts
    63
    I insert the result to Daily table group by the extract date and one of the primary keys (means get yesterday records group by on of the rest 4 keys).

    This is daily job use to reporting purpose .
    Eyal

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I may be wrong, but I don't think you can avoid the reference to other tables participating the view, simply because the view is UNION-based.

Posting Permissions

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