hi everyone,
i recently went to an oracle interview and was asked some questions i would like to share with you.
please feel free to share your answers.
1. if i have a file containing millions of rows,20 million for example, what is your approach on loading this file?
my ans: use sql loader with direct path load
2. after loading the 20 millions record in my staging tables, i want to enrich the data and insert into my presentation table.
what is your approach ?
my ans: split the data into slice and create multiple job that will process each slice of data
3. connect by query, assume the root node is in the middle of a data set. can you traverse up and down using one query or you need two query.
how?
my ans: use two queries
4. you have one file with 5 million rows and you load the file. next day the file comes with a little more than 5 millions and in addition some records were updated. i dont want to load the entire file again. how do i identify deltas and load only the deltas?
no answer. interviewer said answer could be either in oracle or unix. he mentioned using awk but dont know how that is done.
feel free to answer this
5. what is your approach on identifying why query is running slow?
my ans: look at the explain plan, do autotrace.
interview mention if i use the awr report and what to look for in awr.
feel free to post your approach
6. tell me a complex etl you implement
my ans: i mentioned something about duplicate but i know it was wrong.
7. if you compress a table with million of rows, how do you drop column?
my ans: set to unused. interviewer said he wants to drop the column and not set to unsed
please feel free to provide you answer to this question. it will be interesting to know other people's approaches. thanks