mzh

Understanding JOINING.(LEFT/RIGHT/FULL OUTER, Equijoins) /* create the customer table */ Create Table Customers ( CustNo Integer Not Null Primary Key, CustName Char (20), Address Char (40) ); /* create the orders table */ Create Table Orders ( OrderNo Integer Not Null Primary Key, CustNo Integer, OrderDate Date ); /* put some data into the customer table */ Insert into Customers Values (1,'P. Jones','Leeds'); Insert into Customers Values (2,'A. Chan','Hong Kong'); Insert into Customers Values (3,'K. Green','Columbia'); Insert into Customers Values (4,'B. Smith','Leeds'); Insert into Customers Values (5,'A. Khan',''); /* put some data into the orders table */ Insert into Orders Values (1,1,'24-JAN-96'); Insert into Orders Values (2,1,'31-JAN-96'); Insert into Orders Values (3,2,'04-FEB-96'); Insert into Orders Values (4,4,'12-FEB-96'); Insert

CREATING EXPLAIN PLAN FOR ORACLE SQL The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. Run This Script  To create Plan Table $ORACLE_HOME/rdbms/admin/utlxplan.sql Execute the SQL command like the following. SQL> explain plan for select * from ultimus.cor_trans_gl_hist where year=2009 order by branch_id; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1485638787 -------------------------------------------------------------------------------- | Id  | Operation          | Name              | Rows  | Bytes |TempSpc| Cost (% -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |                   |  2429K|   651M|       |   365K |   1 |  SORT ORDER BY     |                   |  2429K|   651M|  180

Compiling INVALID OBJECTS in database connect to the database. run oracle stored sctipts resided at ?/rdbms/admin/utlrp.sql --Find invalid objects using the following query: SQL> select * from dba_objects where status='INVALID'; SQL> @utlrp.sql --Again Find invalid objects using the following query: SQL> select * from dba_objects where status='INVALID'; ----------------------------------------------------------------------------