none
Partitionen einer partitionierten Tabelle aushängen

    Frage

  • 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

    Donnerstag, 6. Dezember 2012 10:13

Antworten

  • 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)

    Freitag, 7. Dezember 2012 08:01
  • 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)

    Donnerstag, 13. Dezember 2012 20:14

Alle Antworten

  • 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)

    Donnerstag, 6. Dezember 2012 10:18
  • 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

    Donnerstag, 6. Dezember 2012 11:12
  • 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)

    Freitag, 7. Dezember 2012 08:01
  • 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

    Dienstag, 11. Dezember 2012 19:16
  • 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

    Mittwoch, 12. Dezember 2012 07:49
  • Ja, das geht normaler Weise. Wichtig ist, das dem entsprechend danach die Partitionierungsfunktion angepasst wird. Es sollte im Grunde ein Sliding Window sein.
    Mittwoch, 12. Dezember 2012 21:57
  • 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.

    Donnerstag, 13. Dezember 2012 19:00
  • 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)

    Donnerstag, 13. Dezember 2012 20:14