SQL Optimization for cross-dataset query of large data tables in Oracle

Since we recently want to query the intersection of two large tables (tens of thousands of records) and B (millions of records), it is too slow to query the joined fields, I have summed up another efficient statement that can solve the problem. I will share it with you. I will not talk about it much. I will introduce my ideas in detail below (my requirements are the ranking of Table B, and take part of the field information from A to export to excel for browsing ):

1. First query the result set from Table B and write it to the temporary table temp,

False Statement: insert into temp (field1, field2, field3....) Select field1, field2, sum (field3) as AA from Table B where condition... Group by field1, field2

Instance eg: insert into sa_sale_order (barcode, sellprice1, sumqty) Select. tsxpluno,. tsxpc, sum (tsxqty) as AA from sa_sale_ct A where (1 = 1) and '+ search_str + 'group by. tsxpluno,. tsxpc ';

2. Then, compare the record corresponding to a and temp, and update other fields in the temp table (obtained from a) [associate a with temp by ID]. Here, specify, update can only be updated one by one, otherwise it will cause a crash.

False Statement: Update temp set field4 = (select field4 from Table A where temp. ID = Table A. ID)

Instance eg: Update sa_sale_order a set goodsid = (select goodsid from hq_goods B where. barcode = B. barcode )';

Update sa_sale_order a set goodsname = (select goodsname from hq_goods B where a. barcode = B. barcode )';

3. After step 3 is updated, after all the data fields in the temporary table are assigned a value, you can query the sorting data, in Oracle, rownum is used to select the first and last n names in sorting.

False Statement: Select * from (select * from temp order by DESC/ASC) Where rownum <= 100

Instance eg: Select * from (select * From sa_sale_order order by sumqty '+ order_str +') Where rownum <= 100 Where order_str is the sort passed by conditions (DESC, ASC)

The above is a bit of my work I have learned. Here, if you want to update data in step 1, there are better methods or SQL statements, please follow up actively, together to improve our knowledge points. Of course, you have a better solution. Please write it in the post to achieve common technological progress !!