Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Post Unanswered: Decode or Join, which is better?

    I have two tables as below. One has around 10,000 rows and the other has only 25 rows and i need only 3 out of those 25. Do, I need to join the two tables or use a decode function, which is better.

    Table A Table B
    tableB.id,.... ID, Name

    I want to have "Name" for all the 10,000 rows of table A. Which of the below two options is better( faster). Is there any other better alternative.

    1) select A.*,B.Name from Table A, Table B
    where A.tableB.id=b.id

    2) Select A.*, decode(a.tableB_id,1,'abc',2,'def'3,ghi')
    from table A.

    There is no primary and foreign key relationship between the tables and this is in Oracle 8i.

  2. #2
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34

    DECODE is faster then JOIN

    Hello sir,
    I try to solve problem, see
    I am creating 2 table as below:
    t1 t2
    Id name Id name
    -------------- ----------------
    1 a 1 a
    2 b 2 b
    3 c 3 c
    upto 20000 records upto 10 records only

    Then starts: "set timing on;"
    I triggered first ( USING JOIN )query as :
    Select t1.*,t2.name from t1,t2 where t1.id=t2.id;
    This query takes 32.08 second to execute and showing joined records....
    And
    Then i triggered second ( USING DECODE ) query as :
    select t1.*,decode(t2.id,2,'b',5,'e',10,'j')result from t1,t2 where t1.id=t2.id;
    This query takes only 09.01 second to execute and showing records....

    FROM MY CREATED SCANARIO IT IS CLEAR THAT DECODE IS MORE FASTER THEN JOIN.
    THANKYOU FOR IMPROVING MY KNOWLEDGE, I NEVER THOUGHT LIKE THIS BEFOR.... ABOUT COMPARISION OF DECODE AND JOIN.

    Ref:
    set: sqlplus set command
    decode: Oracle/PLSQL: Decode Function

  3. #3
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34

    Arrow reply

    reply, if you want to discus more, thanku

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you executed those SELECTs only once, results are most probably wrong. You should have ran both of them several times, discard the first execution time (because, subsequent ones most probably used cached data).

    Besides, SET TIMING ON is not the most accurate way to do that. You should have created execution plan and compare these two plans. Additionally, see what SQL Trace says (i.e. view results of the TKPROF).

    Furthermore, did you create any indexes on these tables? Did you collect statistics?

    There's a LOT more stuff to do in order to make a conclusion. A single-run TIMING ON is far from that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •