Join on Tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Join on Tables

  1. #1
    Join Date
    May 2008
    Posts
    34

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    what do you mean not working, what error do you get

  3. #3
    Join Date
    May 2008
    Posts
    34
    Quote 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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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.

  5. #5
    Join Date
    May 2008
    Posts
    34
    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....

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    it's hot hung up - it's doing what you asked it to do

    trace the query, see where the time is being taken

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  8. #8
    Join Date
    May 2008
    Posts
    34
    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.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Please read this paper how to collect trace your session:

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

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width