chuck

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you have a query that joins table t1 and view v1, and the query uses
an ORDERED hint, exactly what order will the optimizer join the tables
in? Will it use t1 as the driving table and join the tables in v1 in the
same order they're listed in the view's FROM clause? Or will the order
they're listed in the view be irrelevant? I was looking for
do***entation on this but didn't find any. The Performance Tuning Guide
only deals with the scenario where everything in the FROM clause is a table.

example:

select --+ordered
t1.cola, v1.colb
from t1, v1
where t1.colx = v1.coly

Oracle version in question is 9.2.0.5.

- --
To reply by email remove "_nospam"
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (MingW32)

iEYEARECAAYFAkMxs+kACgkQzIf+rZpn0oTpMQCghhfqwNRlRu CMsRUlbq3WYj3b
g7YAoIAckJ5D9L7CItTXsxq2lRZtFGRC
=Pgin
-----END PGP SIGNATURE-----

walt

My understanding is that views are basically a convienece for the user
and that the SQL parser replaces the view with the base tables before
proceeding any farther. Somewhat akin to preprocessing #include
directives in C before compiling.

For instance, if your view was

Creat view v1
CREATE OR REPLACE VIEW v1
AS select t2.colb, t2.coly
from t2, t3
where t2.id=t3.id

Your query will be parsed as if you actually submitted:

select --+ordered
t1.cola, t2.colb
from t1,t2,t3
where t1.colx = v2.coly
and t2.id=t3.id

Any statistics or indicies on the base tables will be used to generate
the execution plan for the query.

In this case, the ordered hint *should* cause the optimizer to join the
tables in the order that they are listed in the view. DISCLAIMER: I
haven't tested it.

//Walt

william robertson

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/hintsref.htm#PFGRF50104

The outcome depends on whether the view can be merged, among other
things.

cybotto

It depends how often the data is updated and how often the query runs.

In a similar situation for me, DB is updated once a day, lots of
queries during the day a materialzed view helped.

Create a materliazed view on top without changing anything on the query
and update this MV in sensitive intervalls. When it's stale it will
pick above query when not it will do a full scan on the MV with only
two colums, instead of t1 (some columns) and than a hash join with a
view with maybe lots of columns and maybe lots of joins.

chuck

"The ORDERED hint causes Oracle to join tables in the order in which
they appear in the FROM clause."

But what if not all the tables are in the FROM clause of the query that
has the ORDERED hint on it? Like in my example where one of the "tables"
is actually a view. It doesn't specify how what the hint does with
tables brought into the query by a view. That's what I was hoping to
find some officially do***ented word on. Maybe I should ask Tom. :-)
--
To reply by email remove "_nospam"

cybotto

You will need to spike the view with hints so it's not behaving
unpretictable. tom will tell not to use hints and let Orcales CBO sort
it out. You are looking for a hash join but then with ORDERED is
exaclty what to do when not to use them.

You can try to put the view as full sql statement as inline view and
spike it with hints or not and see what is comming out.

The query is begging for a full table scan on t1 and maybe on some
others in the view as result. Once CBO ist starting with it will do all
sorts of stuff and forget about it straight away.

For what this excersise is about by the way?

chuck

It's for a custom HR benefits query on a Peoplesoft database.

What I really want is not table scan and hash join, but index scan and
nested loops. CBO is picking a Cartesian join even though there's no
missing joins. I tried FIRST_ROWS first but still got the Cartesian.
When I tried ORDERED I got a very good execution plan for this query. It
went from 6 seconds to 50ms, and from 350k consistent gets to just over 100.

Before I go asking the developers to change the query, or create a
stored outline I want to try increasing optimizer_max_permutations. It's
at the default. I've already tweaked all the other optimizer_*
parameters to obtain good performance overall. This one query is really
throwing the CBO for a loop though. And it's really not that complex.
It's one table and one simple view that joins 4 other tables. Why the
CBO is choosing a Cartesian join is unexplainable.

--
To reply by email remove "_nospam"

cybotto

One full table scann is often not allone. Using ORDERED clause with
some success but can have its draw backs when the execution plan is not
cast in stone. It can happen that suddenly is doing much worse after a
while. It's likely that the index is cached which makes it fast when
running the same query again.

SELECT /*+ ORDERED INDEX(t1 index_name) USE_NL(t1 v1) */ ...

Since with ORDERED it will use a nested loop on t1 and v1. It's not
very scalable and it's a little bit of a gamble what is happening
inside the view over time.

t1 will always have a full table scan without any hints. Maybe is worth
trying to change t1 to an IOT table, so index is already part of t1.