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 150First
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|||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|||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...|||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|||Well, ok then...
what are you going to do with 7,000 rows? can't be OLTP...are doing batch work against them?|||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|||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.
