Re: PARTition elimination

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 18 Nov 2005 23:26:31 +0000
Message-ID: <3bosn1ln4qjt1p591gumb9nirfpg37gm0r@4ax.com>

On Fri, 18 Nov 2005 11:27:05 -0800, DA Morgan <damorgan_at_psoug.org> wrote:

>> (The index is bitmap so it contains null values also , isnt it?)
>
>No Oracle index contains NULLs.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#sthref5176

"Note:
Oracle does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for the index key columns or create a bitmap index."

SQL> create table t (c number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> insert into t values (NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> create bitmap index t_b1 on t (c);

Index created.

SQL> explain plan for select count(*) from t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

Plan hash value: 3600098451

---

| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |      |     3 |     1   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T_B1 |       |            |          |
------------------------------------------------------------------------------

Note

---

• dynamic sampling used for this statement

14 rows selected.

SQL> explain plan for select * from t where c is null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

Plan hash value: 2072587430

---

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  BITMAP CONVERSION TO ROWIDS |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   BITMAP INDEX FAST FULL SCAN| T_B1 |       |       |            |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - filter("C" IS NULL)

Note

---

• dynamic sampling used for this statement

18 rows selected.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Fri Nov 18 2005 - 17:26:31 CST