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