4 Replies Latest reply on Mar 27, 2011 6:36 PM by 613746

    Oracle covering indexes - equivalent to SQL Server INCLUDE indexes ?

    Andy Mackie
      For covering indexes in Oracle, does it have anything similar to SQL Server's INCLUDE indexes ?

      These allow you to define the index key on just the columns used for selection, keeping the index key narrow, but add the other columns needed to cover the query into the leaf level of the index.
      CREATE NONCLUSTERED INDEX NC_EmpDep ON Employee(EmployeeID, DepartmentID) INCLUDE (Lastname)

      Is there anything similar in Oracle ? Or do you have to define the index on all the columns to get a covering index in Oracle ?
      CREATE INDEX NC_EmpDep ON Employee(EmployeeID, DepartmentID, Lastname)

      That would cover the query, but the index key is now bigger than it needs to be.
        • 1. Re: Oracle covering indexes - equivalent to SQL Server INCLUDE indexes ?
          Not familiar enough with your terminology to say, but I'll say anyways, sounds like an [url http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#CNCPT88854]index-organized table or perhaps a [url http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/schemaob.htm#sthref536]materialized view.

          If you mean for something that is immediately and automatically updated as your table is updated, I think the answer is "it has to be bigger." But then again, Oracle has an optimizer, buffering and access methods that may make it moot - since Oracle doesn't have the blocking and concurrency issues of other database engines, it may not matter so much that we don't need to have a whole 'nuther structure taking up even more room, as opposed to structures already in memory or that can be read from disk with larger buffering than usual.

          It depends what you are doing.
          • 2. Re: Oracle covering indexes - equivalent to SQL Server INCLUDE indexes ?
            Andy Mackie
            Oracle terminology seems to be an "index only query". It's to let a query retrieve it's results based solely on an index, rather than having to do lots of lookups to the table to get additional columns. I can't see a direct equivalent in Oracle of a SQL Server Include index. It's not some "nuther structure", it's just an index.

            The MSSQL include index seems structurally similar to an index organized table. However Oracle doesn't let you declare an IOT as an index on another table, if I understand it correctly. A materialized view may be a way to achieve similar functionality, if it is declared as "refresh fast on commit", so it updates immediately. It may also need "enable query rewrite" to allow queries to use the MV without having to explicitly mention the view by name in the query.

            As for blocking and concurrency, that's a different issue, but SQL Server 2005 introduced row-versionining similar to Oracle, so readers/writers never block each other. ("read committed snapshot isolation" and "snapshot isolation", similar to read committed and serializable on oracle).

            So I think the short answer is no, Oracle doesn't have an equivalent to SQL Server's included indexes, but there may be other ways to achieve the same goal.
            (unless someone can tell me otherwise!)
            • 3. Re: Oracle covering indexes - equivalent to SQL Server INCLUDE indexes ?
              Satish Kandi
              SQL Server has a totally different internal structure than Oracle has. In SQL Server, a table is basically a big linked-list and the data blocks are essentially the leaf-blocks of the cluster index. Those blocks are then doubly-linked back and forth so you can traverse the table in a full table scan or in an index range scan. In fact, an index range scan of the whole table is essentially (physically) the same as a full table scan.

              Thus, it seems ( I could be wrong in extending this ) that SQL Server benefits from INCLUDing the extra column with the index itself (which avoids the effective table scan).

              With Oracle, indexes are pointers to actual data blocks and thus, rows can be retrieved directly from blocks. Hence, no INCLUDE equivalent.
              • 4. Re: Oracle covering indexes - equivalent to SQL Server INCLUDE indexes ?
                To my best current knowledge of SQL Server.

                INCLUDING postfixes columns to the index WITHOUT considering them for the index order. That means that UPDATEs can be done faster if only those INCLUDED columns are affected. But that's more or less a theory of mine.

                CLUSTERING INDEX is like an Oracle IOT. And my theory continues that it is effectively like a non clustered index that INCLUDES all the remaining columns of the table. So, I BELIEVE it does not do the index delete/insert for UPDATES that affect the non key columns only.

                It is not required to have a CLUSTERING INDEX in SQL Server. In that case, there will be a heap table like Oracle has them.

                AFAIK there is not way in Oracle to include additional columns to the index without considering them in the index order.

                So, the Oracle way of doing the INCLUDING is appending the columns to the index--as you did.

                If my theory is correct, the difference is UPDATE performance on lastname only. Both will need to store (a copy of) lastname in the index itself.