Join on Tables

[NBSR]

I was reading all about joins and couldnt figure out how to join my tables...i have 3 tables

assembly_parts
Name Null? Type
----------------------------------------- -------- --------------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
BRAND_ID NOT NULL VARCHAR2(4)
ASSEMBLY_PART_NBR NOT NULL VARCHAR2(35)
ASSB_MFG_ORG_SEQ_ID NUMBER(8)
ASSB_PROD_ORG_SEQ_ID NUMBER(8)
PROD_CODE VARCHAR2(4)
ALT_MFG_BILL_OF_MATERIAL_NBR NUMBER(2)
PART_DESC VARCHAR2(35)
PROD_TRADEMARK_ID VARCHAR2(30)
PRODUCT_NM VARCHAR2(70)
MANUFACTURING_LOCATION_ID NOT NULL VARCHAR2(2)

PK ASSB_PT_NBR_SEQ_ID

FK BRAND_ID
ASSB_MFG_ORG_SEQ_ID
ASSB_PROD_ORG_SEQ_ID
MANUFACTURING_LOCATION_ID



ps_operations

Name Null? Type
----------------------------------------- --------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
OPERATION_NBR NOT NULL VARCHAR2(10)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
OPERATION_DESC NOT NULL VARCHAR2(70)
HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6)
PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7)
EFFECTIVE_TO_DT DATE
EXTRACT_IND VARCHAR2(1)

composite primary key ASSB_PT_NBR_SEQ_ID
OPERATION_NBR

FK ASSB_PT_NBR_SEQ_ID


assembly_part_details

Name Null? Type
----------------------------------------- -------- -------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
EXPORT_SCHEDULE_B_CLASS_CDE VARCHAR2(12)
NAFTA_BASIS_CDE VARCHAR2(1)
EFFECTIVE_TO_DT DATE
GENERIC_PART_DESC VARCHAR2(35)
MARKING_WAIVER_IND VARCHAR2(1)
MARKING_WAIVER_DT DATE
ASSEMBLY_TOTAL_COST_AMT NUMBER(13,6)
ASSEMBLY_FOREIGN_COST_AMT NUMBER(13,6)
NAFTA_PART_ORIGIN_IND VARCHAR2(1)
NAFTA_DETERMINATION_TYPE_CDE VARCHAR2(1)
PCODE VARCHAR2(17)
EXTRACT_IND VARCHAR2(1)
TOTAL_OVERALL_COST_AMT NUMBER(14,6)
DUTY_MATERIAL_COST_AMT NUMBER(14,6)
NON_DUTY_MATERIAL_COST_AMT NUMBER(14,6)
NON_DUTY_PACKAGING_COST_AMT NUMBER(14,6)
DIRECT_LABOR_COST_AMT NUMBER(14,6)
DIRECT_LABOR_PER_HOUR_RATE NUMBER(5,2)
COSTING_DT DATE
COST_ERROR_IND VARCHAR2(1)
OVERHEAD_RATE_AMT NUMBER(6,3)
TOTAL_OVERHEAD_COST_AMT NUMBER(14,6)

composite primary key ASSB_PT_NBR_SEQ_ID
EFFECTIVE_FROM_DT

FK ASSB_PT_NBR_SEQ_ID
NAFTA_BASIS_CDE
NAFTA_PART_ORIGIN_IND
NAFTA_DETERMINATION_TYPE_CDE
EXPORT_SCHEDULE_B_CLASS_CDE

I tried many ways to join these tables but they are not working....
Please help me out

Query

select a2.assembly_part_nbr as Assembly_Part_NBR,
sum(p.hours_per_piece_qty) As Total_hrs_per_piece,
a2.part_desc||' '||'SAP' as Part_DESC
from ps_operations p ,
assembly_part_details a1,
assembly_parts a2
where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
to_char(p.effective_to_dt) is null
group by a2.assembly_part_nbr, a2.part_desc

please explain me where i was wrong and why its not working...

Thanks a lot

Sree

[davey23uk]

what do you mean not working, what error do you get

[NBSR]

what do you mean not working, what error do you get

Well its just getting hung up... its processing processing and processing.... finally i have to end it by closing the session

[PAVB]

Well its just getting hung up... its processing processing and processing.... finally i have to end it by closing the session

That's not the definition of "not working", actually that's the definition of "working"

[NBSR]

select /*+ index (a2 Assembly_parts_uk) */
/*+ index (p ps_operation_pk)*/
a2.assembly_part_nbr as Assembly_Part_NBR,
sum(p.hours_per_piece_qty) As Total_hrs_per_piece,
a2.part_desc||' '||'SAP' as Part_DESC
from ps_operations p ,
assembly_part_details a1,
assembly_parts a2
where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
to_char(p.effective_to_dt) is null
group by a2.assembly_part_nbr, a2.part_desc

I gave indexes then also its getting hung up... what should i do now... give me suggests....

[davey23uk]

it's hot hung up - it's doing what you asked it to do

trace the query, see where the time is being taken

[PAVB]

Your hint syntax doesn't work, it's wrong.

Why do you think you have to hint those indexes?

Do you have an explain plan? would you share it with us?

What are the sizes of your tables?

What are the indexes you have available?

How many distinct keys do you have on your indexed columns?

Are your stats up-to-date?

[NBSR]

Well... i dont know why .. i have added rownum<100 in my query and it gave me one single row.... but with out that its doesnt give me any output it just goingon processing... well i was just trying out indexs they are not important any ways... can u say me what excatly was wrong with my indexes in my query...

The execution plan for both the queries,... i cant have rownum<100 in my query,... i just tried it to see ... and my data base is not large... for this join and the condition i have to get around 7000 rows....

SQL> select
2 a2.assembly_part_nbr as Assembly_Part_NBR,
3 sum(p.hours_per_piece_qty) As Total_hrs_per_piece,
4 a2.part_desc||' '||'SAP' as Part_DESC
5 from ps_operations p ,
6 assembly_part_details a1,
7 assembly_parts a2
8 where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
9 a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
10 to_char(p.effective_to_dt) is null and
11 rownum <100
12 group by a2.assembly_part_nbr, a2.part_desc
13 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PS_OPERATIONS' (TABL
E)

4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'ASSEMBLY_PARTS' (TABLE)
7 5 INDEX (RANGE SCAN) OF 'ASSEMBLY_PART_DETAILS_FK1
' (INDEX)

8 4 INDEX (RANGE SCAN) OF 'PS_OPERATIONS_FK1' (INDEX)



SQL> select
2 a2.assembly_part_nbr as Assembly_Part_NBR,
3 sum(p.hours_per_piece_qty) As Total_hrs_per_piece,
4 a2.part_desc||' '||'SAP' as Part_DESC
5 from ps_operations p ,
6 assembly_part_details a1,
7 assembly_parts a2
8 where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
9 a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
10 to_char(p.effective_to_dt) is null
11 group by a2.assembly_part_nbr, a2.part_desc;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_OPERATIONS' (TABLE)
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (FULL) OF 'ASSEMBLY_PARTS' (TABLE)
6 4 INDEX (RANGE SCAN) OF 'ASSEMBLY_PART_DETAILS_FK1'
(INDEX)

7 3 INDEX (RANGE SCAN) OF 'PS_OPERATIONS_FK1' (INDEX)

I hope i answered all ur questions... can u help me now...

[tamilselvan]

Please read this paper how to collect trace your session:

http://oracleact.com/papers/beacon-ora-perf-class-3.pdf

[PAVB]

You have answered about 20% of our questions, not enough... questions are asked for a reason.