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.
January 20, 2005 10:13 AM
January 23, 2005 1:47 AM