Re: 90GB table on Windows 2000

Sorry about leaving out the RELY all the way through - at least I remembered to include it at the end.

The 'bug' is presumably related to the point that RELY exists to help the optimizer understand relationships between data - and therefore get the estimate somewhere in the right ballpark.

Yes, I have done it, but the last time was on 8.1.6, on a Sun, loading 32 GB of data per day, and we had far more serious problems than just working out how to deal with dropping and exchanging partitions - most (if not all) of which have been fixed in 8.1.7.4

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





John Summers wrote in message
<5d76b757.0210290915.13bac9eb_at_posting.google.com>...


>Leave it to the guru to inspire confidence. It's actually not that

bad


>for me. In this case, I can control the loads into the warehouse. So,

>disables is not a big deal... I can make sure nothing comes in during

>the exchange/export.

> The catch you didn't mention is that I found not only do you have to

>leave the constraint in NOVALIDATE mode, I've had to use RELY as

well.


>Otherwise, I found a peculiar "bug" where some queries cause the

>optimizer to drop the ball.

> This is in 8.1.7.0... and from what I get from Oracle, they have no

>idea and it's probably not fixed.

>  Thanks... from what you've stated, I'll assume you HAVE NOT done

>this yourself on a large database. Not the answer I was looking for.

>john.

>

>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

news:<aomb57$1tu$3$8300dec7_at_news.demon.co.uk>...


>> John,

>>

>> It's slightly worse than you think.

>>

>> Despite comments by Daniel, some data

>> warehouse systems do run without constraints

>> (or constraints which are DISABLE RELY)

>> in order to get the optimizer to know how the

>> data hangs together, without the overhead of

>> maintaining indexes.  BUT they are data warehouse

>> which are only subject to batch loads, and the batch

>> code is supposed to prove that the constraints are

>> not needed.

>>

>> --

>> Regards

>>

>> Jonathan Lewis

>> http://www.jlcomp.demon.co.uk

>>