66. What is an OPTIIMIZER ?
OPTIMIZER is an utility used to
determine how to access data requested in the query by the USER or
APPLICATION PROGRAM. The output of an optimizer is EXECUTION PLAN.
67. How OPTIMIZATION is done by the
Oracle in case of a query ?
1] RULE based, and 2] COST based.
68. What is a] RULE based
optimization, b] COST based optimization ?
RULE based optimization USES A FIXED
SET OF RULES to determine how to access the data.
COST based optimization USES
STASTISTICS STORED IN THE DATA DICTIONARY WITH CERTAIN RULES to
determine how to access the data.
Two modes - a] ALL_ROWS, B] FIRST_ROW.
With the help of ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS /
FIRST_ROW, We can alter the modes of cost based optimizer.
69. The KEYWORD comes into the mind
immediately when we talk about security ?????? in ORACLE 7.0 ?
GRANT.
Syntax GRANT privileges(
SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX) ON object TO
user WITH GRANT OPTION;
70 What KEWORD is used to withdraw
the PRIVILEGE you have granted to other user ?
REVOKE
Syntax: REVOKE privileges ON
object FROM users;
71 What is SINGLE INSTANCE ?
A single instance can run on a single
machine.
72 What is MULTIPLE INSTANCES ?
A SINGLE MACHINE can run more than
one instance at a time. Each instance is connected to its own
database.
73 What is DISTRIBUTED PROCESSING ?
Different instances on different
machines can communicate with each other using DATABASE LINKS and the
DISTRIBUTED option. Oracle supports full two-phase commits which
means that inserts, updates and deletes can occur on REMOTE database
via a network running SQL*Net.
74 What is PARALLEL PROCESSING ?
The Oracle parallel server allows
muliple instances to share a single database on a shared disk system.
The instance can run on a parallel computer or on different computers
in a cluster.
75. Difference between SQL and PL/SQL
?
SQL is the ANSI industry standard
language, used to manipulate information in a relational database.
PL/SQL is the procedural language
extension to Oracle’s SQL language.
SQL PL/SQL
|
1. It is flexible, Powerful and
easy to learn.
2. It is a non-procedural language.
It
a] Processes set of records
rather than just one at a time and
b] Provides automatic
navigation to the data.
3. It provides commands for
avariety of tasks including :
a] Querying data
b] Creating,Updating and
Replacing objects and Inserting, Updating
and Deleting rows.
4] All RDBMS supports SQL
Thus one can transfer the
skills gained with SQL from one
RDBMS to another.
Programs written in SQL are
portable, they can often be moved from one database to another
with little modification.
|
1. PL/SQL block can contain any no.
of SQL statements combined with the following :
a] Flow of control statements such as IF..THEN, ELSE, EXIT and GOTO.
b] Repetition statements such as
FOR .. LOOP and WHILE .. LOOP.
c] Assignment statements such as X
:= Y + Z
2. PL/SQL allows you to logically
group a set of statements and send them to the RDBMS as a single
block.
3. Procedural capabilities.
4. Improved performance.
5. Enhanced productivity
6. Portability
7.
Integration with the RDBMS.
|
76. How to fetch description of a code
in the base table block where code is a base table field and
the description is a non-base
table field ?
Use SELECT with INTO clause to fetch
the decription value into the NON-BASE table field.
77. What is the purpose of OUTER JOIN
?
An OUTER JOIN returns all the rows
returned by simple join as well as those rows from one table that do
not match any row from the other table. The symbol (+) represents the
outer join.
78. Difference between EQUI JOIN and
OUTER JOIN ?
EQUI JOIN returns rows from both the
tables provided they both have the same column_name in the
where clause. The symbol (=) represents the EQUI JOIN.
For OUTER JOIN see previous answer.
79. Define NORMALIZATION ?
NORMALIZATION is the process of
putting things right, making them normal. It is a part of analysis
necessary to understand a business, and build a useful application.
The normalization of data ensures
a] Minimization of duplication of
data.
b] Providing flexibility to support
different funtional requirements.
c] Enabling the model to be
translated to database design.
STEPS INVOLVED IN NORMALIZATION
1] Ensure that all the ENTITIES are
uniquely identified by a combination of attributes.
2] Remove repeated attributes or
group of attributes, to place the entities in the first
normal form.
3] Remove attributes that are
dependent on only part of the identifier.
4] Remove attributes that are
dependent on attributes which are not part of the identifier.
80. Define REFERENTIAL INTEGRITY ?
REFERENTIAL INTEGRITY is the property
that guarantees that values from one column depend on values from
another column. This property is enforced through integruty
constraints.
Referential integrity is the
automatic enforcement of referential constraints that exists between
a reference table and a referencing table. When referential integrity
is enforced , the value of a foreign key exists as a primary key
value in the reference table.
81. Explain OUTER JOIN with example ?
SELECT DEPT.DEPTNO,DNAME,JOB,ENAME
FROM DEPT,EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO(+) AND
DEPTNO IN (30,40)
ORDER BY DEPT.DEPTNO;
82. Explain with example how to use a
select statement with GROUP BY HAVING clause ? (or)
Where and when is the HAVING
clause used and what does it have ?
The HAVING clause is coded after the
GROUP BY clause in the query that is summarizing results by one or
more grouping columns. The HAVING clause behaves the same as
the WHERE clause except that it is
used to specify the conditions each returned group must satisfy. If
one row in the group fails the condition of the HAVNG clause, the
entire group is not returned as part of the result.
Ex: SELECT MAX(CUSTID), REPID FROM
CUSTOMER GROUP BY REPID
HAVING COUNT(*) > 2;
83. How do you TUNE SQL statements ?
Use OPTIMIZER HINTS for tuning Sql
statements.
84. What is the advantage of ENFORCE
KEY ?
ENFORCE KEY field characterstic
indicates the source of the value that SQL*FORMS
uses to populate the field
85. What is the Purpose of ERASE
command ?
ERASE removes an indicated Global
variable & releases the memory associated with it.