IBM Info Sphere Data Stage is a ETL Tool. The Huge Number of Applicants or Students or Members are can Try Data Ware Housing Jobs. A Very Few People Know about Data ware Housing or Data Stage Interview Questions. IBM Info Sphere Data Stage Interview Pattern for remaining people will not have any Idea about Interview for those Candidates we publishing Oracle Interview Questions, data base Interview Interview Questions,Data Stage Interview Questions, Data ware Housing Interview Questions,Interview Procedure, How To start Interview, ETL Data Stage Interview Questions to Know how questions are there and how to Answer the questions in simple ways. Data Ware Housing Interview Questions 2014 The Candidates who are Interested to work in IT Sectors this is a First Step to go.
Oracle & Unix Questions
Data stage Interview Questions is a basic Oracle, data bare,UNIX, SQL contains very Logically. For this type of Questions have very easy tricks and easy way to Explain Answers in Interview. The data ware Housing Interview Questions are given below to know how they Asked Questions and Presentation for Highly Competition for IT Sectors. Candidates are Advised Should Read each and every Question more than a times because if you Succeed in Data ware housing or Data stage Interview. Data Ware Housing Interview Questions & Oracle Interview Questions 2014 For all Candidates will have to become a Expert in Good Technical Knowledge and also have More Interview Questions in Data stage that is helpful for Interview.
Data Ware Housing Questions
Data Ware Housing Interview Questions 2014 Candidates will have Ability to work hard because there is no time to prepare the Interview. This Below data Stage Interview Questions , Oracle Interview questions are Very Useful to our Interview Panel. The IBM Info Sphere Data stage Interview Questions 2014 Candidates Should Know the logic and how to get Job in IT Sector, How to Enplane Answers in Interview Details are Given below. There is a way to know for that you have to Data Stage for Prepare Interview.
Oracle in Data stage Interview Questions with Answers :
Q : How strong you are in SQL& PL/SQL?
Ans :
1) I am good in SQL,I use to write the source qualifier queries for informatica mappings as per the business requirement.
2) I am comfortable to work with joins; co related queries, sub queries, analyzing tables, inline views and materialized views.
3) As a informatica developer I could not get more opportunity to work on pl/sql side. But I worked on PL/SQL to informatica migration project so I do have exposure on procedure, function and triggers.
Q : What is the difference between view and materialized view?
Ans :
| View | Materialized view |
| A view has a logical existence. It does not contain data. | A materialized view has a physical existence. |
| Its not a database object. | It is a database object. |
| We cannot perform DML operation on view. | We can perform DML operation on materialized view. |
| When we do select * from view it will fetch the data from base table. | When we do select * from materialized view it will fetch the data from materialized view. |
| In view we cannot schedule to refresh. | In materialized view we can schedule to refresh. |
| | We can keep aggregated data into materialized view. Materialized view can be created based on multiple tables. |
Q : Materialized View :
Ans : Materialized view is very essential for reporting. If we don’t have the materialized view it will directly fetch the data from dimension and facts. This process is very slow since it involves multiple joins. So the same report logic if we put in the materialized view. We can fetch the data directly from materialized view for reporting purpose. So that we can avoid multiple joins at report run time.
It is always necessary to refresh the materialized view. Then it can simply perform select statement on materialized view.
Q : Difference between Trigger and Procedure
Ans :
| Triggers | Stored Procedures |
| In trigger no need to execute manually. Triggers will be fired automatically. Triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table. | Where as in procedure we need to execute manually. |
Q : Differences between sub-query and co-related sub-query
Ans :
| Sub-query | Co-related sub-query |
| A sub-query is executed once for the parent Query | Where as co-related sub-query is executed once for each row of the parent query. |
| Example: Select * from emp where deptno in (select deptno from dept); | Example: Select a.* from emp e where sal >= (select avg(sal) from emp a where a.deptno=e.deptno group by a.deptno); |
Q : Differences between where clause and having clause
Ans :
| Where clause | Having clause |
| Both where and having clause can be used to filter the data. | |
| Where as in where clause it is not mandatory. | But having clause we need to use it with the group by. |
| Where clause applies to the individual rows. | Where as having clause is used to test some condition on the group rather than on individual rows. |
| Where clause is used to restrict rows. | But having clause is used to restrict groups. |
| Restrict normal query by where | Restrict group by function by having |
| In where clause every record is filtered based on where. | In having clause it is with aggregate records (group by functions). |
Q : Differences between stored procedure and functions
Ans :
| Stored Procedure | Functions |
| Stored procedure may or may not return values. | Function should return at least one output parameter. Can return more than one parameter using OUT argument. |
| Stored procedure can be used to solve the business logic. | Function can be used to calculations |
| Stored procedure is a pre-compiled statement. | But function is not a pre-compiled statement. |
| Stored procedure accepts more than one argument. | Whereas function does not accept arguments. |
| Stored procedures are mainly used to process the tasks. | Functions are mainly used to compute values |
| Cannot be invoked from SQL statements. E.g. SELECT | Can be invoked form SQL statements e.g. SELECT |
| Can affect the state of database using commit. | Cannot affect the state of database. |
| Stored as a pseudo-code in database i.e. compiled form. | Parsed and compiled at runtime. |
Q : Differences between rowid and rownum
ans :
| Rowid | Rownum |
| Rowid is an oracle internal id that is allocated every time a new record is inserted in a table. This ID is unique and cannot be changed by the user. | Rownum is a row number returned by a select statement. |
| Rowid is permanent. | Rownum is temporary. |
| Rowid is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed from a table. | The rownum pseudocoloumn returns a number indicating the order in which oracle selects the row from a table or set of joined rows. |
Q : How to find out duplicate records in table?
Ans : Select empno, count (*) from EMP group by empno having count (*)>1;
Q: How to delete a duplicate records in a table?
Ans : Delete from EMP where rowid not in (select max (rowid) from EMP group by empno);
Q: What is your tuning approach if SQL query taking long time? Or how do u tune SQL query?
Ans: If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.
it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.
If joining columns doesn't have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query it should give better performance and also needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using
ANALYZE TABLE employees COMPUTE STATISTICS;
If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like
- ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
(/*+ ALL_ROWS */)
- FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
(/*+ FIRST_ROWS */)
- CHOOSE
One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered. - HASH
Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
/*+ use_hash */
Hints are most useful to optimize the query performance.
No comments:
Post a Comment