Partitionen einer partitionierten Tabelle aushängen

Question

Hallo Forum,

ich habe eine Faktentabelle mit vielen Millionen Datenzeilen in Partitionen zerlegt, für jedes Buchungsjahr eine Partition in einem separaten File. Nun will ich Daten älter als 2 Jahre für die Anwender zunächst nicht zur Verfügung stellen. Es sollen z.B. bei SELECT * FROM <Tabelle> nur Daten aus den Buchungsjahren 2011 und 2012 kommen. Kann ich die nicht benötigten Partitionen irgendwie aushängen? Erst bei speziellen Anforderungen möchte ich gelegentlich alle Jahre verfügbar machen. Das "Einhängen" muss somit auch möglich sein. Geht so etwas?

Vielen Dank

Franz

Answer 1

Hallo Franz,

erstelle eine View a'la

IF OBJECT_ID('dbo.view_myView', 'V') IS NOT NULL
   DROP VIEW dbo.view_myView
   GO

CREATE VIEW dbo.view_myView
AS
    SELECT * FROM dbo.Faktentabelle
    WHERE Buchungsjahr >= (SELECT MIN(Buchungsjahr) + FROM dbo.Faktentabelle);
GO

-- Berechtigungen erteilen
GRANT SELECT ON dbo.view_myView TO [Berechtigungsgruppe];
REVOKE SELECT ON dbo.Faktentabelle TO [Berechtigungsgruppe];

Eine andere Alternative würden "Partitioned Views" sein - hierzu müßte aber die EINE partitionierte Relation in mehrere Faktentabellen / Buchungsjahr aufgeteilt werden. Anschließend wird eine View erstellt, die nur die von Dir gewünschten Jahre anzeigt.

Über das Thema "Partitioned Views" schreibe ich gerade im Blog - wird aber erst am Wochenende online sein.

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 2

Hallo Uwe,

vielen Dank für Deine schnelle Antwort.
Allerdings: Meine Faktentabelle ist zwar partitioniert, enthält aber immer noch alle Buchungsjahre ab 2001.

So bekomme ich mit

SELECT * FROM dbo.Faktentabelle
WHERE Buchungsjahr >= (SELECT MIN(Buchungsjahr) FROM dbo.Faktentabelle)

doch alle Zeilen ab 2001 geliefert, oder nicht? Die WHERE-Klausel hilft da m.E. nicht weiter, weil sie alle Zeilen filtert, in denen das Buchungsjahr größer gleich dem kleinsten Buchungsjahr ist, eben 2001.
Eine Lösung wie etwa

CREATE VIEW dbo.view_myView
AS
    SELECT * FROM dbo.Faktentabelle
    WHERE Buchungsjahr >= 2011
GO

wäre aus naheliegenden Gründen nicht so toll.

Bye

Franz

Answer 3

Hallo Franz,

sorry - ich habe da tatsächlich nicht richtig gelesen. Ich habe verstanden, die LETZTEN 2 Jahre nicht zur Verfügung zu stellen.

In diesem Fall muß die View tatsächlich etwas anders aussehen. Damit sie jedoch dynamisch bleibt, sollte auf jeden Fall das Buchungsjahr berechnet werden.

CREATE VIEW dbo.view_myView
 AS
     SELECT * FROM dbo.Faktentabelle
     WHERE Buchungsjahr > YEAR(getdate() - 2)
 GO

Da ja auf Buchungsjahr ein Index liegt, wird SQL Server selbst erkennen, auf welche Partitionen zugegriffen werden muss.

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 4

Hi,

das wird zwar funktionieren, aber so hatte ich mir das nicht gedacht. Dann müssen ja laufend die Views angepasst werden, wenn bestimmte Buchungsjahre zur Verfügung zu stellen oder herauszunehmen sind.

Ich habe ein bißchen gelesen und getestet: die Lösung lautet SWITCH. Eigentlich ganz einfach. Man legt eine Archivtabelle an, die genau der Definition der Faktentabelle entspricht, auch bzgl. der Partitionen. Mit den Indizes muss man aufpassen, die müssen alle entlang der Partitionierungsspalte definiert sein, sofern man sie überhaupt in der Archivtabelle braucht. Dann kann man folgendes machen:

Aushängen:

ALTER TABLE dbo.Faktentabelle
SWITCH PARTITION 1 TO dbo.FaktentabelleARCHIV PARTITION 1;

Wenn ich jetzt SELECT * FROM dbo.Faktentabelle aufrufe, bleiben die Buchungsjahre aus Partition 1 aussen vor.

Einhängen:

ALTER TABLE dbo.FaktentabelleARCHIV
SWITCH PARTITION 1 TO dbo.Faktentabelle PARTITION 1;

Bye

Michael

Answer 5

Hallo Michael,
Deine Lösung trifft aber immer alle User, wohingegen der Ansatz von Uwe individuell nutzbar ist.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 6

Ja, das geht normaler Weise. Wichtig ist, das dem entsprechend danach die Partitionierungsfunktion angepasst wird. Es sollte im Grunde ein Sliding Window sein.

Answer 7

Ja, das mit dem "Aushängen" trifft dann immer alle User, das ist zunächst so gewollt. Andererseits interessiert mich schon, wie der Ansatz von Uwe individuell nutzbar gemacht werden kann. Ist es damit möglich beispielsweise dem Benutzer A die Jahre 20010 bis 2012 und dem Benuter B die Jahre 2004 bis 2009 zu zeigen? Wie geht das?

Danke schon mal vorab.

Answer 8

hallo Franz,

das geht natürlich auch beutzerabhängig. Lege z. B. zwei Rollen in der Datenbank an:

db_CurrentYears
db_FormerYears

Dann ändere die Abfrage wie folgt um:

CREATE VIEW dbo.view_myView
 AS
     SELECT * FROM dbo.Faktentabelle
     WHERE Buchungsjahr > YEAR(getdate() - 
     -- Wenn es Benutzer der Gruppe db_CurrentYear
     -- sind, nur die letzten 2 Jahre sonst alle Jahre
     CASE WHEN IS_MEMBER('db_CurrentYears') = 1
          THEN 2
          ELSE 99
     END
 GO

Das ist jetzt mal nur so "auf die Schnelle" gemacht. Sicherlich kann man das  noch verfeinern aber das Prinzip sollte klar sein :)

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)