Q: What is the basic difference between ETL and ELT
approach?
A: ETL extracts and transforms data outside
the DB before loading, while ELT loads data into the DB first and leverages DB
power for transformations.
Q: Suppose you want to load a file from a Unix server or
another location to your database, which knowledge modules will you use?
A: I would use an LKM (Load Knowledge
Module) for file-to-SQL or file-to-DB transfer, and an IKM (Integration
Knowledge Module) to load into the target.
Q: Is there any other way apart from using the LKMs?
A: Yes, we can use DB links, external
tables, or ODI procedures with shell scripts to transfer files.
Q: Suppose you want to use the external table, what setup
is required?
A: We need to define the directory in
Oracle, create the external table pointing to the file, and grant proper
read/write permissions.
Q: If my file has a header and I want to read from row 2,
what can we do?
A: We can configure the SKIP_ROWS parameter
in the LKM or define the external table with the SKIP clause.
Q: What is the use of context in ODI?
A: Context allows us to run the same
mapping in different environments (Dev, Test, Prod) by switching logical to
physical schema mappings.
Q: Suppose I want to load data into two dev databases with
same structure, how can I do that?
A: We can create multiple targets in the
mapping or use multiple scenarios/packages to run the same load into both
databases.
Q: In an ODI procedure, what is the use of Tag option?
A: Tags are used to organize and categorize
procedures for easier management and searching.
Q: What is the difference between Command on Source and
Command on Target?
A: Command on Source executes on the source
technology to fetch data, while Command on Target executes on the target system
to load or transform data.
Q: How to use variable values from Command on Source in
Command on Target?
A: We can define variables in Command on
Source and reference them in Command on Target using ODI substitution method
(e.g. #VAR1).
Q: You have an employee table, delete where id=1 then
insert from temp. If insert fails, rollback delete. How?
A: We can use ODI transaction control or
wrap both delete and insert in a single procedure with commit/rollback
handling.
Q: How to execute a scenario from a package and pass
variables?
A: Use the OdiStartScen command in the
package and pass variables through the command options or ODI variables.
Q: What is the difference between KO and OK step in
package?
A: OK executes the next step only if the
previous one succeeds, KO executes the next step if the previous one fails.
Q: What are slowly changing dimensions (SCD)?
A: SCD handle changes in dimension data
over time – Type 1 overwrites, Type 2 adds history, Type 3 stores partial
history.
Q: How to implement SCD2 in ODI mapping?
A: Use the ODI IKM Slowly Changing
Dimension, which handles effective dates, surrogate keys, and inserts new rows
for changes.
Q: If active row is from 1-Jan-2025 to 9999 and new record
comes on 29-Sep, how to end old record on 28-Sep?
A: We update the end date of the old record
to 28-Sep and insert a new record starting 29-Sep using SCD2 logic.
Q: What is Change Data Capture (CDC)?
A: CDC captures only changed data instead
of full loads, improving efficiency. ODI supports CDC via journals and
subscribers.
Q: What is the role of subscriber in CDC?
A: Subscribers consume change data from
journal tables, allowing multiple consumers for different use cases.
Q: When executing mapping with CDC, does it select from
source or JV$/JV$D tables?
A: With CDC enabled, ODI selects from JV$
or JV$D change tables instead of the source table.
Q: Have you worked on Oracle table partitions?
A: Yes, partitions improve query
performance and manageability by splitting large tables into smaller pieces.
Q: Have you heard about optimization hints?
A: Yes, optimization hints are directives
in SQL that influence the optimizer’s choice of execution plan.
Q: What is the use of APPEND hint?
A: APPEND hint enables direct-path inserts,
bypassing buffer cache, improving bulk insert performance.
Q: What are the disadvantages of APPEND hint?
A: It generates more redo/undo, can lock
segments, doesn’t reuse free space, and may require parallel DML settings.
Q: Suppose I need to update 100k rows and commit every 10k.
How can we achieve this?
A: We can use PL/SQL loops with COMMIT
after every 10k updates, or ODI procedures with commit control.
Q: How to check if a query is taking more time today?
A: Check V$SESSION and V$SQL for elapsed
time, AWR reports, execution plan changes, or ODI operator logs for runtime
comparison.
Q: In Explain Plan, what are Nested Loops, Hash Join, Sort
Merge Join?
A: Nested Loops: row-by-row with index,
best for small tables. Hash Join: builds hash table, good for large tables.
Sort Merge: sorts and merges, good if data already sorted.
No comments:
Post a Comment