SQL Server (και άλλες databases)

Απ: foreign keys and indexes

KelMan — Wed, 31 May 2006 18:43:54 GMT

agmarios wrote:

Αυτό κατάλαβα κι εγώ, συνοψίζω:

1. Ορίζοντας Primary Key σε ένα πίνακα δημιουργείται και το αντίστοιχο index

πχ:

create table fl_country(
   cnId int identity(1,1) constraint countryPK primary key,
   cnISOcode nchar(2) not null constraint uniqueISOcode unique(cnISOcode),
   cnName nvarchar(64) not null)

Σωστά.

agmarios wrote:

2. Ορίζοντας Foreign Key σε έναν δεύτερο πίνακα δεν δημιουργείται Index αλλά βελτιστοποιείται για join με τον πρώτο;

π.χ.

create table fl_location(
   locId int identity(1,1) constraint locationPK primary key,
   locCountry int constraint locationFKcountry foreign key references fl_country(cnId),
   locName nvarchar(64) not null)

Απλά ορίζεις ένα constraint. Δεν συμβαίνει τίποτα παραπάνω, δεν συμβαίνει καμιά διαδικασία "βελτιστοποίησης".

agmarios wrote:

3. To join τους είναι optimized, δεν χρειάζεται index το locCountry

select l.locId,l.locName, c.cnName
from fl_location as l inner join fl_country as c on l.locCountry = c.cnId

Τυπικά, δεν υπάρχει κάτι τέτοιο που να λέγεται "optimized join". Τα queries που γράφεις σε συνδυασμό με τα indexes που υπάρχουν είναι optimized ή όχι. Στη συγκεκριμένη περίπτωση, κατά πάσα πιθανότητα, το query σου θα είναι optimized. Δεν χρειάζεται να ορίσεις extra index στο l.locCountry γιατί υπάρχει ήδη αυτό του c.cnId που "βολεύει" τον optimizer περισσότερο καθώς έχει μεγαλύτερο granularity, δηλαδή μοναδικές τιμές, σε σχέση με το l.locCountry που θα έχει επαναλαμβανόμενες τιμές. Βέβαια, όλα αυτά μπορεί να αλλάξουν αν προσθέσεις κάποιο WHERE clause. Γι αυτό, αυτό που παίζει ρόλο δεν είναι μόνο αν υπάρχουν indexes πάνω στα πεδία που γίνεται το join, αλλά στο σύνολό του το query. Και ο καλύτερος τρόπος για να το καταλλάβεις αυτό είναι να μάθεις να διαβάζεις το execution plan του query.

Απ: foreign keys and indexes

agmarios — Wed, 31 May 2006 18:12:37 GMT

Αυτό κατάλαβα κι εγώ, συνοψίζω:

1. Ορίζοντας Primary Key σε ένα πίνακα δημιουργείται και το αντίστοιχο index

πχ:

create table fl_country(
   cnId int identity(1,1) constraint countryPK primary key,
   cnISOcode nchar(2) not null constraint uniqueISOcode unique(cnISOcode),
   cnName nvarchar(64) not null)

2. Ορίζοντας Foreign Key σε έναν δεύτερο πίνακα δεν δημιουργείται Index αλλά βελτιστοποιείται για join με τον πρώτο;

π.χ.

create table fl_location(
   locId int identity(1,1) constraint locationPK primary key,
   locCountry int constraint locationFKcountry foreign key references fl_country(cnId),
   locName nvarchar(64) not null)

3. To join τους είναι optimized, δεν χρειάζεται index το locCountry

select l.locId,l.locName, c.cnName
from fl_location as l inner join fl_country as c on l.locCountry = c.cnId

Απ: foreign keys and indexes

KelMan — Wed, 31 May 2006 17:45:30 GMT

Μάλλον δεν ερμηνεύεις σωστά αυτά που λέει το απόσπασμα. Αρχικά λέει ότι μπορείς να κάνεις join σε οποιαδήποτε πεδία (αρκεί βέβαια να έχει νόημα κάτι τέτοιο) χωρίς να έχεις καθορίσει PK/FK constraints. Λέγοντας "optimized" εννοεί ότι εφ'όσον έχουν φτιαχτεί τα PKs και κατ' επέκταση τα indexes, τότε το join θα χρησιμοποιήσει αυτά τα indexes, δηλαδή οι πίνακες έχουν γίνει optimized έτσι ώστε τα συγκεκριμένα queries να χρησιμοποιήσουν τα indexes.

Απ: foreign keys and indexes

agmarios — Wed, 31 May 2006 17:32:46 GMT

Ξέρω ότι το θέμα έχει συζητηθεί και στο παρελθόν αλλά κάτι δεν μου πάει καλά, στο BOL του SQL γράφει:

Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table. An index allows Microsoft® SQL Server™ 2000 to find related data in the foreign key table quickly. However, creating this index is not a requirement.

αυτό συμφωνεί με αυτά που λες

Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

αλλά εδώ τα αλλάζει αφού λέει ότι κάνει optimize τους πίνακες

υ.γ. ελπίζω κάποια στιγμή να βγάλω άκρη

Απ: foreign keys and indexes

KelMan — Wed, 31 May 2006 04:58:44 GMT

agmarios wrote:

Είχα την εντύπωση ότι όταν δηλώνεις ένα foreign key ο sql δημιουργεί μόνος του το αντίστοιχο index.
Στο κάτω - κάτω είναι προφανές ότι θα κάνεις join τους 2 πίνακες (αλλιώς τι relation είναι αυτό).
Νομίζω μάλιστα ότι είναι βελτιστοποιημένος για joins πάνω σε foreign keys.

Όχι, δεν ισχύει κάτι τέτοιο... Στο παράδειγμα που έδωσε ο Παναγιώτης έχεις τον πίνακα Customers με κλειδί στο CustomerID και τον πίνακα Orders με κλειδί στο πεδίο OrderID κι ένα CustomerID πεδίο που δείχνει τον πελάτη που έχει βάλει την συγκεκριμένη παραγγελία (δεν εξετάζουμε το τι είδος index έχει δημιουργηθεί, αυτό το έχουμε κουβεντιάσει εδώ). Τώρα, αν επιχειρήσουμε να φτιάξουμε το PK/FK constraint στο CustomerID θα πάρουμε λάθος αν το CustomerID στον Customers δεν είναι PK. Όπερ σημαίνει ότι το constraint τυγχάνει να βρει τα indexes που έχουν δημιουργηθεί για το PK, δεν δημιουργείται κανένα index κατά τη στιγμή που ορίζεις το constraint.

Απ: foreign keys and indexes

evliatsas — Wed, 31 May 2006 03:51:17 GMT

To να δηλώνεις ένα Foreign Key, είναι ακριβώς η δημιουργία του Relation.

Απλά, επάνω στο Relation μπορείς να ορίσεις κάποια Constraints τα οποία δεν είναι default.

foreign keys and indexes

agmarios — Wed, 31 May 2006 01:46:59 GMT

Σε ένα post πιο πάνω βρήκα το εξής:

pkanavos wrote:

......
Τώρα για τις άλλες ερωτήσεις: Πρώτα η εύκολη. Προσθέτωντας relations δεν κερδίζεις σε ταχύτητα. Γλυτώνεις όμως μεγάλους μπελάδες καθώς τα relations δεν επιτρέπουν να μπουν τιμές σε ένα πεδίο που δεν υπάρχουν και στον parent πίνακα. Π.χ. αν έχεις ένα πίνακα με παραγγελίες Orders και ένα πίνακα με προμηθευτές Suppliers και ορίσεις ένα foreign key από το πεδίο SupplierID του Orders στο SupplierID του Suppliers, δεν μπορείς να βάλεις στον πίνακα Orders μια τιμή SupplierID η οποία δεν υπάρχει και στον πίνακα Suppliers. Διαφορετικά θα ήσουν υποχρεωμένος να κάνεις τον έλεγχο εσύ ο ίδιος.
Άσε που αν πας να καταλάβεις το σχήμα μιας βάσης χωρίς relations έχεις χάσει από χέρι.

...........

Είχα την εντύπωση ότι όταν δηλώνεις ένα foreign key ο sql δημιουργεί μόνος του το αντίστοιχο index.
Στο κάτω - κάτω είναι προφανές ότι θα κάνεις join τους 2 πίνακες (αλλιώς τι relation είναι αυτό).
Νομίζω μάλιστα ότι είναι βελτιστοποιημένος για joins πάνω σε foreign keys.

Μάριος
υ.γ. αν έχεις δίκιο έχω πολλούς indexes να προσθέσω