How bad can dynamic sql get?

I have a partitioned table in Oracle data warehouse that contains about 2.5 billion rows. Once a month, I need to get rows out of it (for that month only) and use the result set in other operations. The partitions of the monster table are set up per month. So, I would like to select only from the partition for the given month. The select query has to be written once and run many times (so I cannot hard-code the partition name every time). However, I can use dbms_sql to build sql dynamically and execute it. The sql statement itself contains several joins to other tables, and I, of course, would like to be able to utilize the indexes where appropriate.

How inefficient can dynamic sql get? Does optimizer still try to figure out the best access path at parse time (using indexes and all)?

Each partition currently has between 60 and 80 million rows. One other joined table is about 3.5 million, and the other three tables are a few hundred rows each. There are some bitmap and normal indexes on the tables for the referenced fields. The environment is SMP (Sun).

Thanks a lot for any advice!

Sergey

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 02 2000 - 00:00:00 CDT