"Go" Oracle index column NULL value raises execution plan test sample for this table

Source: Internet
Author: User
Tags create index

Sometimes the development of the table structure design, is too random to table field is empty, such as ID1=ID2, if the Allowed field is empty, because the Oracle hollow value is not equal to null value, it is possible to get unexpected results. Beyond that, the key is that null affects Oracle's execution plan.

The following null affects the test example for the execution plan.

/*1. Build test table, where create table is constructed with the test sheet structure object_id non-empty *, go index/

SELECT Count (*) from all_objects WHERE object_id are not NULL; --41790 Pen
DROP TABLE test;
CREATE TABLE Test as SELECT * from All_objects WHERE object_id are not NULL; ----41791 Pen
CREATE INDEX idx_test on test (object_id);
ANALYZE table test Compute STATISTICS for TABLE for all indexes for all indexed COLUMNS;
EXPLAIN PLAN for SELECT Count (*) from test;
SELECT * from TABLE (dbms_xplan.display);

Plan Hash value:3508397080

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |          SELECT STATEMENT |     |    1 | 23 (5) | 00:00:01 |
|  1 |          SORT AGGREGATE |     |            1 |          | |
| 2 |INDEX FAST Full scan| Idx_test |    41791 | 23 (5) | 00:00:01 |
--------------------------------------------------------------------------

/*2. Change the structure of the test table, make the object_id field null, and update a data for null*, go to the full table/

ALTER TABLE Test MODIFY object_id number NULL;
UPDATE test SET object_id=null WHERE rownum=1;
COMMIT;
EXPLAIN PLAN for SELECT Count (*) from test;
SELECT * from TABLE (dbms_xplan.display);
Plan Hash value:1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU) | Time |
-------------------------------------------------------------------
| 0 |      SELECT STATEMENT |     |   1 | 135 (2) | 00:00:02 |
|  1 |      SORT AGGREGATE |     |            1 |          | |
| 2 |TABLE ACCESS full| TEST |   41791 | 135 (2) | 00:00:02 |
-------------------------------------------------------------------

/*3. Adding conditional filters to SQL instructions NULL data *, GO index/

EXPLAIN PLAN for SELECT Count (*) from Test WHERE object_id are not NULL;
SELECT * from TABLE (dbms_xplan.display);
Plan Hash value:3508397080

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------
| 0 |          SELECT STATEMENT |     |     1 |    4 | 23 (5) | 00:00:01 |
|  1 |          SORT AGGREGATE |     |     1 |            4 |          | |
|* 2 |INDEX FAST Full scan| Idx_test |   41791 |    163k| 23 (5) | 00:00:01 |
----------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter ("object_id" is not NULL)

/*4. Delete the object_id is null data from the above changes, then view plan, still walk the whole table */

DELETE from Test WHERE object_id is NULL;
COMMIT;
ANALYZE table test Compute STATISTICS for TABLE for all indexes for all indexed COLUMNS;
EXPLAIN PLAN for SELECT Count (*) from test;
SELECT * from TABLE (dbms_xplan.display);

Plan Hash value:1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU) | Time |
-------------------------------------------------------------------
| 0 |      SELECT STATEMENT |     |   1 | 135 (2) | 00:00:02 |
|  1 |      SORT AGGREGATE |     |            1 |          | |
| 2 |TABLE ACCESS full| TEST |   41790 | 135 (2) | 00:00:02 |
-------------------------------------------------------------------

In conclusion, it seems that if the table structure of the index column is non-empty, the index will go, if the index list structure is empty, then the whole table will be gone regardless of the existence of NULL data, or the index can be filtered by the where index column is not NULL to filter the empty data.

Transferred from: http://www.linuxidc.com/Linux/2012-09/69938.htm

"Go" Oracle index column NULL value raises execution plan test sample for this table

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.