Informatica Reference

Archive for the ‘Oracle Interview Questions’ Category

An inline view is term given to sub query in FROM clause of query which can be used as table. Inline view effectively is a named sub query

Ex : Select Tab1.col1,Tab1.col.2,Inview.col1,Inview.Col2
From Tab1, (Select statement) Inview
Where Tab1.col1=Inview.col1

SELECT DNAME, ENAME, SAL FROM EMP ,
(SELECT DNAME, DEPTNO FROM DEPT) D
WHERE A.DEPTNO = B.DEPTNO

In the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view.

Inline views are determined at runtime, and in contrast to normal view they are not stored in the data dictionary,

There are lot of instances when we want data from sub query to available in main query, before oracle 7.3 only option was to create view and use that in sub query

Disadvantage of using this is

1.Separate view need to be created which is an overhead
2.Extra time taken in parsing of view

This problem is solved by inline view by using select statement in sub query and using that as table.

Advantage of using inline views:

1. Better query performance
2. Better visibility of code

Practical use of Inline views:

1. Joining Grouped data with Non grouped data
2. Getting data to use in another query

Advertisements

DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

An Implicit cursor is one created “automatically” for you by Oracle when you execute a query. It is simpler to code
An Explicit cursor is one you create yourself. It takes more code, but gives more control – for example, you can just open-fetch-close if you only want the first record and don’t care if there are others DBA_DATA_FILES.

INSTR function search string for sub-string and returns an integer indicating the position of the character in string that is the first character of this occurrence.

SUBSTR function return a portion of string, beginning at character position, substring_length characters long.SUBSTR calculates lengths using characters as defined by the input character set.

  • TABLES
  • VIEWS
  • INDEXES
  • SYNONYMS
  • SEQUENCES
  • TABLESPACES

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

  • Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
  • Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
  • Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
  • Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)