0

We're running an Oracle 11.2.0.3 database and have a table with above 10 columns in it. There is a non-unique index over all these columns.

For simplicity in this example, lets assume the columns are called "COLUMN1, COLUMN2, etc. and the index is built on these columns in the same order.

The table contains 120 million entries. Every entry has the exact same value "0" for COLUMN1 and COLUMN2. The other columns contain different values.

Introductory Question...
Based on this, should the index basically be structured such that COLUMN 1 and COLUMN2 would only contain single branches each, and then COLUMN3+ contain multiple branches based on their differing values. ie something like this...

     ROOT NODE
         |
        "0"             (for COLUMN 1)
         |
        "0"             (for COLUMN 2)
    /``/`|`\``\
  /`  /  |  \  `\
"1" "2" "3" "4" "5"...  (for COLUMN 3)
        ...             (etc for COLUMN4+)

This kind of structure would seem logical, as COLUMN1 and COLUMN2 only contain the single value for all entries, so it shouldn't require more than a single branch for each of these columns.

Following on from this, and leading on to the main question...

We have a query similar to the following...

SELECT *
  FROM MYTABLE 
 WHERE COLUMN3 IN ( "5", "6", "7" )
   AND COLUMN4 IN ( "A", "B", "C" );

As you would expect, the Explain Plan shows that this query uses an INDEX SKIP SCAN on the index, as COLUMN1 and COLUMN2 aren't in the WHERE clause.

To a human, we can see that because COLUMN1 and COLUMN2 have a consistent value, Oracle should potentially be able to execute this query as a normal range scan. However, Oracle doesn't have human insight in this way.

The Main Question...
How different is an INDEX SKIP SCAN going to perform in this example compared to a normal range scan? My assumption is that the processing involved for an INDEX SKIP SCAN is going to be significantly different to the processing for a normal range scan, so the performance would be significantly worse even though the first 2 columns have a consistent value. Is this correct?

Is anyone able to provide some information about the Oracle implementation of the different index lookups. I'm more interested to know whether Oracle is smart enough to effectively ignore the first 2 columns when performing the SKIP SCAN, and thus run relatively efficiently, or whether it is still going to have a lot of overhead because the SKIP SCAN implementation processes the index in a different way to a normal range lookup.

Thanks

  • Why don't you just try the query with the extra where terms? Why are these columns in your index at all? – Mat Jun 5 '14 at 9:02
  • Its the standard functionality provided to us by the software vendor - I can only assume the table is 'generic' so it suits many customers, but for us it just so happens we don't need to fill the first 2 columns. I was more interested in the implementation behind the way Oracle performs the different index lookups, to help explain whether Oracle is smart enough to handle it efficiently, or whether it requires human intervention. – wattostudios Jun 5 '14 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.