Namespaces

Can we able to create an index with the same name as the table name ?

Yes . Because the table and index resides in the different namespaces.

A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name. Objects in different namespaces can share the same name.

These object types all share the same namespace:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types

So  it is impossible to create a view with the same name as a table; at least, it
is impossible if they are in the same schema.

These object types each have their own namespace:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions
Thus it is possible for an index to have the same name as a table, even within the
same schema.

SQL> select namespace from v$librarycache;

NAMESPACE
———————————————
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE
JAVA SOURCE
JAVA RESOURCE
JAVA DATA

11 rows selected.

Here there is an small test:

SQL> create table t1 as select *  from user_objects;

Table created.

SQL> create index t1 on t1(object_id);

Index created.

SQL>  CREATE OR REPLACE package t1 as (ENO VARCHAR2);
2  /
CREATE OR REPLACE package t1 as (ENO VARCHAR2);
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here we are getting the error because the packages and tables resides in the same namespace.

Advertisements

Tracing a web application in Oracle

In a busy production environment with many active users, tracing a SQL session is time-consuming and complicated,
because processing SQL statements in any multitier system that uses a connection pool can span multiple processes,
or even different instances.

With Oracle Database 10g, Oracle rationalizes SQL tracing through a new built-in package, DBMS_MONITOR,
which encompasses the functionality of previously undocumented trace tools, such as the DBMS_SUPPORT package.
Now you can easily trace any user’s session from beginning to end—from client machine to middle tier to back end—and generate trace
files based on specific client ID, module, or action.

Now you can add your own reference to the trace file for your easy identification,

alter session set
tracefile_identifier =”webapp”;

Trace files generated by this command have the string value with “webapp”

The DBMS_MONITOR package has routines for enabling and disabling statistics aggregation as well as for tracing by session ID,
or tracing based upon a combination of service name, module name, and action name.

PL/SQL developers can embed calls into their applications by using the DBMS_APPLICATION_INFO package to set module and action names

The service name is determined by the connect string used to connect to a service. User sessions not associated with a specific service are handled by sys$users (sys$background is the default service for the background processes).
Since we have a service and a module name, we can turn on tracing for this module as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’test’, module_name=>’w3link’);

PL/SQL procedure successfully completed.

To Disable:

SQL>exec dbms_monitor.serv_mod_act_trace_disable(service_name=>’test’, module_name=>’w3link’);

PL/SQL procedure successfully completed.
Tracing For a particular session:
 
SQL> select sid, serial#, username
from v$session;

   SID     SERIAL#  USERNAME
     ——    ——-  ————
       103       4152  SYS
       107       2418  SYSMAN
       129         53  TEST
       130        561  HR
       141          4  DBSNMP
. . .
       168          1
       169          1
       170          1
15 rows selected.
SQL> exec dbms_monitor.session_trace_enable
(session_id=>129, serial_num=>53, waits=>true, binds=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(129);
PL/SQL procedure successfully completed.

Importance Of SQL Design

If the sql statement  is designed poorly, nothing much can be done by optimizer or indexes

Few are the well known rules

-Enabling indexes to eliminate the need for full table scans

-Avoid Cartesian joins

–Use UNION ALL instead of UNION – if possible

–Use EXIST clause instead of IN – (Wherever appropiate)

–Use order by when you really require it – Its very costly

–When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!

–Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0

– Avoid writing where is not null. nulls can prevent the optimizer from using an index

– Avoid calculations on indexed columns. Write WHERE amount > 26000/3 instead of WHERE approved_amt/3 > 26000