Re: New to Oracle: DDL in EXECUTE IMMEDIATE question

Brian Peasland wrote:
> <post snipped for brevity>
>
> > Does it make a difference whether a table, view, or stored procedure is
> > created with "EXECUTE IMMEDIATE"?
>
> Depends on what "difference" you are looking for. In the end, the
> database receives the CREATE command. You can issue this in SQL*Plus,
> SQL*Developer, a Pro*C application, through JDBC, or by using EXEC
> IMMEDIATE in a PL/SQL block. The database receives and executes the
> CREATE command. The object is created the same way no matter how it is
> received.
>
> > I'm not talking about executing the
> > DDL from within an application every time I need the object... I mean,
> > executing it once and only once... EVER. After the object is created,
> > when I then create an application that uses the previously (once only)
> > created object, does this object have poorer performance than those
> > created in a more customary fasion?
>
> I cannot see why it the object would have poorer performance. Oracle
> does not create a table differently if the CREATE TABLE command was sent
> via EXEC IMMEDIATE.
>
> But the proof is in the pudding. Why not create two tables. One with
> CREATE TABLE in SQL*Plus and one by using EXEC IMMEDIATE. Then populate
> the tables with the same data. Use DBMS_STATS to populate the exact same
> stats for that object. Then run the exact same queries (multiple times)
> on both tables to see how they both perform.
>
> HTH,
> Brian

That does help! Thanks! I couldn't imagine why an object would be created differently based on where the create command came from. I thought maybe he knew something that I didn't (which I would assume he SHOULD, considering he's the DBA and I've just been using Oracle for 2 days now).

Thanks to all of you. I now know how to proceed... as I have been. One day, I may have time to do performance testing, but I see no reason to do it at the moment (time constraints on my project and learning curve for doing the tests). Though I may be wrong, I don't see any good justification that my DBA was right on this one.

Thanks again! Received on Tue Aug 29 2006 - 11:25:24 CDT