| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-04-04, 12:56
|
|
Registered User
|
|
Join Date: Mar 2004
Posts: 28
|
|
|
How do I explain Indexes?
|
|
Hello,
A co-worker and I are having a discussion, and I'm having a tough time explaining a concept.
We are talking about specifically SQL Server, but I don't think it matters.
Take the following statement:
Select * from TableA where @id = id
My contention is that if there is no index on the table, then the engine is forced to do a table scan on the entire table.
If we add an index to the ID column, then the engine can and will use the index to retrieve the data. Further, I contend that the engine will NOT do a full table scan on the table. It uses the "magic" of the index to find the right record(s), which will cut the number of reads from potentially millions to probably under 50.
My co-worker is convinced that regardless of the existence of the index, the engine must do a full scan of the entire table.
He states that the index will make the full scan faster, but the full scan must take place.
For me it makes sense to think of the Public library analogy. I think of the books on the shelves being the main table, with the card catalog being the index. My co-worker states that this analogy cannot be applied to tables and indexes as rows are being checked in and out and there is no way an index could keep up with the transactions.
Suggestions?
Obviously we are at logger heads....
Thanks in advance!!!!
|
|
03-04-04, 13:05
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
|
Re: How do I explain Indexes?
Your co-worker is completely and utterly wrong, of course! The index contains the ID values (usually in a b-tree format, together with a physical pointer to the relevant row. When you select for a specific ID, the index is searched for that value (very small number of reads), the physical address of the row is obtained and then that row is read in 1 "random access" read. No full scan is performed.
I would imagine that the concept of indexes would be covered in the SQL Server documentation somewhere - I could point you to the Oracle equivalent if it helps.
|
|
03-04-04, 13:14
|
|
Registered User
|
|
Join Date: Mar 2004
Posts: 28
|
|
|
Re: How do I explain Indexes?
|
|
The fascinating thing is when I tried to show him docs.
I looked high and low.
Documentation I found said things like "indexes can speed up searches," and "indexes can be used to decrease query time".
Then they jump STRAIGHT to clustered, composite, unique, etc., and spend time explaining the differences.
So to me, an index that is "close" can make performance gains of magnitudes in the order of 1,000,000 percent faster on large enough tables.
Literally.
If anyone has a good description of HOW an index does its job, I'd appreciate the link.
Thanks again!!!!!!!!!!
|
|
03-04-04, 13:16
|
|
Registered User
|
|
Join Date: Mar 2004
Posts: 28
|
|
|
Re: How do I explain Indexes?
From my co-worker. Again I disagree with him.
i said that if an index is created, it still has to scan the entire index
actually, i said that if a table/index is queried, sql server scans the entire table/index
so if there is no index, the entire table is scanned. if there is an index, the entire index is scanned.
|
|
03-04-04, 13:44
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
There are days that it pays to be a crusty old type. Issue a:
SET SHOWPLAN_TEXT ON
Then run the query(s). Then you can see what happened! Something like:
PHP Code:
CREATE TABLE dbo.inddemo (
inddemoId INT IDENTITY
, notes VARCHAR(2000) NOT NULL
)
INSERT INTO dbo.inddemo (notes) VALUES ('One')
INSERT INTO dbo.inddemo (notes) VALUES ('Two')
INSERT INTO dbo.inddemo (notes) VALUES ('Three')
INSERT INTO dbo.inddemo (notes) VALUES ('Four')
INSERT INTO dbo.inddemo (notes) VALUES ('Five')
INSERT INTO dbo.inddemo (notes) VALUES ('Six')
INSERT INTO dbo.inddemo (notes) VALUES ('Seven')
INSERT INTO dbo.inddemo (notes) VALUES ('Eight')
INSERT INTO dbo.inddemo (notes) VALUES ('Nine')
INSERT INTO dbo.inddemo (notes) VALUES ('Ten')
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM dbo.inddemo WHERE 3 = inddemoId
GO
SET SHOWPLAN_TEXT OFF
GO
ALTER TABLE dbo.inddemo
ADD CONSTRAINT XPKinddemo PRIMARY KEY (inddemoId)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM dbo.inddemo WHERE 3 = inddemoId
GO
-PatP
|
|
03-04-04, 14:42
|
|
Registered User
|
|
Join Date: Mar 2004
Posts: 28
|
|
|
index seek
--Clustered Index Seek(O etc.....
Ok, so I understand how a B-tree traversal works.
Where can I find documentation that when it says "--Clustered Index Seek" that does not mean the engine is scanning the entire "index."
To me this is one of those content/context issues.
My co-worker is reading the content of the docs. But the docs don't give the context of what is really going on.
Thanks again.
|
|
03-04-04, 14:56
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
Ok, let's approach this from the physical side instead of the logical side. Sometimes that is easier to understand because it is nice and concrete. Try something more like
PHP Code:
CREATE TABLE dbo.inddemo2 (
inddemo2Id INT IDENTITY
, whatever VARCHAR(2000) NOT NULL
)
GO
DECLARE @i INT
SELECT @i = 10000
WHILE 0 < @i
BEGIN
INSERT dbo.inddemo2 (whatever) VALUES ('Whatever it takes to make a point')
SELECT @i = @i - 1
END
GO
SET STATISTICS IO ON
GO
SELECT * FROM dbo.inddemo2 WHERE 3 = inddemo2Id
GO
ALTER TABLE dbo.inddemo2
ADD CONSTRAINT XPKinddemo2 PRIMARY KEY (inddemo2Id)
GO
SELECT * FROM dbo.inddemo2 WHERE 3 = inddemo2Id
GO
-PatP
|
|
03-05-04, 05:22
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
|
Re: How do I explain Indexes?
Quote:
Originally posted by drmiller100
My co-worker is convinced that regardless of the existence of the index, the engine must do a full scan of the entire table.
He states that the index will make the full scan faster, but the full scan must take place.
|
This is priceless, isn't it! So (according to your co-worker) the index doesn't affect the way the query is performed, it just somehow "encourages" the DBMS to full scan at a faster rate!
This link is to Oracle documentation, but it explains how a b-tree index is searched pretty well. While there may be differences in SQL Server, the underlying principles will be similar.
|
|
03-05-04, 09:48
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
Gee, does anybody have one of those "encouraging" sticks ??? I could certainly use one for some of these blasted queries.
-PatP
|
|
03-05-04, 09:56
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
|
Re: How do I explain Indexes?
Quote:
Originally posted by andrewst
This link is to Oracle documentation, but it explains how a b-tree index is searched pretty well. While there may be differences in SQL Server, the underlying principles will be similar.
|
What link? This one!
|
|
03-05-04, 10:00
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
Ah-ha! Now we have the missing link! Now, where's a sasquatch when you need one ???
-PatP
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How do I explain Indexes?
