sybrandb (AT) hccnet (DOT) nl wa:
While I realize that you are a 10-year veteran of this group (at least,
so you've said) and probably get tired of answering the same questions
over and over again, it's still no excuse to be rude to people when
telling them to go use google instead of wasting your time. Especially
if you want to increase readership. Everyone has to start somewhere
before becoming an expert with something as complex as Oracle (DB,
AppServer, whatever), and being encouraging rather than derogatory is
more likely to get people to stick around after they stop being annoying
n00bs.


Most people just don't want to get involved in an obviously impending
flamewar over something theoretically off-topic by showing support for
someone who is willing to step forward and call another person out on
their rude behavior. So yeah, I agree with Barry. Sorry.

I've been subbed to these oracle groups for about a year now but up
until about a month or two ago have done nothing but immediately
'catchup' the groups without even reading. I'm not sure what got me
actually reading them recently, but it has been enlightening so far,
random bouts of crankiness aside.

--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator

Peter Kallweit (p_kallweit (AT) arcor (DOT) de) wrote:
: Hi %,

: I've a question about 'leading' the optimizer. To make it not too
: simple, it should work from 9.2.0.4 up to 11.1.0.6.

: I'm selecting from a heavy view (many joins, big result set), using a
: subquery to restrict the result:
: select *
: from heavyView
: where keys in (select keys from simpleView);

: In most cases the query starts with complete execution of the heavyView
: and is then restricting the result using the subquery. This results in a
: very bad performance.

: However, from the application logic I know, that the subquery returns
: only a few rows.
: Therefore I want the query to start with the subquery, and then going
: with the result rows into the main view.
: Indexes to support accessing the main view in this way are available.

: How can I enforce this execution order?

No idea, but there are various ways to structure your query. I would try
them all to see what works best.

syntax not correct

with simple as
( select ...
)
select * from heavy where clause using simple

select heavy.*
from heavy,simple
where heavy.key=simple.key

select
( select c1 from heavy h where h.k = s.k) C1 ,
( select c2 from heavy h where h.k = s.k) C2 ,
...
from
simple s


probably others, it may or may not make a difference.

Peter Kallweit (p_kallweit (AT) arcor (DOT) de) wrote:
: Hi %,

: I've a question about 'leading' the optimizer. To make it not too
: simple, it should work from 9.2.0.4 up to 11.1.0.6.

: I'm selecting from a heavy view (many joins, big result set), using a
: subquery to restrict the result:
: select *
: from heavyView
: where keys in (select keys from simpleView);

: In most cases the query starts with complete execution of the heavyView
: and is then restricting the result using the subquery. This results in a
: very bad performance.

: However, from the application logic I know, that the subquery returns
: only a few rows.
: Therefore I want the query to start with the subquery, and then going
: with the result rows into the main view.
: Indexes to support accessing the main view in this way are available.

: How can I enforce this execution order?

No idea, but there are various ways to structure your query. I would try
them all to see what works best.

syntax not correct

with simple as
( select ...
)
select * from heavy where clause using simple

select heavy.*
from heavy,simple
where heavy.key=simple.key

select
( select c1 from heavy h where h.k = s.k) C1 ,
( select c2 from heavy h where h.k = s.k) C2 ,
...
from
simple s


probably others, it may or may not make a difference.

Peter Kallweit (p_kallweit (AT) arcor (DOT) de) wrote:
: Hi %,

: I've a question about 'leading' the optimizer. To make it not too
: simple, it should work from 9.2.0.4 up to 11.1.0.6.

: I'm selecting from a heavy view (many joins, big result set), using a
: subquery to restrict the result:
: select *
: from heavyView
: where keys in (select keys from simpleView);

: In most cases the query starts with complete execution of the heavyView
: and is then restricting the result using the subquery. This results in a
: very bad performance.

: However, from the application logic I know, that the subquery returns
: only a few rows.
: Therefore I want the query to start with the subquery, and then going
: with the result rows into the main view.
: Indexes to support accessing the main view in this way are available.

: How can I enforce this execution order?

No idea, but there are various ways to structure your query. I would try
them all to see what works best.

syntax not correct

with simple as
( select ...
)
select * from heavy where clause using simple

select heavy.*
from heavy,simple
where heavy.key=simple.key

select
( select c1 from heavy h where h.k = s.k) C1 ,
( select c2 from heavy h where h.k = s.k) C2 ,
...
from
simple s


probably others, it may or may not make a difference.

Peter Kallweit (p_kallweit (AT) arcor (DOT) de) wrote:
: Hi %,

: I've a question about 'leading' the optimizer. To make it not too
: simple, it should work from 9.2.0.4 up to 11.1.0.6.

: I'm selecting from a heavy view (many joins, big result set), using a
: subquery to restrict the result:
: select *
: from heavyView
: where keys in (select keys from simpleView);

: In most cases the query starts with complete execution of the heavyView
: and is then restricting the result using the subquery. This results in a
: very bad performance.

: However, from the application logic I know, that the subquery returns
: only a few rows.
: Therefore I want the query to start with the subquery, and then going
: with the result rows into the main view.
: Indexes to support accessing the main view in this way are available.

: How can I enforce this execution order?

No idea, but there are various ways to structure your query. I would try
them all to see what works best.

syntax not correct

with simple as
( select ...
)
select * from heavy where clause using simple

select heavy.*
from heavy,simple
where heavy.key=simple.key

select
( select c1 from heavy h where h.k = s.k) C1 ,
( select c2 from heavy h where h.k = s.k) C2 ,
...
from
simple s


probably others, it may or may not make a difference.