Two tables have 170 million data, index, but don't go after the correlation index, perform an hour all have no result come out,

STAF_PSN_CLCT_temp is the main table, want to take lzb_dwid_1 a emp_clct_detl_id,

 select aaa psn_clct_detl_id, BBB. Emp_clct_detl_id from STAF_PSN_CLCT_temp aaa 
Left the join lzb_dwid_1 BBB on aaa. Psn_clct_detl_id=BBB. Psn_clct_detl_id 


Select count (1) the from STAF_PSN_CLCT_temp; 173748965 lines/*, indexed psn_clct_detl_id */

Select count (1) the from lzb_dwid_1; 173136805 lines/*, indexed psn_clct_detl_id */

Or with the following wording, query is not out:

 select aaa psn_clct_detl_id, 
(select emp_clct_detl_id 
The from lzb_dwid_1 BBB 
Where the aaa. Psn_clct_detl_id=BBB. Psn_clct_detl_id) emp_clct_detl_id 
The from STAF_PSN_CLCT_temp aaa. 

Excuse me each great god, and what is the query SQL is faster?
The main is to want to:
One of the main table aaa field "psn_clct_detl_id" and another "emp_clct_detl_id" two fields of BBB table,

CodePudding user response:

If there is no filter conditions that can only be used in parallel try

The select/* + parallel (aaa, 4) (BBB, 4) index (aaa, index_name ). */aaa psn_clct_detl_id, BBB. Emp_clct_detl_id
The from
Lzb_dwid_1 STAF_PSN_CLCT_temp aaa, BBB
Where the aaa. Psn_clct_detl_id=BBB. Psn_clct_detl_id

CodePudding user response:

But both the number of rows in the table is not equal, according to aaa table shall prevail, associated with a left?

The select/* + parallel (aaa, 4) (BBB, 4) index (aaa, index_name) */aaa. Psn_clct_detl_id, BBB. Emp_clct_detl_id
The from
Lzb_dwid_1 STAF_PSN_CLCT_temp aaa, BBB
Where the aaa. Psn_clct_detl_id=BBB. Psn_clct_detl_id (+)

CodePudding user response:

1, remove the SQL execution plan;
2, stick down the two indexes on tables;
3, this kind of filter conditions of SQL, most of the time don't walk index, index is the index to cover;
4, I don't know the building Lord so much data? If it is a statistical no problem, but if it is in order to query, this still there should be a filter or paging,