Skip to Main Content
  • Questions

  • millions of dup rec on join multiple tables

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Prabhu.

Asked: February 24, 2017 - 1:24 pm UTC

Last updated: February 25, 2017 - 1:50 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have one SELECT query , which has join around 10 tables, out of 10 tables 2 of them are big tables and remaining are small tables.These 2 big tables together has 90 laks records. When I execute this query it is throwing me error saying that table space error. So I checked count and knowing that , this query returns 30 Million rows instead of 90 laks rows. I have used all the possible columns for joining the tables.

When I removed any one of the table (out of 2 bigger table), it is giving me correct rows. I have tried by removing table 'A' and keeping table 'B' and vice versa. If I have any one of them in the script, everything is fine, the problem is when having both are in the script. I have tried diff join also. Is any way to use subquery or any other ways, please detail about it.

Note : I have asked admin to increase table space , replied NOT POSSIBLE.

Please help me .

Thanks
Prabhu

and Connor said...

This doesn't sound like a tuning question - this is a correctness question.

If I have a query that does:

SQL> select *
  2  from scott.dept, scott.emp
  3  where job = 'CLERK';

    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
        10 ACCOUNTING     NEW YORK            7369 SMITH      CLERK           7902 17-DEC-80        800                    20
        10 ACCOUNTING     NEW YORK            7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        10 ACCOUNTING     NEW YORK            7900 JAMES      CLERK           7698 03-DEC-81        950                    30
        10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
        20 RESEARCH       DALLAS              7369 SMITH      CLERK           7902 17-DEC-80        800                    20
        20 RESEARCH       DALLAS              7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        20 RESEARCH       DALLAS              7900 JAMES      CLERK           7698 03-DEC-81        950                    30
        20 RESEARCH       DALLAS              7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
        30 SALES          CHICAGO             7369 SMITH      CLERK           7902 17-DEC-80        800                    20
        30 SALES          CHICAGO             7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        30 SALES          CHICAGO             7900 JAMES      CLERK           7698 03-DEC-81        950                    30
        30 SALES          CHICAGO             7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
        40 OPERATIONS     BOSTON              7369 SMITH      CLERK           7902 17-DEC-80        800                    20
        40 OPERATIONS     BOSTON              7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        40 OPERATIONS     BOSTON              7900 JAMES      CLERK           7698 03-DEC-81        950                    30
        40 OPERATIONS     BOSTON              7934 MILLER     CLERK           7782 23-JAN-82       1300                    10


then either

1) my result is correct, or
2) my result is incorrect because my SQL is wrong, or
3) my result is incorrect because these tables should not be joined

In this case, it is "2" (I'm missing a join), but if you are joining 2 tables and *still* getting lots of duplicates then it sounds to me like that is two tables that were not meant to be joined.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Try

Gh.., February 25, 2017 - 5:46 am UTC

Yes Connor is right. I assume that your query is written with oracle owned sql. Try to code it using ansi standard sql and you can see better where the lack is.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.