Odpowiednik SQL Server klauzuli Oracle USING INDEX

Czy w Oracle jest SQL Server 2008 odpowiednik klauzuli USING INDEX? Specjalnie dla konstruktu:

CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

W dokumentacji serwera Sql na temat unikalnych indeksów stwierdza (podkreślenie dodane):

Unikalne indeksy są implementowane w następujący sposób:

KLUCZ PODSTAWOWY lub UNIKALNE ograniczenie

Podczas tworzenia ograniczenia KLUCZ PODSTAWOWY unikalny indeks klastrowy w kolumnie lub kolumnach jest tworzony automatycznie, jeśli indeks klastrowany w tabeli już nie istnieje i nie określono unikalnego indeksu nieklastrowego . Kolumna klucza podstawowego nie może dopuszczać wartości NULL.

Co wydaje się sugerować, że istnieje sposób określenia, jakiego indeksu należy użyć dla klucza podstawowego.

Czy w Oracle jest SQL Server 2008 odpowiednik klauzuli USING INDEX?

Nie. Po utworzeniu klucza podstawowego lub unikalnego ograniczenia w SQL Server unikalny indeks wspierający to ograniczenie jest tworzony automatycznie, z tymi samymi kluczami.

Co wydaje się sugerować, że istnieje sposób określenia, jakiego indeksu należy użyć dla klucza podstawowego.

Nie. Dokumentacja próbuje jedynie wyjaśnić, czy automatyczny indeks pomocniczy zostanie utworzony jako klastrowany czy nieklastrowany, jeśli nie zostanie określony. Zgadzam się, że jest myląco sformułowany.

Aby to wyjaśnić, po dodaniu ograniczenia klucza podstawowego do istniejącej tabeli bez wyrażania preferencji indeks pomocniczy zostanie zgrupowany, jeśli w tabeli nie będzie wcześniej istniejącego indeksu klastrowego. Indeks pomocniczy zostanie utworzony jako nieklastrowany, jeśli istnieje już indeks klastrowany

Możesz w szczególności zażądać klastrowanego lub nieklastrowanego klucza podstawowego, używając: PRIMARY KEY CLUSTEREDlub PRIMARY KEY NONCLUSTERED.

W uczciwości dokumentacja jest znacznie jaśniejsza na ten temat pod adresem:

table_constraint (Transact-SQL)

Składnia programu SQL Server do tworzenia indeksu klastrowego, który jest również kluczem podstawowym, to:

CREATE TABLE dbo.c
(
    c1 INT NOT NULL, 
    c2 INT NOT NULL,
    CONSTRAINT PK_c
    PRIMARY KEY CLUSTERED (c1, c2)
);

Jeśli chodzi o komentarz: „zmuszenie PK do użycia nazwanego indeksu”, powyższy kod spowoduje, że indeks klucza podstawowego będzie miał nazwę „PK_c”.

Klucz podstawowy i klucz klastrowania nie muszą być tymi samymi kolumnami. Możesz je zdefiniować osobno. W powyższym przykładzie zmień CLUSTEREDsłowo kluczowe na NONCLUSTERED, a następnie po prostu dodaj indeks klastrowy, używając CREATE INDEXskładni:

CREATE TABLE dbo.c
(
    c1 INT,
    c2 INT,
    CONSTRAINT PK_c
    PRIMARY KEY NONCLUSTERED (c1, c2)
);

CREATE CLUSTERED INDEX CX_c ON dbo.c (c2);

W SQL Server indeks klastrowy jest tabelą, są one takie same. Indeks klastrowy określa logiczną kolejność wierszy przechowywanych w tabeli. W moim pierwszym przykładzie wiersze są przechowywane w kolejności wartości c1i c2kolumn. Ponieważ klucz klastrowania jest również definiowany jako klucz podstawowy, kombinacja c1i c2musi być unikalna dla całej tabeli.

W drugim przykładzie klucz podstawowy składa się z kolumn c1i c2, jednak klucz klastrowania jest tylko c2kolumną. Ponieważ nie podałem UNIQUEatrybutu w CREATE INDEXinstrukcji, klucz klastrowania ( c2) nie musi być unikalny w całej tabeli. „Unikator” zostanie automatycznie utworzony przez SQL Server i dodany do wartości w c2kolumnie, aby utworzyć klucz klastrowania. Ten klucz klastrowania, ponieważ jest teraz unikalny, będzie następnie używany jako identyfikator wiersza w innych indeksach utworzonych w tabeli.

W celu udowodnienia korelacja kluczowych kontroli układu wierszy w pamięci, można użyć funkcji nieudokumentowane, fn_PhysLocCracker(%%PHYSLOC%%). Poniższy kod pokazuje, że wiersze są rozmieszczone na dysku w kolejności c2kolumn, które zdefiniowałem jako klucz klastrowania:

USE tempdb;

CREATE TABLE dbo.PKTest
(
    c1 INT NOT NULL
    , c2 INT NOT NULL
    , c3 VARCHAR(256) NOT NULL
);

ALTER TABLE PKTest 
ADD CONSTRAINT PK_PKTest 
PRIMARY KEY NONCLUSTERED (c1, c2);

CREATE CLUSTERED INDEX CX_PKTest 
ON dbo.PKTest(c2);

TRUNCATE TABLE dbo.PKTest;

INSERT INTO dbo.PKTest (c1, c2, c3)
SELECT TOP(25) o1.object_id / o2.object_id, o2.object_id, o1.name + '.' + o2.name
FROM sys.objects o1
    , sys.objects o2
WHERE o1.object_id >0 
    and o2.object_id > 0;

SELECT plc.file_id
    , plc.page_id
    , plc.slot_id
    , pk.*
FROM dbo.PKTest pk
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc;

Wyniki z mojej tempdb to:

Na powyższym obrazie pierwsze trzy kolumny są wyprowadzane z fn_PhysLocCrackerfunkcji, pokazując fizyczną kolejność wierszy na dysku. Możesz zobaczyć, że slot_idwartość zwiększa krok blokady o c2wartość, która jest kluczem do grupowania. Indeks klucza podstawowego przechowuje wiersze w innej kolejności, co można zaobserwować, zmuszając program SQL Server do zwracania wyników skanowania klucza podstawowego:

SELECT pkt.c1
    , pkt.c2
FROM dbo.PKTest pkt WITH (INDEX = PK_PKTest, FORCESCAN);

Uwaga: nie użyłem ORDER BYklauzuli w powyższej instrukcji, ponieważ próbuję pokazać kolejność elementów w indeksie klucza podstawowego.

Dane wyjściowe z powyższego zapytania to:

Patrząc na fn_PhysLocCrackerfunkcję, możemy zobaczyć fizyczną kolejność indeksu klucza podstawowego.

SELECT plc.file_id
    , plc.page_id
    , plc.slot_id
    , pkt.c1
    , pkt.c2
FROM dbo.PKTest pkt WITH (INDEX = PK_PKTest, FORCESCAN)
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc;

Ponieważ czytamy wyłącznie z samego indeksu, tzn. W zapytaniu nie ma odniesienia do żadnych kolumn poza indeksem, %%PHYSLOC%%wartości reprezentują strony w samym indeksie.

Wyniki: