Re: New to Oracle: DDL in EXECUTE IMMEDIATE question

<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

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown