Multiple datasets in one dataset

#21

On Oct 18, 7:53 am, roman.moroku... (AT) googlemail (DOT) com wrote:

Quote:

What is the execution plan? In SQL*Plus use the command (i hope i get
this right) "SET AUTOT TRACE EXP STAT", and run the query, then post
the output here.

Well, this was indeed right. Congratulations.

Could those records bve ignored? If so, use a WHERE clause to exclude
such a case, such as, AND A.Value1 > 0 AND A.Value2 > 0 ... AND
A.Value10 > 0.

This should be discussed. But I think they should be shown also.

(+) is Oracle specific, just like *= is SQL Server specific. All
support the ANSI syntax, however.

I was aware of this, but to lazy to convert the statement into
standard SQL ;-(

An EXPLAIN PLAN would probably be helpful here as well.

....

The execution plan does not reveal any problem, I guess, whereas
the plain statement does. It never returns.

Please let us know which exact version and platform you are using.
There are some outer join bugs, but not generally on anything this
simple AFAIK. Are you seeing your cpu pegged? Do you know how to
check for wait states with Oracle? Anything else going wrong, errors
in the alert log?

jg
--
@home.com is bogus.
virtually eliminated, huh-huh, huh-huh...
http://www.statesman.com/search/cont...ollglitch.html

#22

Quote:

For the time being, perhaps you can try them to see if there is a
difference.

I did try them. The statement was lightening fast and returned.

Quote:

I don't like the SQL standard syntax for JOINs. So, you'll get no
argument from me.

I don´t like it too. In the real world I have written a conversion
program which does this on our application on the fly. There the
appropriate parameters gets defined and the program create either
the Oracle syntax, or the standard syntax, depending on database
in use.

Regards Roman

#23

Hi Joel,

Quote:

Please let us know which exact version and platform you are using.

Oracle 10g on Windows

Quote:

There are some outer join bugs, but not generally on anything this
simple AFAIK.

Well, I understand.

Quote:

Are you seeing your cpu pegged?

No. I did not look on the server yet. The client gets in a time out
after a while. So it looks like Oracle never returns.

Quote:

Do you know how to check for wait states with Oracle?

No. Please tell me.

Quote:

Anything else going wrong, errors in the alert log?

Did not look at it yet. Will have to soon.

Regards Roman

#24

Quote:

Please let us know which exact version and platform you are using.

Oracle 10g 10.0.2.1.0 on Windows 2003 64 bit
No patch so far installed.

Regards Roman

#25

On Oct 19, 1:08 am, roman.moroku... (AT) googlemail (DOT) com wrote:

Quote:

Hi Joel,

Please let us know which exact version and platform you are using.

Oracle 10g on Windows

There are some outer join bugs, but not generally on anything this
simple AFAIK.

Well, I understand.

Are you seeing your cpu pegged?

No. I did not look on the server yet. The client gets in a time out
after a while. So it looks like Oracle never returns.

Do you know how to check for wait states with Oracle?

No. Please tell me.

http://download.oracle.com/docs/cd/B...htm#PFGRF02410

There are also some 3rd party books. Cary Milsap has a good one.

It's also worthwhile to trace to find out exactly what is going wrong
with a particular SQL. Plenty online about that, including this
groups archives. http://www.psoug.org/reference/trace_analyzer.html

Quote:

Anything else going wrong, errors in the alert log?

Did not look at it yet. Will have to soon.

Oracle 10g 10.0.2.1.0 on Windows 2003 64 bit
No patch so far installed.

You probably want to look into dealing with that.

Just for grins, maybe you should check and see if you get a different
answer using the ANSI syntax for joins.

Example (from metalink Note:137286.1):

This query returns rows from the departments table even if no
employees are
assigned to it.

SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (e.department_id= d.department_id);

This is equivalent to the following pre-Oracle9i outer join notation:

SELECT employee_id, department_name
FROM departments d, employees e
WHERE e.department_id(+) = d.department_id;

jg
--
@home.com is bogus.
"I'd like to see a 'Bagpipe Heroes'" -
http://www.signonsandiego.com/uniont...s_1b19hot.html

#26

On Oct 19, 8:46 am, roman.moroku... (AT) googlemail (DOT) com wrote:

Quote:

I don´t like it too. In the real world I have written a conversion
program which does this on our application on the fly. There the
appropriate parameters gets defined and the program create either
the Oracle syntax, or the standard syntax, depending on database
in use.

Since Oracle supports both, couldn't you have saved yourself the
effort and just used ANSI joins everywhere? Or do you dislike the ANSI
style so much that you wrote a conversion utility to use wherever an
alternative exists? That's one hell of a personal preference.

#27

Hi Dieter,

Quote:

Is there really any DBMS (you're application deals with) which doesn't
support ANSI joins?

Yes. Oracle 9. But I am with you that most modern DBMSs are currently
support the standard. But wouldn´t the standard be much prettier
adopting
the Oracle style? (Never mind. I am just too used to it ;-))

Quote:

Watch out and check the expected result set, there are differences
between ANSI and proprietary outer joins (not only for Oracle but also
for e.g. MS SQL and DB2) as soon as you add some search conditions.
Whereas if you implement ANSI style the answer set will always be the
same on any DBMS.

Interesting.

Quote:

When you're too lazy, i'll do it for you, but only this time ;-)

SELECT *
FROM Foo A
LEFT JOIN Foo B
ON B.WP = A.WP AND B.Curr_Ms = A.Curr_Ms AND B.Value_Group = 21
LEFT JOIN Foo C
ON C.WP = A.WP AND C.Curr_Ms = A.Curr_Ms AND C.Value_Group = 22
LEFT JOIN Foo D
ON D.WP = A.WP AND D.Curr_Ms = A.Curr_Ms AND D.Value_Group = 23
LEFT JOIN Foo E
ON E.WP = A.WP AND E.Curr_Ms = A.Curr_Ms AND E.Value_Group = 24
LEFT JOIN Foo F
ON F.WP = A.WP AND F.Curr_Ms = A.Curr_Ms AND F.Value_Group = 25
WHERE
A.Value_Group = 11

Thanks. :-)

Regards
Roman

#28

Hi Dieter,

Quote:

Watch out and check the expected result set, there are differences
between ANSI and proprietary outer joins (not only for Oracle but also
for e.g. MS SQL and DB2) as soon as you add some search conditions.
Whereas if you implement ANSI style the answer set will always be the
same on any DBMS.

I don´t belive it. There are indeed different behaviours regarding the
proprietary outer joins from Oracle and the ANSI SQL standard. Thanks
for
the advice to test the ANSI style. This one returnes the right result
without
bringing the server down. (Problem described a few posts before.)

More over, the ANSI style executes 2 times faster. On the other side
there
seem to be a bug in the proprietary outer join processing of Oracle.

Now everything looks fine. I do once more thank all the people who
assisted
and helped me with their advices and knowledge on this topic.

Regards
Roman

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Multiple datasets in one dataset

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Multiple datasets in one dataset - 10-18-2007 , 05:41 PM

Re: Multiple datasets in one dataset - 10-19-2007 , 02:46 AM

Re: Multiple datasets in one dataset - 10-19-2007 , 03:08 AM

Re: Multiple datasets in one dataset - 10-19-2007 , 03:21 AM

Re: Multiple datasets in one dataset - 10-19-2007 , 07:03 PM

Re: Multiple datasets in one dataset - 10-21-2007 , 03:32 AM

Re: Multiple datasets in one dataset - 10-23-2007 , 04:03 AM

Re: Multiple datasets in one dataset - 10-23-2007 , 04:25 AM