Re: EXPLAIN PLAN madness!

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3bf2ea7a.32307966_at_ausnews.austin.ibm.com...
> Subject: EXPLAIN PLAN madness!
>
> Platform: Oracle 8.0.5 SE, NT 4
>
> I pulled a heavy-hitting query from v$sqlarea to see if I couldn't
> improve it a bit. First thing I do is run an explain plan in both the
> production and test databases. This query is particularly nasty, with
> joins on 4 different local tables and 2 references to a synonym
> pointing to a remote database. To my surprise, the production and
> test plans were wildly different. The one in test had two separate
> groups of deeply nested outer loops, with full table scans of the same
> table at the bottom of each, and a computed cost of 12722. The
> production version had a single group of nested hash joins and a
> single scan of each of the 4 tables, with a computed cost of 5301.
>
> Confirmed that index structures are identical.
>
> Data volume is within 5% of each other with row count differences of
> less than 1000 on tables with total row counts of 15,000 to 25,000.
>
> Performed ANALYZE . . . COMPUTE on tables and all their indexes in
> both databases.
>
> Re-ran the explain plans and got the same results as before. After
> staring at them a bit something else jumped out at me . . . . the plan
> from the production db had a full table scan on a table that wasn't
> even mentioned in the plan from the test db. Double-checked the query
> and that table is very much in the FROM clause, and does have at least
> one column referenced in the SELECT column list.
>
> So now I have two questions:
>
> How do I explain these wildly different plans?
>
> How is it possible to generate a plan that doesn't reference a table
> used in the query?
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)

OK, is this a puzzle? A riddle?
I will take my crystall ball to bed with me and try to think up your explain plan and other details which are sadly missing from your post.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address