Re: How bad can dynamic sql get?

You can also create a view on that partition. Every month when you need to move to another partition, drop and recreate the view, and don't change your queries at all.

But to answer your specific question - Others may have different experience, but I have used dynamic sql for some medium sized batch processing and it did not have any major impact on the process runtime.

jalexander_at_SummitSoftwareDesign.com
<sergey_s_at_my-deja.com> wrote in message news:8h9cjd$dcm$1_at_nnrp1.deja.com...
> 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 Sat Jun 03 2000 - 00:00:00 CDT