Some Mistakes

Learnt something newly from mistake, that I made while taking Explain plan from production. Here is the piece to demonstrate that.

rajesh@10GR2> create table t
  2  nologging
  3  as
  4  select * from all_objects;

Table created.

Elapsed: 00:00:02.39
rajesh@10GR2>
rajesh@10GR2> alter table t add constraint pk_t primary key(object_id);

Table altered.

Elapsed: 00:00:00.46
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.71
rajesh@10GR2>
rajesh@10GR2> desc t;

 Name                Null?    Type
-------------------- -------- -------------------------
OWNER               NOT NULL VARCHAR2(30)
OBJECT_NAME         NOT NULL VARCHAR2(30)
SUBOBJECT_NAME          VARCHAR2(30)
OBJECT_ID           NOT NULL NUMBER

 ............................

Now, I have Table 'T' having primary key on 'object_id' column and 'object_name' is NOT NULL.

rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2> select object_name from t;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 56749 |  1330K|   179   (1)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| T    | 56749 |  1330K|   179   (1)| 00:00:03 |
--------------------------------------------------------------------------
rajesh@10GR2>

Now this Full Table Scan is fine, since we need 'object_name'  from table 'T', So scanning Index doesn't make sense here.

rajesh@10GR2>
rajesh@10GR2> select count(*) from (select object_name from t);

Now i thought this query should use the same Full Table Scan and count to return its results (that is my mistake). but looking at the Explain plan it is.

rajesh@10GR2> select count(*) from (select object_name from t);

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 949213647
----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    29   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_T | 56749 |    29   (4)| 00:00:01 |
----------------------------------------------------------------------

since the object_name column is NOT NULL (this ensure that each value present in 'object_name' will have an equivalent NOT NULL value in PK index) ,so Oracle realised that full scanning the PK index is very efficient than  Full Table Scan followed by a count.

How ever if i make 'object_name' column as NULLABLE column, Oracle will full scan Table to answer the queries, since B*Tree index created by PK will not hold NULL values. ( since CBO feels that some NULLABLE values exists in 'object_name' column that doesnot exists in B*Tree index  )

rajesh@10GR2> alter table t modify object_name null;

Table altered.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> select count(nm) from (select object_name nm from t);

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    24 |   179   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 56749 |  1330K|   179   (1)| 00:00:03 |
---------------------------------------------------------------------------

rajesh@10GR2>

So, at the end of the day I learnt something new about Oracle database CBO.