Technical: Query Plan Problem in Oracle DB 9i

pts.
Tags:
Oracle
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following