never-ending query across dblink

[SimonSellick]

Hi,

I'm running several queries from the wrong end of a dblink (the bulk of the data is at the far end). Most of them work well, but this one (see below) never returns (or at least, it has never returned by the time the db closes down each evening).

If I remove both of the LEFT JOINs the results come back within a minute or two, which is fine (there are about 50K resulting rows).

When I add just one of the LEFT JOINs back, the query doesn't run in a feasible time.

If I remove the local table and run the remaining query on the natural server (the one with all the rest of the data on it), it runs without the outer joins in about 30 seconds, but with both of them present, again it fails to run in a sensible time.

Again, on the local server, if I change the outer joins to inner, I get no records (fairly quickly), which is not entirely unexpected. If I alter just one of them, the results are quick and as expected.

Might this be an Oracle/ANSI-join problem? I can never remember the Oracle syntax for outer joins, but I do remember a problem with ANSI joins in version 8 (this is 9i).

I've run analyze table compute statistics on all four main tables, but I'm not well up on Oracle performance tweaks - and I suspect that I have done something daft rather than this being a simple tuning issue. I have no DBA permissions on either schema so there is in any case a limit to what I can achieve through tuning, but I have asked the DBA to look for any obvious resource issues, with no luck.

FWIW, the query is:

select count(*)
from gflows.u01@flot u
join gflows.gas_registrations@flot r
on r.meter_point_ref = u.meter_point_ref
and r.confirmation_effective_date <= u.creation_date + 7
join useful_dates d
on d.base_date = r.confirmation_effective_date
left join gflows.u03@flot
on u03.meter_point_ref = u.meter_point_ref
and u03.meter_reading_source = 'E'
and u03.creation_date between u.actual_read_date and p_end
left join gflows.m03@flot
on m03.meter_point_ref = u.meter_point_ref
and m03.read_type in ('B', 'E', 'M')
and m03.creation_date between u.actual_read_date and p_end
where u.creation_date between p_start and p_end
and u.meter_reading_source in ('E', 'M')
and u.actual_read_date between d.work_minus5 and d.work_plus5

Sorry about the length of this post. Any suggestions would be welcomed.

 

[SimonSellick]

Found it. Converting to old-style outer joins (with + signs liberally sprinkled around the join columns) threw an error "Can't use outer join operator (+) with OR or IN" or words to that effect.

If I change the IN ('B', 'E', 'M') to = 'B' it runs quickly; same for 'E' or 'M'.

So now I have to work out how to fool Oracle into giving the results I want. I'll try a sub-query, I suppose. Thanks Mr Oracle - nine major relases and you still can't get an ANSI join right.

 

[sem]

Most probably even fresh statistics doesn't help as each database knows only its own part. You may significantly improve performance by utilizing DRIVING_SITE hint.

BTW are you sure that LEFT JOIN and LEFT OUTER JOIN are the same?





Regards, Dima

http://seminihin.narod.ru

 

[SimonSellick]

Hi sem

Thanks for the hint - I had already tried DRIVING_SITE before I moved on to the local db, and it improved speeds in the working versions but didn't stop the failure.

I haven't tried replacing LEFT with LEFT OUTER - I'll give it a try tomorrow. I know that in SQL Server they mean the same thing, but perhaps Oracle treats them differently.

In the end, I think that I will have to reorganise with a sub-query, as the Oracle error when I changed to the old-style outer join syntax was fairly explicit about the restriction.

Thanks again.

 

[Turkbear]

Hi,
They are the same. Oracle Docs have this:

join_type
The join_type indicates the kind of join being performed:

Specify INNER to indicate explicitly that an inner join is being performed. This is the default. 
Specify RIGHT to indicate a right outer join. 
Specify LEFT to indicate a left outer join. 
Specify FULL to indicate a full or two-sided outer join. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join will be preserved and extended with nulls. 
You can specify the optional OUTER keyword following RIGHT, LEFT, or FULL to explicitly clarify that an outer join is being performed. 
JOIN
The JOIN keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in WHERE clause joins with FROM clause join syntax.

ON condition
Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause.

USING column
When you are specifying an equijoin of columns that have the same name in both tables, the USING column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Do not qualify the column name with a table name or table alias.

In an outer join with the USING clause, the query returns a single column which is a coalesce of the two matching columns in the join. The coalesce functions as follows:

COALESCE (a, b) = a if a NOT NULL, else b.


Therefore:

A left outer join returns all the common column values from the left table in the FROM clause. 
A right outer join returns all the common column values from the right table in the FROM clause. 
A full outer join returns all the common column values from both joined tables. 
Restriction on Columns Used in Joins
You cannot specify a LOB column or a collection column in the USING column clause.

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sem]

Syntax errors are raised befor an attempt to execute code, thus your hang up could not be caused by incorrect outer join. In any case without knowing about data amount/distribution across tables it's almost impossible to give a good advice.

But your target seems to be in minimize data transfers and utilizing indexes. Note that without that hint data from all remote tables is transfered to your local site and then processed there. If your useful_dates table is smaller than others it would be better to send only it to the remote site.

Regards, Dima

http://seminihin.narod.ru

 

[SimonSellick]

Hi Dima,

The only change I made between the query that did not complete and the one that raised the error was the join syntax, specifically, from

a left join b on b.col = a.col and b.col1 in (set)

to

a, b, where b.col(+) = a.col and b.col1(+) in (set)

I am tentatively suggesting that there might be a bug in the parser as it spots the problem in the latter syntax but not in the former.

I wonder whether others have come across this - or am I missing something obvious?

Turkbear - thanks for the manual page confirming that the word OUTER is not expected.

Regards,
Simon.

 

[sem]

Turkbear, thanks also from me, still using old notation.

Simon, outer join against OR using (+) notation is syntax error, using JOIN syntax - is not, you may check it on your local database. Try to trace both local session and remote one opened by db_link.

BTW what are approximate table sizes and do both databases have the same (9i?) version?

Regards, Dima

http://seminihin.narod.ru

 

[SimonSellick]

Dima,

Yes, that was what I found, too - Oracle reports a syntax error for an outer join when an OR or IN () is included with the old-style syntax, but not with the Ansi syntax; but it still fails to work correctly with the Ansi style even though it does not complain about the syntax.

Both databases are 9i running under MVS and the table sizes (all four main tables) are of the order of 100k - 1M rows; useful_dates is a few hundred rows.

I have worked around this now by altering the join clause from

a
LEFT JOIN b ON b.col = a.col AND b.col1 IN (set)

to

a
LEFT JOIN (
SELECT * FROM b WHERE col1 IN (set)
) b ON b.col = a.col

...and this seems to work as expected.

Thanks both for your help.

Regards,
Simon.

 

[sem]

Did you add DRIVING_SITE hint to force remote execution?

Regards, Dima

http://seminihin.narod.ru

 

[SimonSellick]

Yes, and it now runs in about 40 seconds - thanks again all.