Results 1 to 11 of 11
  1. #1
    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!!!!

  2. #2
    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.

  3. #3
    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!!!!!!!!!!

  4. #4
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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 (notesVALUES ('One')
    INSERT INTO dbo.inddemo (notesVALUES ('Two')
    INSERT INTO dbo.inddemo (notesVALUES ('Three')
    INSERT INTO dbo.inddemo (notesVALUES ('Four')
    INSERT INTO dbo.inddemo (notesVALUES ('Five')
    INSERT INTO dbo.inddemo (notesVALUES ('Six')
    INSERT INTO dbo.inddemo (notesVALUES ('Seven')
    INSERT INTO dbo.inddemo (notesVALUES ('Eight')
    INSERT INTO dbo.inddemo (notesVALUES ('Nine')
    INSERT INTO dbo.inddemo (notesVALUES ('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

  6. #6
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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 
    @10000

    WHILE < @i
       BEGIN
          INSERT dbo
    .inddemo2 (whateverVALUES ('Whatever it takes to make a point')
          
    SELECT @= @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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: How do I explain Indexes?

    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.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Gee, does anybody have one of those "encouraging" sticks ??? I could certainly use one for some of these blasted queries.

    -PatP

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: How do I explain Indexes?

    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!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Ah-ha! Now we have the missing link! Now, where's a sasquatch when you need one ???

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •