Oracle Query optimizer

We're in teh process of rolling out pc-based query tools to several hundred end users to allow them to do ad-hoc queries and generate reports without the need for application developers. The tables they'll be using are quite large (millions of records) on a system running Oralce 7.0 on HP/UX (if it's important). My experience with database engines led me to tell the users that the order of joins in the queries they generate is unimportant since the database engine has an optimizer that will re-write it in the most efficient way (determined by cost, time, rules, ...).

So now I have people telling me that it does make a difference. My initial reaction was to ask the db group if they run stats regularly on the tables, but I now here that Oracle doesn't have a runtime optimizer; that queries need to be compiled ahead of time. I'm having trouble believing that (even the Postgres database I've got running on my Linux system at home does runtime optimization). It will be a big problem with end users who need the performance of the optimizer but don't know what the query will be till they need it for a meeting in an hour to get a report out.

Have I misinterpreted something? If not, how do other people handle the growing world of ad-hoc query activity? Received on Thu Jul 07 1994 - 19:13:41 CEST