Optimization of Oracle by with table connection in Oracle

One SQL statement with table connection and connectby was successfully optimized after several days, just like killing a big boss in the game. Now let's sort out the process.

One SQL statement with table connection and connect by was successfully optimized after several days. It felt like a big boss was killed in the game. Now let's sort out the process.

Description

One SQL statement with table connection and connect by was successfully optimized after several days. It felt like a big boss was killed in the game. Now let's sort out the process.

Before optimization:

SELECT r. OUT_VER_BEGIN_IDdataID

FROM DMS_DATA_RELA r, DMS_OBJ o

WHERE r. DELETE_FLAG = '0'

AND r. RELA_TYPE_CODE = 'parent'

AND r. OUT_OBJ_CODE = o. OBJ_CODE

AND o. DELETE_FLAG = '0'

AND o. OPEN_STATE = '1'

AND r. IN_OBJ_CODE! = 'O _ in'

Start with r. IN_DATA_ID in

(SELECT d. OUT_DATA_ID

FROM DMS_DATA_RELA d

R. OUT_VER_BEGIN_ID

After optimization:

I made a connect by loop for this SQL statement and connected it to another table. The effect was superb. I found related examples from Li Hua's massive database solution 3.2.5, note: "If the column in the query condition is in the same table, it is not necessary to perform table join first."

Select dataID

From (SELECT r. OUT_VER_BEGIN_ID dataID, r. out_obj_code

FROM DMS_DATA_RELA r

WHERE r. DELETE_FLAG = '0'

AND r. RELA_TYPE_CODE = 'parent'

AND r. IN_OBJ_CODE! = 'O _ in'

STARTWITH r. IN_DATA_ID in

(SELECT d. OUT_DATA_ID

FROM DMS_DATA_RELA d

Where d. OUT_VER_BEGIN_ID =: 1

And d. last_curent_flag = '1 ')

Connect by r. IN_VER_BEGIN_ID = PRIOR r. OUT_VER_BEGIN_ID) e,

DMS_OBJ o

Wheree. OUT_OBJ_CODE = o. OBJ_CODE

ANDo. DELETE_FLAG = '0'

ANDo. OPEN_STATE = '1'

Optimization process:

I went to the Forum of LIU Da for help when I had no plans. Here is the address.

The following process is based on the above. Basic information and methods

Basic Information

Basic Environment
Operating System: windows server 2008 r2 enterprise





Number of table records

In addition, the table also collects statistics.

Columns in the table:

SQL> desc DMS_DATA_RELA
Name Type Nullable Default Comments
------------------------------------------------------------------
RELA_ID CHAR (32)
IN_DATA_ID VARCHAR2 (200) Y
IN_DATA_NAME VARCHAR2 (200) Y
IN_DATA_SOURCE_ID VARCHAR2 (200) Y
IN_DATA_SOURCE_CODE VARCHAR2 (200) Y
IN_OBJ_CODE VARCHAR2 (200) Y
IN_VER_BEGIN_ID VARCHAR2 (200) Y
IN_VER_BEGIN_DATA_SOURCE_ID VARCHAR2 (200) Y
IN_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2 (64) Y
IN_VER_BEGIN_DATA_OBJ_CODE VARCHAR2 (64) Y
OUT_DATA_ID VARCHAR2 (200) Y
OUT_DATA_NAME VARCHAR2 (200) Y
OUT_DATA_SOURCE_ID VARCHAR2 (200) Y
OUT_DATA_SOURCE_CODE VARCHAR2 (200) Y
OUT_OBJ_CODE VARCHAR2 (200) Y
OUT_VER_BEGIN_ID VARCHAR2 (200) Y
OUT_VER_BEGIN_DATA_SOURCE_ID VARCHAR2 (200) Y
OUT_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2 (64) Y
OUT_VER_BEGIN_DATA_OBJ_CODE VARCHAR2 (64) Y
RELA_TYPE_CODE VARCHAR2 (200) Y
MIDDLE_OBJ_CODE VARCHAR2 (64) Y
LAST_CURENT_FLAG CHAR (1) Y
CREATE_USER_NAME VARCHAR2 (200) Y
CREATE_USER_REAL_NAME VARCHAR2 (200) Y
CREATE_TIME TIMESTAMP (6) Y
UPDATE_USER_NAME VARCHAR2 (200) Y
UPDATE_USER_REAL_NAME VARCHAR2 (200) Y
UPDATE_TIME TIMESTAMP (6) Y
DELETE_FLAG CHAR (1) Y
ORDER_NUM NUMBER (10) Y

In addition, it is also mentioned that after the index is cleared, the effect will be good. First, indexes are not cleared for non-technical reasons. In addition, I created a new environment with no too many indexes. I tried to create different indexes on different columns, and the results were the same. For this SQL statement alone, too many indexes should have little impact.

Someone asked about the index status and attached it together:
SQL> select table_name, index_name, index_type, statusfrom user_indexes where table_name = 'dms _ DATA_RELA ';
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS
-----------------------------------------------------------------------------------------------
DMS_DATA_RELA OUT_VER_BEGIN_ID_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_ID_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_NAME_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_SOURCE_CODE_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_SOURCE_ID_INDEX NORMAL VALID
DMS_DATA_RELA IN_OBJ_CODE_INDEX NORMAL VALID
DMS_DATA_RELA IN_VER_BEGIN_ID_INDEX NORMAL VALID
DMS_DATA_RELA OUT_DATA_NAME_INDEX NORMAL VALID
DMS_DATA_RELA OUT_DATA_SOURCE_ID_INDEX NORMAL VALID
DMS_DATA_RELA OUT_OBJ_CODE_INDEX NORMAL VALID
DMS_DATA_RELA RELA_TYPE_CODE_INDEX NORMAL VALID
DMS_DATA_RELA DELETE_FLAG_INDEX NORMAL VALID
DMS_DATA_RELA LAST_CURENT_FLAG_INDEX NORMAL VALID
DMS_DATA_RELA CREATE_TIME_INDEX FUNCTION-BASED NORMAL VALID
DMS_DATA_RELA OUT_DATA_ID_INDEX NORMAL VALID
DMS_DATA_RELA PK_DMS_DATA_RELA NORMAL VALID
16 rows selected

I have tried Materialized View

Create materialized view mv_dms_ddr


Management

Tried Nested Loop

Someone mentioned the nested loop. I have tested it before and the effect is not very good. Now I will attach the nl execution plan. The execution plan is tested by substitution of variables.

Input the variable value without the nl hint.

Set autot traceonly

SELECT

R. OUT_VER_BEGIN_ID dataID

FROM DMS_DATA_RELA r, DMS_OBJ o

WHERE r. DELETE_FLAG = '0'

AND r. RELA_TYPE_CODE = 'parent'

AND r. OUT_OBJ_CODE = o. OBJ_CODE

AND o. DELETE_FLAG = '0'

AND o. OPEN_STATE = '1'

AND r. IN_OBJ_CODE! = 'O _ in'

Start with r. IN_DATA_ID in

(SELECT d. OUT_DATA_ID

FROM DMS_DATA_RELA d

Where d. OUT_VER_BEGIN_ID = '2017 _ syspro_o_wbs'

And d. last_curent_flag = '1 ')

Connect by r. IN_VER_BEGIN_ID = PRIORr. OUT_VER_BEGIN_ID

Used time: 00: 00: 06.45

Execution Plan

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

Plan hash value: 3423681500

Bytes ----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes ----------------------------------------------------------------------------------------------------

| 0 | select statement | 637K | 92M | 6643 (1) | 00:01:20 |

| * 1 | FILTER |

| * 2 | connect by with filtering |

| * 3 | FILTER |

| 4 | COUNT |

| * 5 | hash join | 637K | 92M | 6643 (1) | 00:01:20 |

| 6 | table access full | DMS_OBJ | 63 | 945 | 3 (0) | 00:00:01 |

| 7 | table access full | DMS_DATA_RELA | 637K | 83M | 6636 (1) | 00:01:20 |

| * 8 | table access by index rowid | DMS_DATA_RELA | 1 | 82 | 4 (0) | 00:00:01 |

| * 9 | index range scan | OUT_DATA_ID_INDEX | 1 | 3 (0) | 00:00:01 |

| * 10 | hash join | ||||

| 11 | connect by pump |

| 12 | COUNT |

| * 13 | hash join | 637K | 92M | 6643 (1) | 00:01:20 |

| 14 | table access full | DMS_OBJ | 63 | 945 | 3 (0) | 00:00:01 |

| 15 | table access full | DMS_DATA_RELA | 637K | 83M | 6636 (1) | 00:01:20 |

| 16 | COUNT |

| * 17 | hash join | 637K | 92M | 6643 (1) | 00:01:20 |

| 18 | table access full | DMS_OBJ | 63 | 945 | 3 (0) | 00:00:01 |

| 19 | table access full | DMS_DATA_RELA | 637K | 83M | 6636 (1) | 00:01:20 |

| * 20 | table access by index rowid | DMS_DATA_RELA | 1 | 82 | 4 (0) | 00:00:01 |

| * 21 | index range scan | OUT_DATA_ID_INDEX | 1 | 3 (0) | 00:00:01 |

Bytes ----------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id ):

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

1-filter ("R". "DELETE_FLAG" = '0' AND "R". "RELA_TYPE_CODE" = 'parent' AND

"O". "DELETE_FLAG" = '0' AND "O". "OPEN_STATE" = '1' AND "R". "IN_OBJ_CODE" <> 'O _ in ')

2-filter (EXISTS (SELECT 0 FROM "DMS_DATA_RELA" "D" WHERE "D". "OUT_DATA_ID" =: B1 AND

"D". "OUT_VER_BEGIN_ID" = '2017 _ syspro_o_wbs 'AND "D". "LAST_CURENT_FLAG" = '1 '))

3-filter (EXISTS (SELECT 0 FROM "DMS_DATA_RELA" "D" WHERE "D". "OUT_DATA_ID" =: B1 AND

"D". "OUT_VER_BEGIN_ID" = '2017 _ syspro_o_wbs 'AND "D". "LAST_CURENT_FLAG" = '1 '))

5-access ("R". "OUT_OBJ_CODE" = "O". "OBJ_CODE ")

8-filter ("D". "OUT_VER_BEGIN_ID" = '2017 _ syspro_o_wbs 'AND

"D". "LAST_CURENT_FLAG" = '1 ')

9-access ("D". "OUT_DATA_ID" =: B1)

10-access ("R". "IN_VER_BEGIN_ID" = NULL)

13-access ("R". "OUT_OBJ_CODE" = "O". "OBJ_CODE ")

17-access ("R". "OUT_OBJ_CODE" = "O". "OBJ_CODE ")

20-filter ("D". "OUT_VER_BEGIN_ID" = '2017 _ syspro_o_wbs 'AND

"D". "LAST_CURENT_FLAG" = '1 ')

21-access ("D". "OUT_DATA_ID" =: B1)

Statistics

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

1 recursive cballs

0 db block gets

2103709 consistent gets

0 physical reads

0 redo size

596 bytes sent via SQL * Net toclient

350 bytes received via SQL * Netfrom client

2 SQL * Net roundtrips to/fromclient

4 sorts (memory)

0 sorts (disk)

5 rows processed

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