Shulou(Shulou.com)05/31 Report--

This article is about how to optimize is null and is not null in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Recently, when I was working, I encountered a relatively large data query, and my sql had no problem when the amount of data was small, and it was very slow when the amount of data reached 300W. I only optimized sql by myself, which had not been optimized before, so I recorded my own optimization process. This is about the optimization of is null and is not null. The environment used is 0racle11g.

There is a field b in the existing a table. I want to find out the data of the b field is null in the a table.

Select * from a where b is null

I built the index on the b field, but when the conditions are is null and is not null, the execution plan does not walk the index but scans the whole table. At this time, there are 310w records in table a, and it takes about 0.526 seconds to execute this query.

Optimization:

Through functional indexing: change an empty field to a non-empty c value through nvl (bforce c). Make sure that the c value does not appear in the data. And then set up the function index on the function nvl (bmai c).

Select * from a where nvl (bmaine c) = c

The time taken at this time is about 0.01 seconds.

When the condition is is not null, the same theory can be replaced by nvl (bmaine c) c.

Sub-query condition Optimization of Oracle query Optimization

Environment: oracle 11g

The existing a table is associated with the b table through the A01 field. To query the data of the a table, there is no data in the b table. The sql is as follows

Select count (1) from (select A. from b where b.a01=a.a01, (select count (1) from b where b.a01=a.a01) as flag from a) where flag=0

Because flag is a virtual field and cannot go through the index, it takes about 2 seconds for this sql to execute a total of 310W data queries.

Using not exists to optimize sql is as follows

Select count (1) from a where not exists (select 1 from b where a.a01=b.b01)

Using not exists to walk the index, the execution time is about 0.2 seconds.

Thank you for reading! This is the end of the article on "how to optimize is null and is not null in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.