February 28, 2012
I do not always get as much benefit from the books that I read as the books’ authors probably intended, although the contents of books, whether right or wrong, sometimes help me remember nearly forgotten facts. Some of the books, for instance “Pro Oracle SQL” describe how to use b*tree indexes to locate rows with NULL values in a column. The book “Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition” also shows several techniques for utilizing b*tree indexes to locate rows with NULL values in a column. Richard Foote’s blog also has at least one article that describes how to use b*tree indexes to locate NULL values in a column.
So, what headed me down the path of NULL Values are Not Stored in Indexes? I read the following sentence in the book “Oracle Database 11gR2 Performance Tuning Cookbook” on page 177:
“NULL values are not stored in indexes, so when we query for records with a NULL value in field X, even if the X column is indexed, the index will not be used.”
I can’t help but feel that I have seen very similar statements in the past. The Oracle Database documentation, at least from 8.1 through 11.1 included the following quote:
“The query must be guaranteed not to need any NULL values from the indexed expression, because NULL values are not stored in indexes.”
But where else have I seen that message? A Google search for: oracle NULL values are not stored in indexes found a couple of places where the message is repeated.
“Oracle Database 11g: The Complete Reference” (I read the 9i version of this book years ago):
“NULL values are not stored in indexes. Therefore, the following query will not use an index; there is no way the index could help to resolve the query:”
select Title from BOOKSHELF where CategoryName is null;
“Oracle 9i Performance Tuning Tips & Techniques” page 39 (I quickly paged through the 10g book in a book store once, and read the sample chapter on Statspack reports):
“Using IS NULL or IS NOT NULL will also suppress index use because the value of NULL is undefined.”
“Oracle Data Warehouse Tuning for 10g” page 51 (I have not had the opportunity to read this book):
“For a BTree index, NULL values are not included in the BTree structure and, thus, not even accessible through the index.”
“Expert Indexing in Oracle Database 11g” page 159 (any problems here – I think that I have a test case somewhere that suggests that the second column should be a number, I thought about buying this book):
“If all index columns are NULL, Oracle Database doesn’t include rows into an index. However, you can actually index NULL values by simply adding another column to the index, like so:
SQL> create index with_null on employees(nullable_column, '1');
—
So, what is my review comment for the Cookbook?
The book states, “NULL values are not stored in indexes, so when we query for records with a NULL value in field X, even if the X column is indexed, the index will not be used.” The book’s description is incomplete. NULL values are not stored in single column b*tree indexes. There are at least four methods to work around this issue and allow indexes to be used to identify rows with a NULL value in the indexed column:
- Define a composite index with at least one other column that has a NOT NULL constraint – ideally, the column in which the NULL values might appear would be the leading column in the composite index.
- Define a composite index with a numeric constant (such as 1) as the second column in the composite index.
- Bitmap indexes always store NULL values – if appropriate (column experiences few updates, deletes, inserts, and an Enterprise Edition database), create a bitmap index for the column.
- If the number of NULL values in a column will be relatively small (compared to the number of rows in the table), and the original SQL statement may be modified, create a function based index that converts NULL values to 1 and non-NULL values to NULL:
- DECODE(C3,NULL,1)
- (CASE WHEN C3 IS NULL THEN 1 ELSE NULL END)
- (NVL2(C3,NULL,1))
Let’s build a little test case to demonstrate. First, a table is created with 1,000,000 rows, two indexes are created, and then statistics are gathered with histograms generated for all indexed columns:
CREATE TABLE T2 ( C1 NUMBER NOT NULL, C2 VARCHAR2(15) NOT NULL, C3 DATE, C4 VARCHAR2(255)); INSERT INTO T2 SELECT ROWNUM C1, RPAD(CHR(MOD(ROWNUM-1,26)+65),15,'0') C2, DECODE(MOD(ROWNUM,100),0,NULL,SYSDATE+ROWNUM/10000) C3, RPAD('A',255,'A') C4 FROM DUAL CONNECT BY LEVEL<=1000000; COMMIT; CREATE INDEX IND_T2_C3_C2 ON T2(C3,C2); CREATE INDEX IND_T2_C3_C ON T2(C3,1); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254')
In the above table, 1% of the rows will have a NULL value in column C3.
Let’s try the first test to see if one of the above two indexes may be used to locate the NULL values in column C3:
SELECT C1, C2, C3 FROM T2 WHERE C2='D00000000000000' AND C3 IS NULL; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID 3sjqqpjradbgz, child number 0 ------------------------------------- SELECT C1, C2, C3 FROM T2 WHERE C2='D00000000000000' AND C3 IS NULL Plan hash value: 895813321 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 389 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 385 | 14245 | 389 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_T2_C3_C2 | 385 | | 4 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C3" IS NULL AND "C2"='D00000000000000') filter("C2"='D00000000000000')
Well, it appears that the composite index on columns C3 and C2 might have helped quickly locate the rows with NULL values in column C3 (we just tested point #1 above). Let’s drop that index and try again:
DROP INDEX IND_T2_C3_C2; SELECT C1, C2, C3 FROM T2 WHERE C2='D00000000000000' AND C3 IS NULL; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID 3sjqqpjradbgz, child number 0 ------------------------------------- SELECT C1, C2, C3 FROM T2 WHERE C2='D00000000000000' AND C3 IS NULL Plan hash value: 1053304445 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 550 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 385 | 14245 | 550 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_T2_C3_C | 10000 | | 33 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='D00000000000000') 2 - access("C3" IS NULL)
This time we cannot simply state that “NULL Values are Not Stored in Indexes”. The Predicate Information section of the execution plan shows access(“C3” IS NULL) (we just tested point #2 above).
Let’s drop the index and try something else:
DROP INDEX IND_T2_C3_C; CREATE BITMAP INDEX IND_T2_C3_BIN ON T2(C3); SELECT C1, C2, C3 FROM T2 WHERE C2='D00000000000000' AND C3 IS NULL; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID 3sjqqpjradbgz, child number 0 ------------------------------------- SELECT C1, C2, C3 FROM T2 WHERE C2='D00000000000000' AND C3 IS NULL Plan hash value: 1153509852 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1954 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID | T2 | 385 | 14245 | 1954 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | IND_T2_C3_BIN | | | | | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='D00000000000000') 3 - access("C3" IS NULL)
Once again, we found that we cannot simply state that “NULL Values are Not Stored in Indexes”. The Predicate Information section of the execution plan shows access(“C3” IS NULL) (we just tested point #3 above).
Let’s drop the index and try just indexing the NULLs (sub-point 1 of point #4 above):
DROP INDEX IND_T2_C3_BIN; CREATE INDEX IND_T2_C3_FN ON T2 DECODE(C3,NULL,1); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE 254 FOR ALL INDEXED COLUMNS SIZE 254',NO_INVALIDATE=>FALSE) SELECT /*+ INDEX(T2) */ C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND DECODE(C3,NULL,1)=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID 8psj7gcwcn72m, child number 0 ------------------------------------- SELECT /*+ INDEX(T2) */ C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND DECODE(C3,NULL,1)=1 Plan hash value: 2303772603 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3752 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 385 | 14245 | 3752 (1)| 00:00:01 | |* 2 | INDEX FULL SCAN | IND_T2_C3_FN | 10000 | | 3234 (2)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='D00000000000000') 2 - filter(DECODE(INTERNAL_FUNCTION("C3"),NULL,1)=1)
The index that we created was used. Once again, we found that we cannot simply state that “NULL Values are Not Stored in Indexes”.
Let’s drop the index and try just indexing the NULLs (sub-point 2 of point #4 above):
DROP INDEX IND_T2_C3_FN; CREATE INDEX IND_T2_C3_FN ON T2 (CASE WHEN C3 IS NULL THEN 1 ELSE NULL END); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE 254 FOR ALL INDEXED COLUMNS SIZE 254',NO_INVALIDATE=>FALSE) SELECT /*+ INDEX(T2) */ C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND (CASE WHEN C3 IS NULL THEN 1 ELSE NULL END)=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID 2w9h5jm4tdzpz, child number 0 ------------------------------------- SELECT /*+ INDEX(T2) */ C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND (CASE WHEN C3 IS NULL THEN 1 ELSE NULL END)=1 Plan hash value: 941108248 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 10024 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 385 | 15015 | 10024 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_T2_C3_FN | 10000 | | 20 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='D00000000000000') 2 - access("T2"."SYS_NC00005$"=1)
The index that we created was used, although notice that the Predicate Information section of the plan is a bit different from before, and this time we have an INDEX RANGE SCAN operation rather than an INDEX FULL SCAN operation. Once again, we found that we cannot simply state that “NULL Values are Not Stored in Indexes”.
Let’s drop the index and try just indexing the NULLs (sub-point 3 of point #4 above):
DROP INDEX IND_T2_C3_FN; CREATE INDEX IND_T2_C3_FN ON T2 NVL2(C3,NULL,1); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE 254 FOR ALL INDEXED COLUMNS SIZE 254',NO_INVALIDATE=>FALSE) SELECT C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND NVL2(C3,NULL,1)=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID fmsg8vztz32d2, child number 0 ------------------------------------- SELECT C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND NVL2(C3,NULL,1)=1 Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3282 (100)| | |* 1 | TABLE ACCESS FULL| T2 | 385 | 14245 | 3282 (1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("C2"='D00000000000000' AND NVL2("C3",NULL,1)=1))
The index that we created was… not used? But is it not the case that the NVL2(C3,NULL,1) function result is the equivalent of the DECODE and the CASE function results? Let’s give this one another try with a hint:
SELECT /*+ INDEX(T2) */ C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND NVL2(C3,NULL,1)=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); SQL_ID f510adk7fqwyu, child number 0 ------------------------------------- SELECT /*+ INDEX(T2) */ C1, C2, C3, NVL2(C3,NULL,1) C4 FROM T2 WHERE C2='D00000000000000' AND NVL2(C3,NULL,1)=1 Plan hash value: 2303772603 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3740 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 385 | 14245 | 3740 (1)| 00:00:01 | |* 2 | INDEX FULL SCAN | IND_T2_C3_FN | 10000 | | 3223 (1)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='D00000000000000') 2 - filter(NVL2("C3",NULL,1)=1)
That’s better, even if we did receive an INDEX FULL SCAN operation rather than in INDEX RANGE SCAN operation as was the intended result.
Something fun to think about – why did the technique using the CASE syntax that resulted in an INDEX RANGE SCAN operation have a plan with a calculated cost of 10,024, when the plans with the INDEX FULL SCAN operations have a calculated cost of about 3,750?
So what is your statement about null values and indexes?
Does it have maybe influence on db version?
I think that Yasser may have already answered your question, but then I may be misunderstanding your question.
In short, indexes may be used to locate rows that contain NULL values in a table column. NULLs may be stored in indexes. Whether or not a specific index should be created or adapted specifically to locate NULL values will depend on the importance of query performance, compared to the use of additional disk space for the index and index maintenance delays during inserts, updates, and deletes of the column data as additional redo and undo is created and the index structure grows.
Method 1 uses a standard composite b*tree index. Method 2 creates a function based composite index with the second column containing a numeric constant (I think that the Enterprise Edition of Oracle Database 8i was the first version that supported function based indexes, and that feature was extended to the Standard Edition in 9i). Method 3 creates a bitmap index – that requires the Enterprise Edition. Method 4 creates a function based index that indexes only cases where a column value is null.
I think up-front statement “NULL values are not stored in indexes” in renowned Oracle book, with author having idea behind the scenes to avoid complexity can sometimes cause reader to feel uncomfortable, but i think we should be thankful to author for publishing the scenarios and making our brains to think in different views and avoid “Compulsive Tuning Disorders”
To me this statement “NULL values are not stored in indexes, so when we query for records with a NULL value in field X, even if the X column is indexed, the index will not be used.” seems to be WRONG and incompletely framed ‘sentence’.
-Yasser
Charles,
Once again, nice demonstration and good question.
why did the technique using the CASE syntax that resulted in an INDEX RANGE SCAN operation have a plan with a calculated cost of 10,024, when the plans with the INDEX FULL SCAN operations have a calculated cost of about 3,750?
I must admit I don’t know the answer but my best guess is the costing has something to do with the way index is being used (access vs. filter). In case of INDEX RANGE SCAN, while the index step has ACCESS predicate (and a low cost of 20) whereas in case of INDEX FULL SCAN, the index step has FILTER predicate (and significantly higher cost of 3234). Till this date, am not quite clear about the difference in ACCESS and FILTER predicates.
Narendra,
I am not sure that I have a nice, short description of access and filter predicates. You might think of it this way, if you think about the index in the front of a book:
Access Predicate: You know that the topic of interest is found in chapter 8, under a heading of “Examining Execution Plans and Plan Statistics” which is in a section of the book from pages 219 through 226 (or 227). You then flip to pages 219 through 226 and start reading.
Filter Predicate: As you are reading through the section, you are discarding sentences that do not contain the exact information that is of interest, and remembering the information that is of interest. (If the index were more detailed, but the specific item of interest was not detailed – you did not know to look specifically under the heading “Examining Execution Plans and Plan Statistics”, then part of this filtering might have taken place while examining the index).
This is the description that is provided on page 222 of the book “Expert Oracle Practices”:
From the Oracle Database documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm
Description by Karen Morton:
https://method-r.fogbugz.com/default.asp?method-r.11.552.2
Description from the book “Cost Based Oracle Fundamentals”:
http://books.google.com/books?id=TGSd3pkMx5IC&pg=PA383#v=onepage&q&f=false
Description from the book “Pro Oracle SQL”:
http://books.google.com/books?id=nrwu-f4JwjcC&pg=PA164#v=onepage&q&f=false
Description from the book “Troubleshooting Oracle Performance”:
http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA211#v=onepage&q&f=false
Charles,
With all due respect, I don’t think your analogy explains my issue and neither does the description in the links that you have provided. Your analogy is in-line with what happens in the case of INDEX RANGE SCAN example above. Essentially, I understand that, typically, an ACCESS PREDICATE is applied to the index access step in the plan (as you have described with book index analogy) and the FILTER PREDICATE is applied to the table access step in the plan (as you described in “As you are reading through the section…”).
However, I had pointed out the difference in the way predicates are reported for index access only. In your examples, with DECODE index the plan reports the predicate for index access step as filter predicate (with much higher cost) as compared to the access predicate (with much lower cost) reported when a CASE based index is used.
So far, the only resource that comes close to this is http://jonathanlewis.wordpress.com/2010/08/31/filter-bug/
Would love to know if there are any better or more detailed explanations available.
BTW, apologies for diverting the discussions away from your main question (which, as I said, I don’t know the answer for)
I might not be answering your question yet. However, did you notice that the CASE example that showed an INDEX RANGE SCAN had the following in the Predicate Information section access(“T2”.”SYS_NC00005$”=1) – SYS_NC00005$ is a hidden/virtual column that was automatically generated when the function based index was built:
So, why did we not see the same virtual columns with the NVL2(C3,NULL,1) function. I could say that this test result was intentional, or I could say that I accidentally omitted the () around the NVL function when I created the index. Take a look at the output of the following script that first incorrectly formats the index specification, and then correctly formats the index specification:
Notice now that the hidden/virtual column appears in the second execution plan.
The hidden/virtual column was created.
Now, let’s fix the DECODE test case:
The output:
No index access in the second execution plan, even though an index access path was hinted. So, what happened in the second execution plan? Let’s take a look at the hidden/virtual columns:
The hidden column exists. Maybe the index was never created? Let’s check:
Yes, the index is there. Maybe the Filter Predicate in the execution plan provides a clue DECODE(INTERNAL_FUNCTION(“C3”),NULL,1)=1.
The new challenge then, why was a full table scan selected?
I guess the answer lies in USER_IND_EXPRESSIONS (or ALL). I remember quite some time back reading about why a function-based index was not being used on AskTom site and Tom pointed out that the function-based index can be used provided the predicate matches the expression in those views for the index. Not sure what version was that and can not find the link to it. In that thread, Tom had explained how the ALL_IND_EXPRESSIONS store the expression in different way that the one specified while creating the index (in some particular case). Not sure it is still valid.
Here is my test on a 10.2.0.5 db (Can’t remember how to format as code). But you can see the difference when, as you said, brackets are used or not.
Charles,
Apologies but the INTERNAL_FUNCTION appears because C3 is DATE datatype and it is being compared to NULL (which, by default, is VARCHAR2, I think). Hence the conversion for data type compatibility.
Narendra,
Well done. Another case where a single test case is insufficient, especially if an intentional (or unintentional) mistake is made in the test case 🙂
Well, that is not what I requested – the problem extends beyond problems with CURSOR_SHARING=FORCE – NLS settings could also be an issue.
Back to the original test, and a fix.
Results:
Charles,
Guess what? I don’t get table scan when using DECODE. My results are same as that using NVL (again, apologies for not formatting)
Charles,
Back to the original test, and a fix.
Nice one. What do you get if you convert NULL and not C3 ? 🙂
Proved this almost four years ago on my own blog:
http://oratips-ddf.blogspot.com/2008/04/tale-of-two-indexes.html
David,
Thank you for posting a link to your article – always good to see links to articles that demonstrate helpful information. You certainly did capture all of the options.
The first time I read the article, my eyes became a bit blurry… why would Oracle’s optimizer select to use an INDEX FULL SCAN operation, which reads one block at a time (from disk), when it can just about as quickly read 8, 16, or 128 8KB blocks at a time (from disk) during a FULL TABLE SCAN operation. Then it hit me on the second read through… your test table only had 10 very narrow rows – the indexes probably contained only an index root block.
I extended your test case to 1,000,000 rows, with each row being much wider (column C_2 is padded to 255 characters) (tested on 11.2.0.2, I am explaining the steps for the other people who may read this comment, not necessarily for David):
Will the optimizer select to use the TEST1_C_2 index if c_2 IS NOT NULL is specified in the WHERE clause:
Well, in this case a FULL TABLE SCAN operation was selected with a calculated cost of 13,143.
Let’s force an index access path:
An INDEX FULL SCAN operation was used, but it was not automatically selected because the 54,890 cost is greater than the 13,143 cost for the FULL TABLE SCAN operation.
Let’s try again with a narrower version of the table, with column C_2 restricted to 10 characters:
Repeating the test with C_2 IS NOT NULL specified in the WHERE clause:
A full table scan again, this time with a calculated cost of 1,239.
Let’s force an index access path:
The INDEX FULL SCAN operation had a calculated cost of 5,472, which is more than the 1,239 for the FULL TABLE SCAN operation, so that is why the full table scan was selected.
Let’s check selecting column C_1 to see if we also experience a FULL TABLE SCAN for this query:
No full table scan, but we did receive an INDEX FAST FULL SCAN operation, which performs multi-block reads (from disk) of the index blocks, much like what would happen with the table blocks during a FULL TABLE SCAN operation.
Let’s see what happens if we try to force an index access path using a hint for the same query:
Notice that the cost increased dramatically, and that the execution plan shows an INDEX FULL SCAN operation rather than an INDEX FAST FULL SCAN operation.
For fun, let’s check the calculated cost of a FULL TABLE SCAN so that we are able to compare the cost with the INDEX FULL SCAN operation:
The cost of the FULL TABLE SCAN is less than that of the INDEX FULL SCAN operation – so if the INDEX FAST FULL SCAN operation was not legal, the FULL TABLE SCAN operation would have been selected.
Maybe it is a problem with skewed system statistics?
The MBRC is just 6, so the optimizer definitely was not under-costing the FULL TABLE SCAN (and INDEX FAST FULL SCAN) operation.
Let’s bump the MBRC to 16 just to see what happens:
The INDEX FAST FULL SCAN operation’s cost dropped from 921 to 457.
So, what would happen if an INDEX FAST FULL SCAN operation were not valid for this SQL statement?:
A full table scan with a cost of 585.
Let’s switch back to an INDEX FULL SCAN operation:
The calculated cost is unchanged after the change of the MBRC system statistic.
—
David,
Would you be able to re-run your test with Oracle Database 11.2.0.1, 11.2.0.2, or 11.2.0.3 with a larger number of rows? I am curious to know if you will see INDEX FAST FULL SCAN operations, INDEX FULL SCAN operations, or TABLE ACCESS FULL operations in the execution plans.
Oracle doc:
http://docs.oracle.com/cd/B12037_01/server.101/b10736/indexes.htm
“Unlike most other types of indexes, bitmap indexes include rows that have NULL values. ”
And now what?
🙂
Rg,
Damir
[…] https://hoopercharles.wordpress.com/2012/02/28/repeat-after-me-null-values-are-not-stored-in-indexes/ […]
[…] by posts from Charles Hooper and Richard Foote. Share this:FacebookTwitter Posted by Chris Saxon at 08:00 Tagged […]