Χθες κατά την διάρκεια του μαθήματος που έκανα ένα αγαπητός συνάδελφος στην συζήτηση που είχαμε ανέφερε ότι όποτε κάνεις ένα query που κάνει COUNT(*) στα δεδομένα μας κάνει πάντα table scan.
Φυσικά δεν θα μπορούσα να αφήσω την ευκαιρία χαμένη και δείξω ότι δεν είναι έτσι τα πράγματα και για αυτό του έκανα το παρακάτω demo με τις απαραίτητες φυσικά επεξηγήσεις το οποίο θα μοιραστώ και με εσάς.
Για το λόγο αυτό ας φτιάξουμε ένα πίνακα του οποίου το record length θα είναι τέτοιο ώστε σε κάθε data page να χωράει ένα record και αυτό το κάνω ώστε να γίνουν ευκολότερα τα μαθηματικά που θα χρειαστούμε για το παράδειγμα μας.
CREATE TABLE T
(
COL1 INT IDENTITY
, COL2 INT DEFAULT (1)
, COL3 CHAR(8000) DEFAULT (REPLICATE('A',8000))
);
GO
INSERT INTO T DEFAULT VALUES;
GO 10000
UPDATE T SET COL2=COL1;
GO
Ο πίνακας μας έτσι όπως είναι δεν έχει κάποιον index και είναι σε heap όπως φαίνεται από την εκτέλεση των στατιστικών των indexes
SELECT index_id
, index_type_desc
, index_depth
, index_level
, page_count
, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('T'),DEFAULT,DEFAULT,'DETAILED')
GO
index_id index_type_desc index_depth index_level page_count record_count
----------- ----------------- ----------- ----------- -------------------- --------------------
0 HEAP 1 0 10000 10000
Αν κάνουμε COUNT(*) σε αυτό λογικό είναι ότι αυτό θα κάνει table scan όπως και φαίνεται από το execution plan
SET SHOWPLAN_ALL ON;
GO
SELECT COUNT(*) FROM T;
GO
SET SHOWPLAN_ALL OFF;
GO
StmtText StmtId NodeId Parent PhysicalOp LogicalOp
---------------------------------------------------------------------------- ----------- ----------- ----------- ----------------- ---------------
SELECT COUNT(*) FROM T; 1 1 0 NULL NULL
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0))) 1 2 1 Compute Scalar Compute Scalar
|--Stream Aggregate(DEFINE:([Expr1007]=Count(*))) 1 3 2 Stream Aggregate Aggregate
|--Table Scan(OBJECT:([demodb].[dbo].[T])) 1 4 3 Table Scan Table Scan
Αν σε αυτό τον πίνακα φτιάξουμε έναν clustered index και δούμε τα index statistics θα δούμε ότι πλέον δεν έχουμε HEAP αλλά την αναμενόμενη δομή ενός clustered index.
CREATE CLUSTERED INDEX I1 ON T(COL1);
GO
SELECT index_id
, index_type_desc
, index_depth
, index_level
, page_count
, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('T'),DEFAULT,DEFAULT,'DETAILED')
GO
index_id index_type_desc index_depth index_level page_count record_count
----------- ------------------------------------------------------------ ----------- ----------- -------------------- --------------------
1 CLUSTERED INDEX 3 0 10000 10000
1 CLUSTERED INDEX 3 1 19 10000
1 CLUSTERED INDEX 3 2 1 19
Αν κάνουμε COUNT(*) όπως είναι τώρα θα δούμε το λογικό που δεν είναι κάτι άλλο από να χρησιμοποιήσει τον clustered index καθώς πλέον με βάση αυτόν είναι δομημένα τα δεδομένα μας στα data pages, και όπως φαίνεται και από το execution plan κάνει scan με βάση αυτόν (clustered index)
SET SHOWPLAN_ALL ON;
GO
SELECT COUNT(*) FROM T;
GO
SET SHOWPLAN_ALL OFF;
GO
StmtText StmtId NodeId Parent PhysicalOp LogicalOp
---------------------------------------------------------------------------- ----------- ------ ------ -------------------- --------------------
SELECT COUNT(*) FROM T; 1 1 0 NULL NULL
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0))) 1 2 1 Compute Scalar Compute Scalar
|--Stream Aggregate(DEFINE:([Expr1007]=Count(*))) 1 3 2 Stream Aggregate Aggregate
|--Clustered Index Scan(OBJECT:([demodb].[dbo].[T].[I1])) 1 4 3 Clustered Index Scan Clustered Index Scan
Αν έτσι όπως είμαστε φτιάξουμε ακόμα ένα index ο οποίος θα είναι έτσι και αλλιώς nonclustered.
CREATE INDEX I2 ON T(COL2);
GO
Αν δούμε τα index statistics θα δούμε ότι έχουμε πλέον δύο indexes
SELECT index_id
, index_type_desc
, index_depth
, index_level
, page_count
, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('T'),DEFAULT,DEFAULT,'DETAILED')
GO
index_id index_type_desc index_depth index_level page_count record_count
----------- ------------------------------------------------------------ ----------- ----------- -------------------- --------------------
1 CLUSTERED INDEX 3 0 10000 10000
1 CLUSTERED INDEX 3 1 19 10000
1 CLUSTERED INDEX 3 2 1 19
2 NONCLUSTERED INDEX 2 0 20 10000
2 NONCLUSTERED INDEX 2 1 1 20
Εκτελώντας πάλι το COUNT(*) θα δούμε ότι αυτή την φορά χρησιμοποιεί τον δεύτερο index και αυτό γιατί ο SQL Server όταν κάνει COUNT(*) σε ένα πίνακα στο οποίο υπάρχουν indexes χρησιμοποιεί πάντα αυτόν που έχει τον μικρότερο αριθμό σελίδων στο leaf level του καθώς μπορεί από εκεί με ασφάλεια να βγάλει το αποτέλεσμα.
SET SHOWPLAN_ALL ON;
GO
SELECT COUNT(*) FROM T;
GO
SET SHOWPLAN_ALL OFF;
GO
StmtText StmtId NodeId Parent PhysicalOp LogicalOp
---------------------------------------------------------------------------- ----------- ------ ------ ---------------- ---------------
SELECT COUNT(*) FROM T; 1 1 0 NULL NULL
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0))) 1 2 1 Compute Scalar Compute Scalar
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*))) 1 3 2 Stream Aggregate Aggregate
|--Index Scan(OBJECT:([demodb].[dbo].[T].[I2])) 1 4 3 Index Scan Index Scan
/*antonch*/