Shulou(Shulou.com)06/01 Report--

The use of no_unnest hint allows the execution plan to generate filter, that is, no expansion, but in general, using unnest hint does not eliminate filter.

Find out the non-unique index in the library by following SQL, and you might write SQL like this:

SELECT SEGMENT_NAME,SUM (BYTES/1024/1024) m FROM DBA_SEGMENTS WHERE SEGMENT_NAME NOT IN (select index_name from dba_indexes where UNIQUENESS = 'NONUNIQUE') GROUP BY SEGMENT_NAME

It takes a minute. Why is it so slow? When there are not in, not exists, in, and exists after the SQL statement where subquery, CBO will try to unnest the subquery to eliminate filter, but the above example CBO does not do so, let's take a look at the execution plan.

Let's take a look at the execution plan for adding unnest hint to the subquery:

SELECT SEGMENT_NAME,SUM (BYTES/1024/1024) m FROM DBA_SEGMENTS WHERESEGMENT_NAME NOT IN (select / * + UNNEST * / index_name from dba_indexeswhere UNIQUENESS = 'NONUNIQUE') GROUP BY SEGMENT_NAME

Filter is eliminated and CBO reassembles the base table based on the underlying data dictionary so that the execution plan becomes hash joinanti,0.23 and execution is completed in a second.

Let's take a look at an example of optimizing subquery unnesting through with as materialize.

SQL is as follows:

Select AREA_NAME,sum (reve) from t_order o where exists (select AREA_ID from t_customer c where nation = 'Aus' and c.AREA_ID = o.AREA_ID union select AREA_ID from f_customer f where nation =' US' and f.AREA_ID o.f_area_id) group by AREA_NAME;selectsum (bytes/1024/1024) M from dba_segments where segment_name = 'Flying customer' M192

Table F_CUSTOMER192M

The implementation plan is as follows:

We see that it takes nearly 2 hours to complete without going through the index. by setting up a composite index, let it go index_ffs.

CREATE INDEX IDX_FFS_NATION_ID ONf_customer (AREA_ID,nation,0)

If the execution is completed in 8 minutes, is there a better way?

As follows:

With x as (select / * + materialize * / AREA_ID from f_customer f where nation = 'US') select AREA_NAME,sum (reve) from t_order o where exists (select AREA_ID from t_customer c where nation =' Aus' and c.AREA_ID = o.AREA_ID union select AREA_ID from x where x.AREA_ID o.f_area_id) group by AREA_NAME

In FILTER, the SQL statement after NOT IN (NOT EXISTS) is executed many times, and the amount of data is already very large, and the result can be imagined. However, using HINT MATERIALIZE in conjunction with WITH AS to materialize some of the columns in the table, a view-based temporary table is created during execution. In this way, NOT EXISTS does not perform a scan of a large data table or a fast scan of a large index every time, and the larger the data of the table, the wider the table, the more obvious the optimization effect.

| | author profile |

Yao Chong Walk Technology senior database technology expert is familiar with Oracle, MySQL database internal mechanism, rich Oracle, MySQL fault diagnosis, performance tuning, database backup and recovery, replication, high availability solutions and migration experience.

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.