CODEWITHSUNDEEP
c#oracle-database
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10237

Is it superfluous to prepend the schema name to a table name in a query?

Both of these work in my app:

INSERT INTO PLATYPUS (Bla, Blee, Bloo, Blah) VALUES (:Bla, :Blee, :Bloo, :Blah)

INSERT INTO CRITTERS.PLATYPUS (Bla, Blee, Bloo, Blah) VALUES (:Bla, :Blee, :Bloo, :Blah)

...Is one way preferred over the other?

Upvotes: 0

Views: 234

Answers (4)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

After working on a number of systems that have used both implicit and explicit (I call them "hardcoded") schema references, I've found that in all cases, hardcoding the schema name in application code makes life more difficult.

This is why Oracle has synonyms.

The only time I hardcode schema names is in deployment scripts, e.g. when creating an object I want to explicitly state which schema the object should be created in.

It means that when the developers ask, "can we have a copy of our dev database in the same instance?", I can say, "no problem - give me a few minutes". I create a new schema, copy the tables etc. into it, then update their login user's synonyms to point to the new schema. Voila, two databases on one instance. However, if I let them hardcode a schema name in the application code, this becomes impossible because synonym translation doesn't get done.

Upvotes: 2

Bjarte Brandt
Bjarte Brandt

Reputation: 4461

The first statement in any Oracle application is explicitly to state which objects you will access:

alter session set current_schema = MYSCHEMA;

This way I can point my application to different schemas (databases) within ONE database server (instance). So never login as the schema-account. The schema-account should be locked, and only opened during ddl-changes (upgrades).

Let's say I am a broadcaster and I run several channels: RTL1, RTL2, RTL3, RTL4, etc... The same application can logon to different databases (Oracle term schema) within the database server

Now I run my application for RTL1:

alter session set current_schema = RTL1;
select * from top_stories;

Now I run my application for RTL2:

alter session set current_schema = RTL2;
select * from top_stories;

etc...

The #1 design flaw I see is the one-to-one relationship: application - database server. It makes administration, storage and backup a fulltime job. Oracle can run thousands of applications and databases/schemas on one database server.

But as always, it depends. Sometimes it makes sense to run one application on one database server.

So I try not to prefix and design my applications to be installed in any schema. If I need to access objects in other schemas, I make use of views and/or synonyms.

This approach has worked very well and I know what objects I am accessing by querying:

select sys_context('USERENV','CURRENT_SCHEMA') from dual;

Upvotes: 1

pyrospade
pyrospade

Reputation: 8078

If you are using multiple schemas, then no. It's all about context. Explicit is better than implicit.

Upvotes: 3

Chris Farmer
Chris Farmer

Reputation: 25386

They both work because you're probably logged in as a user whose default schema is CRITTERS. If you log in as another user when the default schema is something different, you will have to use the qualified form of your query.

Upvotes: 2

Related Questions