Hi
We have a very poor sql statement when synchronizing in Eclipse.
harversions data rows : 296,000
harversioninview data rows : 1,047,000
INSERT INTO harOBJIDTemp
SELECT MAX ( V1.versionobjid )
FROM harVersions V1, harVersionInView VIV, harversiontemp X,
harversions V
WHERE V1.versionobjid = VIV.versionobjid
AND VIV.viewobjid IN ( ?, ? )
AND V1.inbranch = 0
AND X.versionobjid = V.pathversionid
AND v1.itemobjid = V.itemobjid
AND V1.versionstatus IN ( 'N','D' )
GROUP BY V1.itemobjid
-- RETURN CODE: DB_SUCCESS
-- ROWS UPDATED: 17048
-- END: 2020/10/22 15:16:54 **** SQL Milliseconds: 22473
We have a SQL statement that our DBA has tuned and we want to change it to this one.
INSERT INTO harOBJIDTemp
SELECT /*+ LEADING(V) USE_NL(V1) PUSH_PRED(V1) */ V1.versionobjid
FROM (SELECT /*+ NO_MERGE ORDERED USE_NL(V) INDEX(X (VERSIONOBJID)) INDEX(V (PATHVERSIONID, ITEMOBJID)) */ DISTINCT V.itemobjid
FROM harversiontemp X,
harversions V
WHERE X.versionobjid = V.pathversionid ) V,
(SELECT /*+ NO_MERGE INDEX(V1 (ITEMOBJID,VERSIONOBJID,VERSIONSTATUS,INBRANCH))*/
V1.itemobjid
, MAX ( V1.versionobjid ) AS versionobjid
FROM harVersions V1
WHERE 1=1
AND V1.inbranch = 0
AND V1.versionstatus IN ( 'N','D' )
GROUP BY V1.itemobjid) V1
WHERE 1=1
AND v1.itemobjid = V.itemobjid
AND EXISTS (SELECT /*+ NO_UNNEST INDEX(VIV (VERSIONOBJID, VIEWOBJID)) */ 'X'
FROM harVersionInView VIV
WHERE V1.versionobjid = VIV.versionobjid
AND VIV.viewobjid IN ( 2112, 2111 ) );
Boroadcom policy says that the source cannot be changed. Is there any case that solved through Oracle tunning?
As a result of our testing, it seems that the desired performance cannot be achieved without modifying the sql statement.
Thanks.