some “JOIN” test

1.prepare the data material
SQL> desc part
Name                                      Null?                     Type
—————————————– ——– —————–
PART_ID                                   NOT NULL    VARCHAR2(4)
SUPPLIER_ID                                                   VARCHAR2(4)

SQL> select * from part;
PART SUPP
—- —-
P1   S1
P2   S2
P3
P4

SQL> desc supplier
Name                                      Null?    Type
—————————————– ——– —————–
SUPPLIER_ID                               NOT NULL VARCHAR2(4)
SUPPLIER_NAME                             NOT NULL VARCHAR2(20)

SQL> select * from supplier;
SUPP SUPPLIER_NAME
—- ——————–
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

P3,P4对应没有数据,执行如下的sql

SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id=s.supplier_id

PART            SUPPLIER_NAME
—-                  ——————–
p1                    Supplier#1
p2                   Supplier#2

//it’s easy to understand the inner join,get the rows which values exsit in both of the table

//The join shown above is an inner join, which results in just the rows that have corresponding rows in both tables

//of course,the sql could be written as below(inner join)
SQL> select p.part_id,s.supplier_name from part p join supplier s on p.supplier_id=s.supplier_id;

PART SUPPLIER_NAME
—- ——————–
p1   Supplier#1
p2   Supplier#2

//there will be the same result no matter which object was based on as join object
SQL> select p.part_id,s.supplier_name from supplier s join part p on p.supplier_id=s.supplier_id;

PART          SUPPLIER_NAME
—-            ——————–
p1                Supplier#1
p2               Supplier#2

2.outer join
//If we want all parts to be listed in the result set, irrespective of whether they are supplied by any supplier or not,
then we need to perform an outer join

//left outer join, based on table in left side,if the column of right side don’t have value,take the left side as bench mark

and appeared as null
SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id=s.supplier_id(+)

PART          SUPPLIER_NAME
—-                ——————–
p1                Supplier#1
p2               Supplier#2
p4
p3

//The outer join above lists all of the parts. For the parts that don’t have a corresponding supplier,
null values are displayed for the SUPPLIER_NAME column. However, not all the suppliers are displayed.
Since supplier S3 doesn’t supply any parts, it gets excluded from the result set of the above outer join

//If we want all the suppliers listed in the result set,
irrespective of whether they supply any part or not, we need to perform an outer join like the following

SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id(+)=s.supplier_id

PART    SUPPLIER_NAME
—-         ——————–
p1            Supplier#1
p2            Supplier#2
                 Supplier#3

 

-EOF-

“enq: TX – row lock contention”-case 2

due to “Waits due to Unique or Primary Key Constraint enforcement

–Session#1: ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );

–Session#1: insert into tx_eg values (10,’New’,’MALE’);
–Session#2: insert into tx_eg values (10,’OtherNew’,null);

–DBA:

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type='TX';

SID       TY     ID1         ID2      LMODE     REQUEST    BLOCK
—–    —– — ———- ———- ———- ———- ———-
150     TX     327690   298        0                 4                     0
150     TX     524335   262          6                0                     0
159     TX      327690   298        6                  0                     1

This shows SID 150 is waiting for the TX lock held by SID 159 and it wants the lock in share mode (as REQUEST=4).

check out the v$session_wait for wait events

SQL> select sw.event,sw.sid,sw.wait_class from v$session_wait sw where sw.event like ‘%enq%’;

EVENT                                                       SID                      WAIT_CLASS
—————————–                   ———-          ——————
enq: TX – row lock contention            150                Application

–Ses#1:commit;
–Ses#2:ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
–Ses#2:rollback;

-EOF-