Oracle uses with AS and hint materialize to optimize SQL to resolve filter inefficiencies



in the process of doing the project, a page uses a similar example of the following SQL query data. For the sake of secrecy and ease of use, I replaced the table names and fields in the project with the system tables and fields in the ORACLE database.

in the project I have done. Similar to the All_tables table has about 8W of data, the following query SQL 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 joins 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 joins dba_users DEV on AU. USERNAME = DEV. USERNAME INNER JOIN (SELECT OWNER as USERNAME from All_tables T WHE RE 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 joins 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

for the above SQL the problem of slow statement operation. I did the following analysis for example:

the first step, the statement of the WHERE condition after the three or are respectively and the main query to run a piece, running speed is very fast, put on a piece is very Slow.

               The second step. Against the aboveSQLand three xORsplit out of threeSQLThe running plan, for example, as seen in the. Found aboveSQLIn the run there is aFILTER, the filter verb is used in theNot EXISTSthat is causing thisSQLThe reason for running slowly.

the reason was found. We have to find a way to run the plan.FILTERremoved. Start to addHINT. But the experiment was very muchHINT. are not working. The final result was the same, and later thought with AScan improveSQLthe query speed, it will affect theSQLThe part that runsSQLput in with ASinside, the result is still the same. Later try to putHINT materialize and with AS are used in conjunction with the following SQL, the query speed is improved a lot. For example, as seen in the. the Not EXISTS of the FILTER in the run 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 joins dba_users DEV on AU. USERNAME = DEV. USERNAME INNER JOIN (SELECT OWNER as USERNAME from All_tables T WHERE t.last_analyzed = TRU NC (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 joins 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 joins 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

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbglhbmd3zwl3zwkxmza=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma== /dissolve/70/gravity/southeast ">

Summarize:

in filter not EXISTS after the sql statement run multiple times, the amount of data is very large, each time to run again, the results can be imagined. However, use hint materialize Span style= "font-family: Arial" > and with as Span style= "font-family: Arial" > Combined, the inline view is manifested, and a temporary table based on the view is created during the run.

This will not every time not EXISTS to run the Big data table scan, only need to scan once.

but is it possible to withas Each statement manifested that? Suppose with as The statement is only called once, you may not use the hint materialize hint materialize Span style= "font-family: Song Body" > The first query creates a temporary table based on the results of the view, which also takes some time.

You can use HINTmaterializeIf you use it multiple times.

Oracle uses with AS and hint materialize to optimize SQL to resolve filter inefficiencies