-
Join on Tables
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
-
what do you mean not working, what error do you get
-
Originally Posted by davey23uk
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
-
Originally Posted by NBSR
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"
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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....
-
it's hot hung up - it's doing what you asked it to do
trace the query, see where the time is being taken
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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...
Last edited by NBSR; 05-05-2008 at 02:05 PM.
-
Please read this paper how to collect trace your session:
http://oracleact.com/papers/beacon-ora-perf-class-3.pdf
-
You have answered about 20% of our questions, not enough... questions are asked for a reason.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|