Star Schema Performance Problems?

We are having Oracle performance problems with a star schema.

We have a 16 million row fact table surrounded by a dozen small dimension tables. If we run a report query joining the fact table with several dimension tables that only restricts on the dimension tables, it looks like Oracle is trying to do multiple scans of the fact table. If we place the restriction on the fact table, then explain plan seems to do something reasonable. In one case, the select ran 25 times faster if the restriction was placed on the fact table.

Is this normal? Is there something I should know about Oracle and Star Schemas?

Thanks
Randy Eash Received on Mon Jun 26 2000 - 00:00:00 CDT