| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.theory -> Re: Functions and Relations
Aloha Kakuikanu wrote:
> vc wrote:
> > In Oracle 9i:
> > The nested loops plan does 15 times more work (30617 c.g. vs. 2216
> > c.g.) than the hash join. It's hardly "logically about the same amount
> > of work".
>
> This is because hash matching is done in memory and therefore is not
> reflected in the logical IO.
In Oracle, a logical get operation may obtain a block either from the disk cache or result in a physical disk access. So, no, the "logically gotten" blocks for the purpose of a hash join should be all accounted for.
>The matching itself is logically very
> similar, or is it not?
Well, it's just a join implementation so I guess they are similar, or you mean something else ?
>
> BTW, you omited the actual execution time difference. This is becuase
> it confiurms my 10 times heuristic, isn't it?-)
In a sort of way, yes, it does if you think that a 5 times difference in the execution time is OK. Here's some real table join:
select *
from
tu_c a, tu_p b where a.SEQ_FIN_TXN_ID = b.SEQ_FIN_TXN_ID
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 334959 189.76 277.10 283438 190958 0
5024367
Rows Row Source Operation
------- ---------------------------------------------------5024367 HASH JOIN
select /*+ use_nl(a,b) */ *
from
tu_c a, tu_p b where a.SEQ_FIN_TXN_ID = b.SEQ_FIN_TXN_ID
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 334959 241.60 1481.30 212586 15798901 0
5024367
Rows Row Source Operation
------- ---------------------------------------------------5024367 NESTED LOOPS
The NL elapsed time is 1481 sec vs. the SJ time of 277 sec.
>
> > That answers "why you think it'll be more performant" but does not
> > answer "How do you suggest to materialize 'f(x)' as a relation ?". So
> > how do you suggest to do the trick ?
>
> No, I objected to the method where we materialize the `y=f(x)`
> relation. I suggested a vague idea that we access this relation via
> index -- function invokation.
Can you make this idea a little bit less vague ?
>
> > > Are you aware that selectivity/cardinality estimation related to
> > > function invokations in SQL is finicky in today's RDBMS implementations?
> >
> > I am aware of the cardinality problem with 'select * from t1 where x in
> > (select x from table(function_1(a,b,c))' and its variations, but the
> > problem can be cured with the cardinality hint. It may not be
> > esthetically pleasing, granted, but what is the alternative ?
>
> Not only that. Can you specify card(A) = 10 * card(B)? How do you
> specify cardinality after some predicates are applied? How do you
> specify cardinality for a volatile (or temporaty) table that changes
> cardinality by orders of magnitude?
The last one is implemented by Oracle as "dynamic sampling". Not very well though.
>
> > One can
> > use a temp table too which perhaps essentially the same as your
> > suggestion (materializing a function as a temp relation). However,
> > there may be unexceptable overhead when using temp relations, or such
> > temp relation may be infinite and therefore impossible.
>
> The cost of such materialization should be evaluated apriory. Given
> that there is unavoidable selectivity/cardiality estimation errors, I'd
> even suggest a heuristics that explicitly prohibits such a
> materialization.
Received on Tue Nov 21 2006 - 19:43:27 CST
 |
 |