Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Source: Internet
Author: User

Sometimes the development of table structure design, whether the table field is empty is too casual, such as id1 = id2, if the field is allowed to be empty, because the Oracle empty value is not equal to the null value, unexpected results may be obtained. In addition, NULL affects the execution plan of oracle.

The following is a test example where NULL affects the execution plan.

/* 1. Construct the test table. The object_id of the test table created in the create table method is not empty *, and the index/

SELECT Count (*) FROM all_objects WHERE object_id is not null; -- 41790
Drop table test;
Create table test as select * FROM all_objects WHERE object_id is not null; ---- 41791
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, set the object_id field to NULL, and update the data to NULL/

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. Add a condition to the SQL command to filter the NULL data * and use the index/

Explain plan for select Count (*) FROM test WHERE object_id is 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 | 23 (5) | 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

2-filter ("OBJECT_ID" is not null)

/* 4. delete the modified object_id is NULL data, view the plan, and still go to the full 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 summary, it seems that if the table structure of the index column is not empty, the index will be taken; if the index list structure can be empty, the whole table will be taken regardless of whether null data exists; if the where index column is not null can be used to filter empty data, the index will still be used.

Related Article

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.