Well, as far as I understand this query and the output you reported, at least one of your three tables (a,b,c) is empty! You are performing cartesian join, as there is no where clause. And cartesian product returns no rows only if one or more joined tables contains no rows.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
But I want the query to run even if the table c does not contain any rows. And,does not decode take care of 'WHERE'.
Indeed, if i put some data into c , I get cartesian result which again is not much use to me
I need to make a report from tables A, B and C and format it.
If A.ittype=1 or 2, then corresponding itaddr from A should be mapped with B.itaddr. If they are same, then the corresponding B.servloc should be displayed as intended_servloc.
If A.ittype=3 or 4, then corresponding itaddr from A should be mapped with C.itaddr. If they are same, then the corresponding C.servloc should be displayed as actual_servloc.
The table A is a partitioned table with each partition having around 60 milliion records. Table B has around 1.6 million records and table C has about 100,000 records.
There are no indexes on table A because of the need of fast insertions.
Okay, it's your database, but that is rather, uh, insane :)
Okay, so we have no choice but to do a full table scan on 60 million rows. The question is how to then join to the other tables...
This query will give you what you are asking for, but I may need to think about whether or not there is a better way.
---------1,---(SELECT SERVLOC FROM B WHERE B.ITADDR = A.ITADDR),
---------2,---(SELECT SERVLOC FROM B WHERE B.ITADDR = A.ITADDR),
---------3,---(SELECT SERVLOC FROM C WHERE C.ITADDR = A.ITADDR),
---------4,---(SELECT SERVLOC FROM C WHERE C.ITADDR = A.ITADDR)
The problem, of course, is that you will end up doing a total of 60 million correlated sub-selects, which simply can't be fast. I'll assume that you have *some* kind of restricting criteria and are not actually building a report with 60 million rows. Now, if your restricting criteria is on B or C, this solution will not work.
I will be trying the solutions you suggested in the morning.
No, the restrictions are only on the table A and none on B or C. So, probably I will end up having a temporary table which will be populated using the restrictions on A, and then will be used with B and C in fetching the report.
About the absence of Indexes, as I said fast insertions are the criteria presently. It is felt that indexes may slow down the insertions.
However, after some testing, I intend to have some indexes
on the table A.
Thanks a lot.