Technical: Query Plan Problem in Oracle DB 9i
I have a set of PL/SQL functions building a 7M row table. The last function adds around 0.5M rows by gathering data from two existing tables (0.3M rows each) and from rows already placed in the table being built. The rows are added by a cursor which constructs the new row and writes one at a time.
I am using CBO, with stats on the two existing tables.
If there are NO stats on the table being built, the
code takes 4 mins (uses hash joins). If however there are stats on that table (taken when size was 5M rows), a very strange query plan is selected (as if table only contained one row - it uses a cartesian join) and code takes 9 hours. I would like to know what might cause the strange plan to be selected.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 20, 2005 10:13 AM
UPDATED:
January 23, 2005 1:47 AM
Answer Wiki:
Hi,
I wonder how can you conclude that the plan chosen is
"as if the table contained only one row ?".
A cartesian product occurs in any case when you use two
tables in the FROM clause, without joining them in the
WHERE clause, no matter whether you have statistics collected or not.
This depends solely on the SELECT statement itself.
Optimizer plans may indeed change when statistics change,
but they are generally supposed to improve, but there may
however exist cases where there are still things that the
optimizer can't know (for example, not having histograms
where they could be helpful).
Best rgds,
Iudith
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
