ORACLE uses with as and hint materialize to optimize SQL to solve low FILTER efficiency and hintmaterialize

ORACLE uses with as and hint materialize to optimize SQL to solve low FILTER efficiency and hintmaterialize
Zookeeper

During project creation, a page uses SQL statements similar to the following to query data, I replaced the table name and field in the project with the system table and field in the ORACLE database.

In my project, a table similar to ALL_TABLES contains more than pieces of data, and the query SQL below is very slow.

WITH PARAMS AS (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL)SELECT AU.USERNAME, AU.USER_ID  FROM ALL_USERS AU INNER JOIN PARAMS PA    ON 1 = 1 INNER JOIN DBA_USERS DU    ON AU.USERNAME = DU.USERNAME WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND       AU.USER_ID = PA.USER_ID) OR              (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND       AU.USERNAME NOT IN       (SELECT AU.USERNAME            FROM ALL_USERS AU           INNER JOIN DBA_USERS DEV              ON AU.USERNAME = DEV.USERNAME           INNER JOIN (SELECT OWNER AS USERNAME                        FROM ALL_TABLES T                       WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA              ON AU.USERNAME = ATA.USERNAME)) OR       (PA.SDATE IS NOT NULL AND       AU.USERNAME IN       (SELECT AU.USERNAME            FROM ALL_USERS AU           INNER JOIN DBA_USERS PA              ON AU.USERNAME = PA.USERNAME           INNER JOIN ALL_TABLES ATA              ON PA.USERNAME = ATA.OWNER           WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND       AU.USER_ID = PA.USER_ID))   AND DU.PROFILE LIKE 'D%'   AND AU.USERNAME LIKE PA.SNAME

In view of the slow execution of the preceding SQL statement, I made the following analysis:

Step 1: Execute the Three OR clauses after the WHERE condition of the statement and the primary query respectively. The execution speed is very fast and the execution speed is very slow.

Step 2: Compare the execution plans of the preceding SQL statements and the Three OR split SQL statements, as shown in. It is found that there is a FILTER in the execution of the preceding SQL statement, and the not exists is used in the FILTER predicate, which is the cause of the slow running of this SQL statement.

If the cause is found, you have to find a way to remove the FILTER of the execution plan. I want to add HINT at first, but it does not work if I have tried a lot of HINT. The final result is the same. Later I thought that with as could improve the SQL query speed. I put the SQL statement that affects SQL Execution in WITH AS, and the result is the same. Later, I triedMATERIALIZE and with as are used in combination and modified to the following SQL statements. The query speed is greatly improved. As shown in, the not exists filter in the execution plan does NOT exist.

WITH PARAMS AS (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL),USERNAMEDATA AS (SELECT /*+ materialize */   AU.USERNAME    FROM ALL_USERS AU   INNER JOIN DBA_USERS DEV      ON AU.USERNAME = DEV.USERNAME   INNER JOIN (SELECT OWNER AS USERNAME                FROM ALL_TABLES T               WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA      ON AU.USERNAME = ATA.USERNAME)SELECT AU.USERNAME, AU.USER_ID  FROM ALL_USERS AU INNER JOIN PARAMS PA    ON 1 = 1 INNER JOIN DBA_USERS DU    ON AU.USERNAME = DU.USERNAME WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND       AU.USER_ID = PA.USER_ID) OR              (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND       AU.USERNAME NOT IN (SELECT USERNAME FROM USERNAMEDATA)) OR       (PA.SDATE IS NOT NULL AND       AU.USERNAME IN       (SELECT AU.USERNAME            FROM ALL_USERS AU           INNER JOIN DBA_USERS PA              ON AU.USERNAME = PA.USERNAME           INNER JOIN ALL_TABLES ATA              ON PA.USERNAME = ATA.OWNER           WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND       AU.USER_ID = PA.USER_ID))   AND DU.PROFILE LIKE 'D%'   AND AU.USERNAME LIKE PA.SNAME

Summary:

In the FILTER, the SQL statement after NOT EXISTS is executed multiple times, and the original data volume is large. The results can be imagined once every time. However, HINTMATERIALIZE and with as are used together to materialized the inline view. During execution, a temporary view-based temporary table is created. In this way, not exists does NOT scan the big data table every time. You only need to scan the data table once.

But can every statement in WITHAS be materialized? If the statement in with as is called only once, it is better not to use HINTMATERIALIZE, because HINT is usedMATERIALIZE creates a temporary table based on the view results during the first query, which also takes some time. HINT can be used for multiple times.MATERIALIZE.

SQL with advantages

The with as phrase, also known AS subquery factoring, allows you to do a lot of things and define an SQL segment, which will be used by the entire SQL statement. Sometimes, it is to make the SQL statement more readable, or it may be in different parts of UNION ALL, as part of the provided data.
Especially useful for union all. Because each part of union all may be the same, but if each part is executed once, the cost is too high, so you can use the with as phrase, you only need to execute it once. If the table name defined by the with as phrase is called more than twice, the optimizer automatically puts the data obtained by the with as phrase into a TEMP table. If it is called only once, no. The prompt materialize is to forcibly put the data in the with as phrase into a global temporary table. This method improves the speed of many queries.

Why is the statement for updating multiple tables written differently in oracle/DB2 and SQL server?

Oracle is a column-based SQL server Based on rows