Interviewing for a PL/SQL developer position ultimately will turn towards talking about database structures and objects. But most of us will begin to fall asleep if we have to answer some of the more basic questions about what a table is, what a column is, what an index is, etc. Moreover, I don't want to bore you with having to read through the more basic questions and answers. Instead, I thought it would be much more beneficial if I took from my experiences as a DBA / Developer those pieces of information I actually used and thought could help you spice up your studying for that next job interview.

Again, as you read these, especially if you're getting ready for an interview, remember that these questions are not hard and fast, many of them beg for further investigation and many of them you should try within your own sandbox if you've not ever encountered them before. Good luck. I hope these questions get you started down the right path.

1. Define "Normal Form" and give brief descriptions of the levels

Most shops want everyone to at least understand that there is some order in the universe and that "Normal Form" is what controls this within our databases. For most purposes this is correct so do brush up on what "Normal Form" is by reviewing these very basic definitions; realizing that there is more to each definition and there are higher levels then third normal form but this will be plenty for most interviews.

Normal Form - is nothing more than a set of criteria within relational database theory that aids in determining a table's degree of vulnerability against logical inconsistencies and anomalies.

First Normal Form - No repeating elements or groups of elements

Second Normal Form - No partial dependencies on the primary key

Third Normal Form - No dependencies on non-key attributes

2. Name some of the database views you might use to find the structure of a table or index

I don't know many developers that cannot rattle off some of the database views to check database structures. Here, for tables and indexes, the most commonly used are the DBA, USER, or ALL variations of _TABLES, _INDEXES, _TAB_COLUMNS, _IND_COLUMNS.

3. IF you needed to extract the full table DDL for a table, how might you do that?

This is where it gets a bit tricky and many developers might start rattling off the various database tools out there that can reverse engineer database structures. This is good and many shops use these tools but if your interviewer is looking for a developer bent a little on the DBA side you may want to impress them by rattling off a few of the DBMS_METADATA functions such as GET_DDL, GET_DEPENDENT_DDL, GET_GRANTED_DDL, etc. These DBMS_METADATA functions can easily extract the DDL for various objects quite nicely.

As an added bonus to this simple DDL extraction, impress the interviewer again by telling them that you also are able to compare schemas by using the DBMS_METADATA_DIFF package; providing great insight into differences in dev, test, QA, and production environments before migration of an application.

4. How might you quickly and easily "de-attach" "attach" indexes from tables to help aid in determining their usefulness?

Oracle has a feature called INVISIBLE indexes that allow users/developers, to actually hide "de-attach" indexes from the objects they support. Through commands like ALTER INDEX <index_name> INVISIBLE; we don't have to drop indexes and recreate them when we want to use them, check their validity. The indexes are still around when invisible, are maintained through INSERTs, UPDATEs, and DELETEs but just not used in determining access paths by SQL or applications. An invisible index is not seen by the optimizer and won't be used for any execution plans unless there is a hint specified, the session is set to use invisible indexes, or if the database is set to use all invisible indexes. This provides us with some fantastic opportunities. With the use of a new init.ora parameter called optimizer_use_invisible_indexes you can toggle session or system wide use of invisible indexes--giving you a unique opportunity to test the effects of new indexes before completely moving into a production environment or even flip on indexes during end of month or yearly processing that would normally throw normal SQL off during any other time of the year.

5. How might we tell if an index is being used within our applications?

We are all aware that there are often hundreds of indexes cluttering up a database, often providing no benefit other than the fact of increasing INSERT, UPDATE, and DELETE times if having to be maintained. To determine if indexes are being used we could always extract all the SQL and their execution paths to determine if an index is being used but there is an easier way, good for developers prior to going production, to test indexes they might think are necessary. You can easily turn on the monitoring usage of an index by issuing the command "ALTER INDEX <index_name> MONITORING USAGE" and then query the V$OBJECT_USAGE view to see if it has been used during you application.

6. What is a "Nested Table?"

I'm confident we all know what a normal table is. A nested table is nothing more than a normal table that has a column whose datatype is another table, a table within a table (nested), a column that is of type table, or a column that has for its values another table. This is much like having a "relationship" defined directly inside the table, unfortunately eliminating the ability of other tables to also creating a relationship without going through the parent data.

7. What is a table function?

A table function allows you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to a table would. The added benefit here is to have a table function perform some transformation to the data in question before it is returned in the result set.

8. Have you ever heard of the FIPS flagger?

FIPS stands for Federal Information Processing Standards and aids in giving us a method for identifying / flagging nonconforming SQL elements or conforming SQL elements against the ANSI format or syntax rules, helping developers to produce portable applications.

9. An interview about database objects wouldn't be complete unless there were a couple of SQL examples to help / determine if you know how to extract data from them.

While this is a very important section, I'm not going to bog down the article with examples. There are many out there on the Net. Just we aware that you DO need to understand how to not only create relationships between database objects but you should be able to query them as well. It is not uncommon for an interviewer to give an actual test here, giving you a set of un-related tables, ask you to create the relationships, identify the indexes, and generate some SQL in response to business questions about the data.

These are the "top" nine questions that I'd ask, AND STUDY, for a PL/SQL developer position. They are again not hard-n-fast questions and if I was the interviewer I'd most definitely jump off in a few questions and try to get insight into the actual code you might have written that relates to the individual questions. Be sure to have one or two interesting implementations, projects, or code that you've actually been involved in so that you can add some color.

Related Articles

Oracle DBA Job Interview - Nailing a Fairly Common Question
Interview Tips for Aspiring Junior DBAs
Oracle Technical Interview
Oracle Technical Interview Questions Answered - Part 1
Oracle Technical Interview Questions Answered - Part 2

Previous

Back to DBAsupport.com