Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: problem with joins

    hello oracle developers..
    I have 3 tables e1,e2,e3 which r actually created based on deptno 10,20,30 respectively of emp table.like..create table e1 as select *from emp where deptno=10; ..and so forth.
    when I got to get ename,dname from e1,e2,dept
    I can say select.. from e1,dept union sele..from e2,dept;
    ..
    can I have any other alternative method for doing this.
    coz..using union for my actual tables(e1,e2,e3,dept r for eg. sake) is timeconsuming.
    plz. help me in doing this..
    thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    18

    Re: problem with joins

    I think u need to re-look into the design of your tables...y do you have seperate tables storing information for different department nos... also from the way we you have created the tables it seems they contain the same information as emp table so instead of querying by taking a union of e1, e2, etc.. y not just query emp table...


    Originally posted by sridharreddy_d
    hello oracle developers..
    I have 3 tables e1,e2,e3 which r actually created based on deptno 10,20,30 respectively of emp table.like..create table e1 as select *from emp where deptno=10; ..and so forth.
    when I got to get ename,dname from e1,e2,dept
    I can say select.. from e1,dept union sele..from e2,dept;
    ..
    can I have any other alternative method for doing this.
    coz..using union for my actual tables(e1,e2,e3,dept r for eg. sake) is timeconsuming.
    plz. help me in doing this..
    thanks in advance.

  3. #3
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Re: problem with joins

    Originally posted by muffi
    I think u need to re-look into the design of your tables...y do you have seperate tables storing information for different department nos... also from the way we you have created the tables it seems they contain the same information as emp table so instead of querying by taking a union of e1, e2, etc.. y not just query emp table...
    The reason for splitting the table into multiple tables is ..
    the actual table(in the eg. emp) contains huge data...like 4-5 milion records which is queried in nested loops....so inorder to to improve the speed of data retrieval I splitted the tables..and infact the split improved the speed of lot of queries in my application.so..in this case the application cant use both emp and e1,e2,e3 etc.
    I think Im clear abt. the issue.. please let me know if iam not.
    thanks for response..
    I hope u will reply me..

  4. #4
    Join Date
    Mar 2004
    Posts
    9

    Lightbulb

    I was just wondering if initially instead of splitting table if partitioning could have helped... that would have served the purpose.

  5. #5
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    oracle join

    Originally posted by visheetal
    I was just wondering if initially instead of splitting table if partitioning could have helped... that would have served the purpose.
    yah.. thanks for the suggestion.. I wolud have to make lot of changes in the existing design if I have to make use of partitions.. anyway.. cud u plz.. tellme whether this will create any problems.. and cud u tell me how do I go abt. to solve the first problem ..i.e.
    I got to get the data from those 2 tables e1,e2 without using set operators.
    thanks in advance..

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by visheetal
    I was just wondering if initially instead of splitting table if partitioning could have helped... that would have served the purpose.
    Yup, Partitioning, Views, Materialized Views ...

    You have painted/designed yourself into a corner.
    Bad sql is now the result of poor design (sorry to say)

    A solution to your current dilema is to redesign.
    Right now all you are asking for is a quick-fix.

    have you tried (I doubt this would be faster however):

    select * from
    (select ename from e1 where dept_no = X),
    (select ename from e2 where dept_no = Y)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89
    Originally posted by The_Duck
    Yup, Partitioning, Views, Materialized Views ...

    You have painted/designed yourself into a corner.
    Bad sql is now the result of poor design (sorry to say)

    A solution to your current dilema is to redesign.
    Right now all you are asking for is a quick-fix.

    have you tried (I doubt this would be faster however):

    select * from
    (select ename from e1 where dept_no = X),
    (select ename from e2 where dept_no = Y)
    thanks..
    but the query u gave returns all the rows(15) instead of 3+5.
    I replaced x with 10 and y with 20
    thanks in advance..
    plz. let me know abt . this.

  8. #8
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Originally posted by sridharreddy_d
    thanks..
    but the query u gave returns all the rows(15) instead of 3+5.
    I replaced x with 10 and y with 20
    thanks in advance..
    plz. let me know abt . this.
    You said that tables e1,e2,e3 are for dept nos 10.20,30 respectively.. You mean there is single dept name for all employees in e1.. similarly in e2 and e3 as well.

    why not try this...

    select ename, (select dname from dept where dept_no=10)
    from e1
    UNION
    select ename, (select dname from dept where dept_no=20)
    from e2

    In anycase, as previously sugegsted, try going for design change as against 'another' fix.
    Oracle can do wonders !

  9. #9
    Join Date
    Feb 2004
    Posts
    18

    Re: problem with joins

    [QUOTE]Originally posted by sridharreddy_d
    hello oracle developers..
    I have 3 tables e1,e2,e3 which r actually created based on deptno 10,20,30 respectively of emp table.like..create table e1 as select *from emp where deptno=10; ..and so forth.
    when I got to get ename,dname from e1,e2,dept
    I can say select.. from e1,dept union sele..from e2,dept;
    ..
    can I have any other alternative method for doing this.
    coz..using union for my actual tables(e1,e2,e3,dept r for eg. sake) is timeconsuming.
    plz. help me in doing this..
    thanks in advance.
    [/QUOTE

    I think you can eliminate the join with dept table since if you are querying e1, all the records will belong to the same dept. So you can split the queries instead of one single query.. I hope it solves your problem... but I think a re-design is the best option

Posting Permissions

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