Oracle uses WITHAS and HINTMATERIALIZE to optimize SQL statements to reduce FILTER efficiency

During the project process, a page uses SQL statements similar to the following to query data. For confidentiality and convenience, I replaced the table names and fields in the project with those in the ORACLE database.

During the project process, a page uses SQL statements similar to the following to query data. For confidentiality and convenience, I replaced the table names and fields in the project with those in the ORACLE database.

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
(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

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

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

The first step is to execute the Three OR clauses after the WHERE condition of the statement and the primary query respectively. The execution speed is very fast and it is very slow to put them in one clause.

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.

For more details, please continue to read the highlights on the next page: