Oracle Development Series (v) 3 ways to take the left table not on the right table-extended to DB2

Citation:

When we do database development with PL SQL processing data, often encounter a table is not in the table B records or the left table is not in the right table records, so specifically to do a simple summary, in order to use the review later.

Solution:

Take a table A field is not the B table our natural logic would think of a not in B, this is the first method

1 not in

For example, the database is a test database that does not run a business, and the data volume of the two tables is the same as in the case of the non-in to find records in the table prd_inst_id

For example, for the Production library table L and T table data volume is the same, the data volume of about 900w

2 not exsits

1) For example, use NOT exists to find a table prd_inst_id is not in table B records

2)

Select ssss1.* from  odso.tb_b_ft_broadband_l Ssss1 where isn't exists (select 1 from          Tb_b_ft_broadband b         where ssss1.prd_inst_id = b.prd_inst_id); --2min 30s

3Left joins or right join special attention generally don't think of this

such as: First use a table Left association B table to a table all records of the result set, and then limit b.latn_id is null (assuming B itself record latn_id is not empty) should actually use B.PRD_INST_ID is null (association field)

You can get the records in table B that do not have a table prd_inst_id, that is, a is not in table B.

All of the above is a table is not in table B records accounted for the total record data is very few cases, a few one out of 10,000

In terms of efficiency, the test library's not-in ratio is not exsits at 20w and the data volume is fast, although the gap is twice times, but the time difference is small.

The production library's not exists is a lot faster than not in two minutes, so there's a faster question about not in and not exists, depending on the situation and the implementation plan , refer to:

Oracle Development Series (iii) exists? exists usage and in? In comparison (10g)

It took 10 minutes to see the left join time, a few times more than in and exists,

But in the DB2 is generally found in large data volume does not exist in general with the left Join Association field as the distribution key, will soon.

remark:The 3rd kind of left join, sometimes in the interview may ask. To prevent tension from being missed.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Development Series (v) 3 ways to take the left table not on the right table-extended to DB2