Implicit vs Explicit Cursors. Static vs Dynamic Cursors.

Life Cycle of DB Cursors. Implicit vs Explicit, Static vs Dynamic Cursors.

What are Database Cursors?

Oracle RDBMS assigns a private area to contain related information and the data returned or affected by a SQL statement when executed from a PL/SQL block. A Cursor is a mechanism of naming that private area so that the contents of which can be manipulated programmatically.

A Cursor in its simplest form can be thought of as a pointer to the records in database table or a virtual table represented by the result of a SELECT statement (in case of JOIN for example). A sample Cursor declaration/definition:

DECLARE

 CURSOR sample_cur /*... explicit cursor ...*/
 IS
 SELECT T2.description
 FROM SampleTable1 T1, SampleTable2 T2
 WHERE T1.id = T2.id

 /*...declaring a variable to hold data...*/
 sample_var VARCHAR2(50);

BEGIN

 /*... open the cursor if not opened already ...*/
 IF NOT sample_cur%ISOPEN
 THEN
  OPEN sample_cur;
 END IF;

 /*... fetching data from a cursor into a variable ...*/
 FETCH sample_cur INTO sample_var;

 /*... once finished using the cursor then close it ...*/
 CLOSE sample_cur;

END;

Once defined/declared a Cursor can be OPENed, FETCHed, and finally CLOSEd quite easily using the obvious looking commands. You can put the FETCH inside a loop if you wish to iterate through all the rows returned by the cursor. This of course holds true only for Explicit Cursors as these operations are (and can only be) performed implicitly by the PL/SQL engine for Implicit Cursors. Read more about the type of Cursors in the subsequent sections.

Life Cycle of a Cursor. How PL/SQL Engine executes a Cursor?

The PL/SQL Engine performs the following operations (either automatically or as specified in the program depending upon whether the cursor is implicit or explicit) during the entire life cycle of a database cursor:

• PARSE: making sure that the statement is valid and determining the execution plan. Implicitly done by the engine.
• BIND: associating values with placeholders in the SQL statement which is implicitly done by the engine for static SQL and needs to be specified explicitly for dynamic SQL.
• OPEN: acquiring memory for the cursor, initializing the cursor pointer and making the SQL statement ready to be executed. Implicitly done by the engine in case of an Implicit Cursor otherwise it's required to be specified for Explicit Cursors.
• EXECUTE: executing the SQL statement in the engine and seting the pointer to point just above the first row. Can't be specified explicitly.
  
• FETCH: retrieving the next row from the cursor's ResultSet. In case of an Explicit Cursor, this operation doesn't raise an exception/error when it reaches the end of the ResultSet. So you need to explicitly have some code making sure that the end of the ResultSet has reached.
• CLOSE: closing the cursor and releasing all memory used by it. Once close you can of course not use the cursor anymore. Implicit Cursors are automaticaly closed by the engine, but Explicit Cursors require the programmer to close them explicitly.

Implicit vs Explicit Cursors

For every INSERT, UPDATE, and DELETE in your code the PL/SQL engine creates implicit cursors and the developer doesn't require to create these cursors explicitly. In fact they can't create them explicitly even if they want to. Such cursors are called Implicit Cursors. If you have a SELECT statement returning only a single row then also an implicit cursors is created automatically. Implicit cursors are OPENed, FETCHed, and CLOSEd automatically and they don't offer any programmatic control.

For SELECT statements returning more than one rows you need to create cursors explicitly to iterate through all the returned rows/records. Such cursors are called Explicit Cursors. You can however create explicit cursors on those SELECT statement as well which return only a single row. It's advisable to do so if you're using PL/SQL Release 2.2 or earlier (read the reason below). But, if you're heavily re-using the same SELECT query then you would probably be better off with the implicit one for single-row SELECT statements.

Drawbacks of Implicit Cursors

• Less Control: Implicit Cursors can't be OPENed, FETCHed, or CLOSEd explicitly and hence they give less programming control.
• Less Efficient: In addition, they are comparatively less efficient than the explicit cursors (at least theoretically). The reason why it is slightly slow is because an implicit cursor runs as a SQL statement and Oracle SQL is ANSI-standard which requires a single-row query to not only fetch the first row, but also to perform a second fetch to ensure if more than one rwo will be returned by that query or not (in which case TOO_MANY_ROWS excepion will be raised by the PL/SQL engine). In case of an explicit cursor there would be only one FETCH in such a situation. But, this holds true only till PL/SQL Release 2.2 as in PL/SQL Release 2.3 implicit cursors got optimized and now they run slightly faster than the corresponding explcit cursor. In addition there are more chances that an implicit cursor would be re-used in the application and if that happens then the parsing time can be escaped as it might lie in pre-parsed state in the shared memory. So implicit cursors may enhace the overall performance of the application in case you're using PL/SQL 2.3 or later releases.
• Vulnerable to Data Errors: Less programmatic control on implicit cursors makes them more vulnerable to data errors as you can't really OPEN/FETCH/CLOSE them at will. For explicit cursors you may have custom validations in place and decide accordingly. Example: suppose you have a query returning only a single row and you are using an implicit cursor for that, but what if the same query returns morethan one rows in future. Implicit cursor will raise TOO_MANY_ROWS exception which may or may not like depending upon your application requirements.

Difference between Static Cursors and Dynamic Cursors

• A Static Cursor doesn't reflect data changes made to the DB once the ResultSet has been created whereas a Dynamic Cursor reflects the changes as and when they happen.
• A Static Cursor is much more performant than a Dynamic Cursor as it doesn't require further interaction with the DB server.
• A static cursor supports both Relative and Absolute Positioning whereas a Dynamic Cursor supports only Relative Positioning.
• A Static Cursor can be used for Bookmarking purposes as the data returned is static whereas a Dynamic Cursor can't be used for the same.

Static Cursor: A Database Cursor is called a Static Cursor if it captures the snapshot of data only at the time when the ResultSet (or Recordset in case of MS SQL Server) is created with no further DB interaction afterwards. And hence a Static Cursor will be unaware of any data changes made into the database after the ResultSet has been created. A Static Cursor facilitates scrolling through the static ResultSet and it supports both absolute and relative positioning. Reason being, the ResultSet is static and hence the cursor can always be sure of the position of all the records/rows. Relative positioning can be specified in terms of offsets from the current, top or bottom rows.

Dynamic Cursor: A Dynamic Cursor is the one which reflects all the data changes made into the database as and when they happen. This may require continuous re-ordering of data in the ResultSet due to the database changes and hence it's much more expensive than a Static Cursor. The possible re-ordering of records in the ResultSet doesn't allow a Dynamic Cursor to support absolute positioning as it can't be sure of the absolute position of a record/row in the ResultSet. Hence a Dynamic Cursor only supports relative positioning. Furthermore a Dynamic Cursor doesn't support Bookmarks for the obvious reasons.

Liked the article? You may like to Subscribe to this blog for regular updates. You may also like to follow the blog to manage the bookmark easily and to tell the world that you enjoy GeekExplains. You can find the 'Followers' widget in the rightmost sidebar.

XML Database - NXD, XAPI, Apache Xindice, Ozone DB, Sedna

XML Database - NXD, XAPI, Apache Xindice, Ozone DB, Sedna

XML Database - what's it?

XML Databse is just like any other database system with the only difference being the data is stored in XML format in this case. We have two major categories of XML Databases:-

• XML-enabled DB: Here the underlying database is one of the traditional databases only, but the database gets equipped with the ability of mapping the XML input into the format which the underlying DB can understand and it also takes care of mapping the returned output of the underlying DB into the corresponding XML output for the user. Evidently such an XML DB is different from any other traditional DB in only one sense - it handles the mappings automatically and the user of not bothered to do that in the middleware.
• Native XML DB (NXD): This category is also known as NXD. Databases belonging to this category has XML document as the fundamental unit of storage (the way we have row of a table as the fundamental unit of storage in a traditional relational DB). How the data is actually stored in the DB is of little concern (if at all) to the user and it's an implementation dependent stuff and it may not be in form of text files.

Why do we use XML Databases?

Very simple to answer this question... right? XML has become a norm for data transportation these days so you may have a requirement of directly dealing with XML Input and similarly you may need to return an XML Output. In such cases (which are increasingly getting more and more popular) XML DB can be of great help which avoids you to pay much attention to how the mappings actually take place.

XAPI - XML:DB API

Similar to JDBC ad ODBC in case of relational databases, we have XAPI for XML Databases which provide an implementation-independent access to the XML database.

Examples of XML Databases

• Apache Xindice - it is pronounced as (zeen-dee-chay). It's an example of a Native XML DB. It's basically a continuation of the project named 'dbXML Core' which was donated to the Apache Software Foundation in Dec, 2001. As already specified why NXDs are used, if you deal with XML Input and XML output then Apache Xindice is something you may find quite interesting. Needless to mention, like other Apache projects this is also Open Source and Free. Apache Xindice V1.1 released on May 09, 2007. Apache Xindice supports XAPI for Java Development and XML-RPC API for other languages. More details can be found here.
• Ozone Database - it's an Open Source, Java-based, Object-Oriented Native XML DBMS which is completely implemented in Java and hence it supports an application program to run directly in a transactional database environment. Ozone DB has a DOM Implementation (compliant to W3C) supporting the XML data storage and retrieval and you can use almost every XML tool to access the data. It also supports Apache Xerces-J and xalan-J. One important point to note here is that Ozone DB doesn't depend on any back-end DB or mapping technology as it contains its own clustered storage and cache system to store and manage persistent Java objects. More details can be found here.
• Sedna DB - it's another Native XML DB implemented in C/C++ which supports a wide range of XML applications including Content Management, Event-based SOA, etc. It's a full-fledged DBMS and it supports W3C compliant XQuery language for querying. This is also an Open Source DB and can be obtained for free under Apache Licence 2.0. It's now available for all popular platforms - Linux, Windows, FreeBSD, and Mac OS. More details can be found here.
• myXMLDB - another Open Source Native XML Db which has been implemented on top of MySQL and which uses BLOBs to store the XML documents with a maximum size of 256MB each. It has a GUI interafce and it has a Java implementation of XAPI. This XML DB is known for supporting huge XML documents. Since most of other well-known XML databases use DOM for storing an XML doc hence they can't support really huge XML docs. DOM is inherently very memory consuming and this is where myXMLDB takes the lead over the others. If your project requires some really huge XML Docs to be stored and accessed then you would probably be left with this option only. More details can be found here.

Apart from these Native XML databases, we have several XML-enabled Databases as well. Soem of them being MS Access 2002, IBM DB2, MS FoxPro, IBM Informix, Oracle, MS SQL Server, Sybase ASE 12.5, PostgreSQL, etc. A comprehensive list and details of all these XML Databases can be found here.

Liked the article? You may like to Subscribe to this blog for regular updates. You may also like to follow the blog to manage the bookmark easily and to tell the world that you enjoy GeekExplains. You can find the 'Followers' widget in the rightmost sidebar.

What is SQL Injection? How can it be handled?

What is SQL Injection? How can it be handled?

SQL Injection - what's it?

It's an attack where some malicious piece of code is added to a SQL statement which is later passed to a DBMS to be parsed and executed. If the overall SQL statement string is syntactically correct (and of course if the user has rights to fire those SQL commands) then the SQL gets executed by the SQL Engine and consequently makes the database inconsistent (to say the least otherwise such an effort may completely spoil the database as well).

Example: suppose you have a SQL statement string as specified below:

...
String parm = request.getParamater("parameterName");
String queryString = "SELECT * FROM table_name WHERE column_name = '" + parm + "'";
...

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(queryString);
...

Now, if the end user enters say "value'; DROP TABLE table_name --" as the parameter then the SQL query string will ultimately become "SELECT * FROM table_name WHERE column_name = 'value'; DROP TABLE table_name --'" which is valid SQL statement and it'll first fetch the results of the query and immediately after it it'll drop the table until you actually realize it. Notice the usage of '--' at the end of the parameter which mischievously makes the last "'" of the queryString ineffective. Anything following '--' is considered as comment in SQL ... right? Needless to mention here that ';' is an SQL statement terminator and it gives the attacker a luxury of terminating the expected SQL statement and fire his/her own malicious SQL statement.

Thus we see how easily one can add malicious but valid code to SQL statements and how terrible results can that produce for your application.

How can SQL Injection be handled?

Next obvious questions which comes to mind is - how can we effectively handle it? Can we really restrict it to happen altogether? I really doubt that we can claim 100% avoidance, but we can follow several measures to catch it beforehand and then accordingly we can deal with the situation.

As we can easily understand that as long as a SQL query is valid (and the user has right set of privileges) the execution of SQL queries can hardly be stopped without programmatic intervention both at the middleware level as well as the DBMS level. So how can we actually deal with such a situation? By following the same old rule of not relying on what comes as an input and by ensuring that all the user inputs go through a strict set of validations before they reach the DBMS only in the case they pass all the stages. Now deciding on how many validations the input should go through depends upon the foresight of the DBA/Developer and the success of SQL Injection will then depend upon how far can the DBA/Developer think ahead of the attacker.

Some Typical Validations for SQL Injection avoidance

All Inputs should be strictly validated without making any assumption about their

• datatype
• length
• format
• range

Strictly checking and rejecting inputs having avoidable characters like

• ; : the SQL query delimeter (we saw the impact above)
• -- : the SQL comment
• ' : data string specifier (it should be appended by the code)
• /*..*/ : comment delimeters can be used to fool the app to a great extent

Checking the SQL Parameters - as we know that Parameters collection inherently checks the type and length checks so it's better to use them while passing the parameters to the parametrized SQL statements whenever possible.

Checking Injection caused by Truncation - pay attention to the maximum length a variable can hold as rest of the characters in the assigned value will be trunctaed silently and that may cause severe damage in certain cases.

These are just few basic validations. Depending upon the actual DBMS you're using you can have various other validations before the SQL statement is permitted to be parsed and executed.

Liked the article? You may like to Subscribe to this blog for regular updates. You may also like to follow the blog to manage the bookmark easily and to tell the world that you enjoy GeekExplains. You can find the 'Followers' widget in the rightmost sidebar.

Physical DB Connections vs Logical DB Connections

Before moving on to the details of Physical DB Connections, Logical/Pooled DB Connections, and their implementations, you may like to go through the concept of Connection Pooling. Read the article - Connection Pooling - What's it & Why needed?

Physical Databse Connections

These type of DB connections are established either using the DriverManager (JDBC 1.0) class OR DataSource (JDBC 2.0) interface. In this case the connection is established to the DB directly at the time of use of the connection and as soon as the application finishes its interaction with the database, the connection object is destroyed and garbage collected.

Example: establishing a physical DB connection using DriverManager class (JDBC 1.0)

...
try {
Class.forName("driverClass");
} catch (ClassNotFoundException cnfe) {
System.err.println("Driver Class Not Found: " + cnfe.getMessage());
}
try {
Connection con = DriverManager.getConnection(dbURL, userID, password);
......
//...business logic implementation
con.close(); //... connection will be garbage collected
} catch (SQLException sqle) {
System.err.println("Exception while establishing DB Con: " + sqle.getMessage());
}
...

Logical/Pooled Database Connections

Logical or Pooled database connections are those connection objects which are created and maintained by the Connection Pool manager. The application requiring a connection to the database simply ask the Pool to provide them a connection object. The Pool either finds a free connection object or if all are exhausted then creates (of course, if the maximum limit of connection objects is not reached yet otherwise the request is queued) a new connection objects, registers it to the Pool and then gives that connection, which is finally used to serve the request. Similarly, when the request/application is done with the DB Connection then that is simply returned back to the Pool (and not destroyed or garbase collected as is the case with Physical DB Connections). The creation and destruction (garbae collection would probably be better term here) of all the connection objects of the Pool is maintained by the Pool Manager only and that's the main difference between the two. Otherwise, a logical connection is also internally a physical connection only, though it's an encapsulated one so that it can be managed by the Pool Manager.

Logical/Pooled DB connections are obtained using JDBC 2.0 Pooling Manager interfaces: javax.sql.ConnectionPoolDataSource and javax.sql.PooledConnection. ConnectionPoolDataSource works as a resource manager factory for the objects of type PooledConnection. The implementation of these interfaces are of course implementation dependent. All the J2EE compliant implementations just need to conform to the specifications and how they internally do is of little concern to the application developers.

Example: establishing a logical connection using JDBC 2.0 pooling manager interfaces

...
try {
Context context = new InitialContext(params);
ConnectionPoolDataSource cpds = (ConnectionPoolDataSource) context.lookup(poolSource);
...
//... setting parameters...
PooledConnection pc = cpds.getPooledConnection();Connection con = pc.getConnection();
...
//... business logic implementation...
con.close(); //... connection will returned back to the Pool
} catch (SQLException sqle) {
System.err.println("Exception while establishing DB Con: " + sqle.getMessage());
}

Calling close() on a PooledConnection object

Notice that in case of logical connections, the 'con.close()' will only return the connection object back to the Connection Pool whereas the same method when executed on a physical database connection object will cause the connection object to be garbage collected. The reason for this different behavior of the close() method on two Connection objects (acquired through different sources) is that whenever the Pool Manager creates a PooledConnection object, it calls the addConnectionEventListener on that object to register a ConnectionEventListener object with every PooledConnection object. Where does this object come from? Actually the pool manager itself implements the interface ConnectionEventListener and registers itself with every PooledConnection object. This registration causes the pool manager to be notified whenever any event occurs on any PooledConnection object. The close() call on a Connection object retrieved from a PooledConnection object (the Connection object acquired from a PooledConnection object is nothing but a handle to the actual PooledConnection object) simply notifies the pool manager that the application is done with the PooledConnection object and the pool manager simply deactivates the handle to that PooledConnection object and returns it back to the pool.

How & When a PooledConnection object actually gets destroyed?

A PooledConnection object gets destroyed only when the pool manager calls close() method on any such object because in that case close() method serves the normal process of destroying the object and not of just notifying some listener. Otherwise you need to develop a custom pool manager which doesn't really registers a PooledConnection object with a ConnectionEventListener object (i.e., the pool manager itself provided it implements this interface). In such a case the close() call will actually destroy the physical connection referenced by the PooledConnection object. But, this will obviously defeat the concept of Connection Pooling and therefore such an approach should be discouraged. It's better to go directly with physical connections in such a scenario.

EXPLAIN PLAN - what's it and how do we use it?

What does the EXPLAIN PLAN statement do in Oracle?

EXPLAIN PLAN statement when executed for a query displays the execution plan chosen by the Cost Based Oracle Otimizer. The execution plan of a SQL statement shows the sequence of instrcutions the Oracle Server will perform while executing that statement. This works for SELECT, INSERT, UPDATE, and DELETE all four statements. The beauty of EXPLAIN PLAN statement lies in the fact that it doesn't require the query statement to be run and this reduces the time taken for displaying the execution plan to a great extent, especially for long-running queries. This was a limitation with the AUTOTRACE statement and probably this is the reason why they have come up with the EXPLAIN PLAN statement as AUTOTRACE always requires the query to be run to completion (Remember TRACEONLY option of the AUTOTRACE command only suppresses the display of the result of the query, but the query is required to run to completion in that case as well).

DML or DDL?

EXPLAIN PLAN is a DML statement and hence you need to COMMIT the transaction explicitly as Oracle does not implicitly commit the changes made by DML statements.

What all you require to use it?

EXPLAIN PLAN statement requires you to have sufficient privileges like INSERT-privilege on the output table (in case you're specifying an output table to hold the execution plan... the default output table is PLAN_TABLE), EXECUTE-privilege to execute the SQL statement being used with the EXPLAIN PLAN, and if that SQL statement accesses any other Views/Tables (and if those Views/Tables subsequently access any other Vieews/Tables then for them as well... and so on) then you should have privileges to access all those Views/Tables involved directly or indirectly involved in the execution of the SQL statement for which EXPLAIN PLAN is being used.

Example: how do we use EXPLAIN PLAN?

EXPLAIN PLAN
FOR
SELECT * FROM TABLE_NAME1 T1, TABLE_NAME2 T2
WHERE T1.PK = T2.PK AND ... ;

The immediate output your SQL* Plus console will show is 'Explained.' and for viewing the execution plan generated by the EXPLAIN PLAN command, you can use utlxpls.sql (for parallel queries you should use utlxplp.sql).

What if the same plan table is used by many?

If the same plan table is being shared by many or if you are interested in maintaining the history of execution plans generated then you should use the STATEMENT_ID clause and this will attach the user specified ID with the particular execution plan. Now, you can easily retrieve all the plans by just mentioning the particular STATEMENT_IDs.

EXPLAIN PLAN
SET STATEMENT_ID = 'Statement Id #1'
INTO PLAN_TABLE
FOR
...SQL statement...;

EXPLAIN PLAN works fine for partitioned tables as well and in that case you can simply use the PARTITION_START, PARTITION_STOP, and PARTITION_ID columns of the PLAN_TABLE (if the default plan table is being used to capture the execution plan) to get the partitio-related information contained in the execution plan. Needless to mention that you always use pre-defined bind variables with the EXECUTION PLAN.

Note: This question was asked by one of our regular visitors, Amit. He has been very frequent in bringing up good questions. Keep it up Amit. Thanks for your contribution.

New features introduced by Oracle 9i

New features of Oracle 9i

Oracle 9i is having several new features and enhancements to expand the depth and usefulness of the previous release named Oracle 8i. Some of these new features are:-

• Dynamic Memory Pools - until Oracle 8i we required to stop and restart the Oracle Server after modifying the various memory pools including Shared Pool, Buffer Cache, Large Pool, etc. But, Oracle 9i reflects the changes without requiring to restart the server.
• Automatic Undo Management - Oracle 9i automates the process of Undo and now the use doesn't require to define, manage, and monitor the rollback segments. Instead the rollback segments are automatically created, monitored, sized, and deleted simply by defining the Undo tablespace.
• Auto Handling of Stabdby DB - for a completely automatic handling of standby databases.
• Flashback Query - this feature returns the results of the queries run against a database as they would have been produced at an earlier time and hence the name 'Flashback'.
• Concept of Clusters - this feature enables Oracle 9i database to be spread across multiple machines and hence resulting in a cluster of server. These servers can be used to extend the scalability and the availability of the cluster.
• XML data type introduced - this feature enables the XML documents directly into the database and thereby eliminating the need to parse the documents while storing into or retrieving from the DB.
• Partioning based on LOV - Oracle 9i introduced a new way to partition the data - by using a list of values, especially for the cases where partitioning is being done to isolate maintenance operations.
• Two-phase Rollback - Oracle 9i facilitates the database to be used soon after the roll-forward phase of the recovery gets completed and hence the use don't need to wait for the entire rollback to finish and thereby the wait time decreases.
• No Data Loss - Oracle 9i if configured for the 'ZERO Data Loss' feature will ensure that everything that is written to the local log file is also getting written to the log file of the standby DB. This makes sure that no data is lost in case of database crash as both the active DB as well as the standby DB always remain in the same state.
• TIMESTAMP Data Type family - Oracle 9i introduced this data type family to store Dates with Time up to 9 decimal places of a second. Members of this data type family are: TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.
• Server Parameter file - Oracle 9i introduced this file, which allows to maintain the initialization parameters in a binary server parameter file (created using CREATE SPFILE statement... don't edit this file manually in a text-editor). This file is also known as SPFILE. As the name suggests that this file resides on the machine where Oracle Database Server runs. The initialization parameters stored in this file are persistent and any changes made by using ALTER SYSTEM statements remain effective only for that particular server instance and during the next server startup again the same SPFILE is read and the server is initialized using the parameteres stored in that file only.
• Label Security - Oracle 9i introduced this feature to provide a comprehensive and highly customizable solution for row level security. This feature reduces the need for relying upon manual or physical controls to protect the data by labelling data and restricting access with a high degree of granularity. For example, if the same application is being used across organizations then sensitivity labels can be used to restrict the application users to one or few of the organizations only OR may be we can use the same approach to restrict access to the data to only a subset of users within an organization.
• External Tables - these are flat files stored outside the database that are treated by Oracle as tables. Data in such tables is read-only and no indexes can be created on these tables. No DML operation can be performed on these tables, but we can use them for query, join, and sort oparations. Two new views: DBA_EXTERNAL_TABLES and DBA_EXTERNAL_LOCATIONS have been introduced to administer the external tables.
• Explicit Defaults - The DEFAULT keyword can now be used to explicitly assign the column default values in INSERT/UPDATE statements. Prior to this, we would normally skip the default column.
• Several new functions introduced - Oracle 9i added several new functions including NULLIF (expression1, expression2): returns NULL if both the expressions are equal otherwise the base expression i.e., the first expression; COALESCE (expression1, expression2, ... expressionN): returns the first non-NULL expression in the list otherwise NULL.
• MERGE statement introduced - Oracle 9i added this statement, which implements Upsert functionality. It's used for conditional INSERT/UPDATE. Read more about this statement here.

Note: This question was asked by one of our visitors, Amit. Thanks Amit for your contribution. I hope this article helps you getting the answer to your question. Keep visiting/posting!

How to tune SQL Queries for better performance?

Performance tuning of SQL Queries

Note: One of our visitors, Amit asked this question by posting a comment. Thanks Amit for your contribution. Keep visiting/posting!

Performance of the SQL queries of an application often play a big role in the overall performance of the underlying application. The response time may at times be really irritating for the end users if the application doesn't have fine-tuned SQL queries. There are sevaral ways of tuning SQl statements, few of which are:-

• Understanding of the Data, Business, and Application - it's almost impossible to fine-tune the SQl statements without having a proper understanding of the data managed by the application and the business handled by the application. The understanding of the application is of course of utmost importance. By knowing these things better, we may identify several instances where the data retrieval/modification by many SQL queries can simply be avoided as the same data might be available somewhere else, may be in the session of some other integrating application, and we can simply use that data in such cases. The better understanding will help you identify the queries which could be written better either by changing the tables involved or by establishing relationships among available tables.
• Using realistic test data - if the application is not being tested in the development/testing environments with the volume and type of data, which the application will eventually face in the production environment, then we can't be very sure about how the SQL queries of the application will really perform in actual business scenarios. Therefore, it's important to have the realistic data for development/testing purposes as well.
• Using Bind Variables, Stored Procs, and Packages - Using identical SQL statements (of course wherever applicable) will greatly improve the performance as the parsing step will get eliminated in such cases. So, we should use bind variables, stored procedures, and packages wherever possible to re-use the same parsed SQL statements.
• Using the indexes carefully - Having indexes on columns is the most common method of enhancing performance, but having too many of them may degrade the performance as well. So, it's very critical to decide wisely about which all columns of a table we should create indexes on. Few common guidelines are:- creating indexes on the columns which are frequently used either in WHERE clause or to join tables, avoid creating indexes on columns which are used only by functions or operators, avoid creating indexes on the columns which are required to changed quite frequently, etc.
• Making available the access path - the optimizer will not use an access path that uses an index only because we have created that index. We need to explicitly make that access path available to the optimizer. We may use SQL hints to do that.
• Using EXPLAIN PLAN and TKPROF - these tools can be used to fine tune SQL queries to a great extent. EXPLAIN PLAN explains the complete access path which will be used by the particular SQL statement during execution and the second tool TKPROF displays the actual performance statistics. Both these tools in combination can be really useful to see, change, and in turn fine-tune the SQL statements.
• Optimizing the WHERE clause - there are many cases where index access path of a column of the WHERE clause is not used even if the index on that column has already been created. Avoid such cases to make best use of the indexes, which will ultimately improve the performance. Some of these cases are: COLUMN_NAME IS NOT NULL (ROWID for a null is not stored by an index), COLUMN_NAME NOT IN (value1, value2, value3, ...), COLUMN_NAME != expression, COLUMN_NAME LIKE'%pattern' (whereas COLUMN_NAME LIKE 'pattern%' uses the index access path), etc. Usage of expressions or functions on indexed columns will prevent the index access path to be used. So, use them wisely!
• Using WHERE instead of HAVING - usage of WHERE clause may take advantage of the index defined on the column(s) used in the WHERE clause.
• Using the leading index columns in WHERE clause - the WHERE clause may use the complex index access path in case we specify the leading index column(s) of a complex index otherwise the WHERE clause won't use the indexed access path.
• Indexed Scan vs Full Table Scan - Indexed scan is faster only if we are selcting only a few rows of a table otherwise full table scan should be preferred. It's estimated that an indexed scan is slower than a full table scan if the SQL statement is selecting more than 15% of the rows of the table. So, in all such cases use the SQL hints to force full table scan and suppress the use of pre-defined indexes. Okay... any guesses why full table scan is faster when a large percentage of rows are accessed? Because an indexed scan causes multiple reads per row accessed whereas a full table scan can read all rows contained in a block in a single logical read operation.
• Using ORDER BY for an indexed scan - the optimizer uses the indexed scan if the column specified in the ORDER BY clause has an index defined on it. It'll use indexed scan even if the WHERE doesn't contain that column (or even if the WHERE clause itself is missing). So, analyze if you really want an indexed scan or a full table scan and if the latter is preferred in a particular scenario then use 'FULL' SQL hint to force the full table scan.
• Minimizing table passes - it normally results in a better performance for obvious reasons.
• Joining tables in the proper order - the order in which tables are joined normally affects the number of rows processed by that JOIN operation and hence proper ordering of tables in a JOIN operation may result in the processing of fewer rows, which will in turn improve the performance. The key to decide the proper order is to have the most restrictive filtering condition in the early phases of a multiple table JOIN. For example, in case we are using a master table and a details table then it's better to connect to the master table first to connecting to the details table first may result in more number of rows getting joined.
• Simple is usually faster - yeah... instead of writing a very complex SQL statement, if we break it into multiple simple SQL statements then the chances are quite high that the performance will improve. Make use of the EXPLAIN PLAN and TKPROF tools to analyze both the conditions and stick to the complex SQL only if you're very sure about its performance.
• Using ROWID and ROWNUM wherever possible - these special columns can be used to improve the performance of many SQL queries. The ROWID search is the fastest for Oracle database and this luxury must be enjoyed wherever possible. ROWNUM comes really handy in the cases where we want to limit the number of rows returned.
• Usage of explicit cursors is better - explicit cursors perform better as the implicit cursors result in an extra fetch operation. Implicit cursosrs are opened the Oracle Server for INSERT, UPDATE, DELETE, and SELECT statements whereas the explicit cursors are opened by the writers of the query by explicitly using DECLARE, OPEN, FETCH, and CLOSE statements.
• Reducing network traffic - Arrays and PL/SQL blocks can be used effectively to reduce the network traffic especially in the scenarios where a huge amount of data requires processing. For example, a single INSERT statement can insert thousands of rows if arrays are used. This will obviously result into fewer DB passes and it'll in turn improve performance by reducing the network traffic. Similarly, if we can club multiple SQL statements in a single PL/SQL block then the entire block can be sent to Oracle Server involving a single network communication only, which will eventually improve performance by reducing the network traffic.
• Using Oracle parallel query option - Since Oracle 8, even the queries based on indexed range scans can use this parallel query option if the index is partitioned. This feature can result in an improved performance in certain scenarios.

Role of MERGE command? What's Upsert Functionality?

Question: Role of MERGE SQL command? What's Upsert Functionality?

Answer: Oracle 9i introduced a new SQL command named 'MERGE' and this command is used for implementing conditional INSERT and UPDATE commands in s single atomic statement. This functionality is commonly knwon as 'Upsert Functionality'.

This MERGE command comes very handy especially in the situations where on-line data requires the database to be refreshed periodically. The newly arrived data may require the existing rows of table(s) to be changed and/or new records to be inserted into the table(s) based on certain conditions. This is what MERGE was invented for.

Advantages of using MERGE

Prior to Oracle 9i, all such activities used to be performed using a series of DML statements or by using a PL/SQL program and both the approaches had performance issues for obvious reasons. The former required many data scans whereas the latter required the changes to be made in the database on a per-record basis. Usage of MERGE not only improves the performance in such scenarios, but also makes the overall functionality pretty simple.

Example: SQL statement using MERGE command to INSERT/UPDATE in a single atomic statement:-

MERGE INTO TABLE_NAME T1
USING ANOTHER_TABLE_NAME T2
ON (T1.KEY1 = T2.KEY1 AND T1.KEY2 = T2.KEY2 AND ...)
WHEN MATCHED THEN
UPDATESET COLUMN_NAME1 = NEW_VALUE_FOR_THE_COLUMN, ...
WHEN NOT MATCHED THEN
INSERT (T1.COL1, T1.COL2, ....) INTO VALUES (T2.COL1, T2.COL2, ..);

DDL, DML, DCL, and TCL - what are they used for?

DDL - Data Definition Language

SQL statements belonging this category are used to define the structure of the database, which is normally known as database schema. Few of the SQL commands belonging to this category are:-

• CREATE - used for creating database objects, such as tables, views, seq, indexes, etc.
• DROP - used for deleting database objects.
• ALTER - used for modifying the structure of database objects - structure not values.
• RENAME - used for renaming database objects.
• TRUNCATE - used for deleting all records from a table.
• COMMENT - used for adding comments to data dictionary

DML - Data Manipulation Language

As the name suggests, SQL commands belonging to this category are used for manipulating the data contained by the database objects, euch as tables, views, etc. Few of the SQL commands of this category are:-

• SELECT - probably the most commonly used SQL command. It's used for fetching the data from a database.
• INSERT - used for inserting new records/data in a table.
• UPDATE - used for updating the data within a table. The difference between ALTER and UPDATE is that the former is used for modifying the structure of database objects whereas the latter is used for modifying the data contained by a table (a database object).
• DELETE - used for deleting records from a table. The point to remember here is that this command doesn't remove the spaces of those deleted records. They still remain there. This is the difference between TRUNCATE and DELETE. Read more in this article - Difference between DELETE, TRUNCATE, & DROP >>
• MERGE - used for conditional INSERT/UPDATE. I'll post a separate post on MERGE. Please refer the article MERGE statement/Upsert Functionality >> for more details.
• CALL - as the name might suggest, this command is used for calling either a PL/SQL subprogram or a Java subprogram.
• EXPLAIN PLAN - used for describing the access path to the data. Read the details in this article EXPLAIN PLAN - what's it & how do we use it?
• LOCK TABLE - as the name suggest, this is used for locking a table and hence controlling the simultaneous access to the table.

DCL - Data COntrol Language

These commands are used for controlling the access to the data in the database by either granting the permission to access or by revoking the already granted permission. The SQL commands belonging to this category are:-

• GRANT - used for granting the permission to access the data.
• REVOKE - used for revoking the already granted pemission to access the data.

TCL - Transaction Control Language

These commands are used for controlling the state of the transaction they are fired from. Controlling a transaction includes capabilities of committing it, rolling back the changes, identifying the SAVEPOINTs to control the ROLLBACK, etc. The command belonging to this category are:-

• COMMIT - used for making the changes permanent.
• ROLLBACK - used for restoring the database to the previously COMMITted state.
• SAVEPOINT - used for identifying a point to which the transaction can be rolled back if required of course before the transaction gets COMMITted.
• SET TRANSACTION - used for modifying the properties of the current transaction like modifying the Isolation Level of the transaction.

SQL Query to find the lowest/top N records on a column

Question: SQL Query to find the lowest/topmost N records based on a column?

Answer: Other popular variants of this question are:-

SQL Query to find the lowest/highest 5/10/N employees/table based on Salary/Any Other Column?

SQL Query to find the bottom/top 5/10/N employees/table based on Salary/Any other Column?

Finding the lowest/bottom N records

There are several ways depending upon which version of Oracle DB are you using. For 8i and above you can use an 'inner query' to get the result. Prior to that version we used to have the same achieved by using a 'sub query'. Oracle 9i and above makes such tasks even more simple by providing RANK() and DENSE_RANK() functions.

Let's take the approach where we use an 'inner query' to get the result:-

SELECT * FROM (SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME)WHERE ROWNUM < N;

Here the inner query 'SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME' find all the records of the given table and orders them in ascending order based on the COLUMN_NAME. Now the external query selects all the rows having the ROWNUM less than 'N' out of the sorted resultset. Since, the inner query is sorting the records in the ascending order hance the records selected with 'ROWNUM <>

Using RANK() (or similarly DENSE_RANK()) function:-

SELECT * FROM (SELECT COL1, COL2, ..., RANK() OVER (ORDER BY COL2) COL2_RANK FROM TABLE_NAME)WHERE COL2_RANK <= N;

In this case, the RANK() function is based on the column named 'COL2' and the external query selects the records having COL2_RANK as 1 ... N. Since, the default sort order for RANK() (or DENSE_RANK()) function is 'ascending' hence the above query will return the bottom N records of the table named 'TABLE_NAME' based on the column named 'COL2'.

How to find the highest/top N records

Well... now it should be quite easy to find them. In all the above approaches, we simply need to add the keyword 'DESC' in the ORDER BY clause to have the ordered sequence in the Descending Order. Everything else will remain the same.

Difference between RANK() and DENSE_RANK() functions?

Question: What's the difference between RANK() and DENSE_RANK() functions?

Answer: Oracle 9i introduced these two functions and they are used to rank the records of a table based on column(s). The syntax of using these functions in SQL queries is 'RANK()/DENSE_RANK() OVER (ORDER BY )'. Here the ORDER BY clause decides which all columns (and in what order) will be used to group and rank the records.

The default sorting order is 'Ascending Order' and if we want we may specify 'DESC' to have the Descending Sort Order. The ranks start from 1 and not from 0.

Handling of NULL values

We may use either NULLS FIRST or NULLS LAST clause to specify the position of the NULL values in the ordered sequence. With Ascending Sort Order, if we specify NULLS FIRST clause then that means we want all the NULLs to be treated as smaller than the Non-NULL values and hence they are positioned right at the top above all the Non-NULL values whereas NULLS LAST clause would mean in this case that the NULLs are larger than all the Non-NULL values and thereby they would be positioned at the bottom of the sequence after the largest Non-NULL value. Exactly the reverse happens in case of Descending Sort Order.

Difference between RANK() and DENSE_RANK()

The difference between the two is that RANK() leaves gaps while ranking the records whereas DENSE_RANK() doesn't leave any gaps. For example, if we have more than one records at a particular position then RANK() will place all those records in that position and it'll place the next record after a gap of the additional records. But, DENSE_RANK() (which will also place all the records in that position only) will not leave that gap for the next rank. Suppose, we have a tie at the third position and there are 2 records with the same value then RANK() and DENSE_RANK() both the functions will place both the records in the third position only, but RANK() will have the next record at the fifth position (leaving a gap of 1 position) whereas the DENSE_RANK() function will have the next record at the forth position itself (no gap).

What is the difference between VARCHAR and VARCHAR2?

Question: What is the difference between VARCHAR and VARCHAR2?

Answer: Well... no difference as of now. Both the VARCHAR and the VARCHAR2 work exactly the same way (both are currently used for storing variable length character strings), but we should avoid using VARCHAR as it's a reserved keyword for future use and hence it may have a different meaning sometime down the line. And at that point the queries involving VARCHAR may required to be changed.

SQL Query to find every Nth row from a table?

Question: SQL Query to find every Nth row from a table?

Answer: There are many ways of getting it. One possible way is to use a subsequery, which uses the MOD function on ROWID and the result of the subquery is used by the external query to find every Nth row. The query will be something like:-

SELECT * FROM TABLE_NAMEWHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME);

Here the subquery 'SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME' will have the resultset having records of the form:-

(ROWID, 0)

(ROWID, 1)

(ROWID, 2)

............

(ROWID, N-1)

Now, the external query will pick only those records for which the subquery return (ROWID, 0) and these records will obviously be every Nth record as the MOD function will return (ROWID, 0) in all such cases.

SQL Query to remove duplicate records from a table?

Question: How to remove duplicate records from a table using SQL?

Answer: There may be quite a few possible ways of doing this, but the two most common ways are:-

Simpler and Faster approach - In this approach we simply create anothet table having only the distinct rows of the original table, drop the original table, and finally rename the new table to the original table. Voila! We're done. But, do remember that dropping a table will drop all the indexes and priviledges as well. So, you'll need to create them again.

CREATE TABLE NEW_TABLE AS SELECT DISTINCT * FROM ORIGINAL_TABLE;
DROP TABLE ORIGINAL_TABLE;
RENAME NEW_TABLE TO ORIGINAL_TABLE;
...create indexes/privileges on ORIGINAL_TABLE now...

The standard ROWID approach - it's the same approach where we simply compare the ROWID of the records having the same key values (duplicate records) and select only one of the duplicate rows - the one having either the min or the max ROWID. Don't worry, these ROWIDs are system generated and will never be duplicate, so you won't be having more than one min (or max).

DELETE FROM ORIGINAL_TABLE T1 WHERE ROWID > (SELECT MIN(ROWID) FROM ORIGINAL_TABLE T2WHERE T1.KEY = T2.KEY);

OR

DELETE FROM ORIGINAL_TABLE T1 WHERE ROWID < (SELECT MAX(ROWID) FROM ORIGINAL_TABLE T2WHERE T1.KEY = T2.KEY);

Here KEY represents the set of columns based on which we're deciding the duplicates.

Difference between DELETE, DROP, and TRUNCATE

Question: Difference between DELETE, DROP, and TRUNCATE SQL Commands?

Answer: Following are some of the differences between the three SQL commands:-

• DML vs DDL - The first obvious difference is that DELETE is a DML command whereas TRUNCATE and DROP both are DDL commands. As we know that we can rollback only a DML command and not a DDL command hence another inferred difference will be that DELETE can be rolled back whereas the TRUNCATE and DROP operations can not be rolled back. As with every other DML command, DELETE requires a COMMIT command to be executed to make the changes permanent, whereas TRUNCATE and DROP both being DDL don't need that. Changes will automatically get committed.
• Select Records vs All Records - DELETE can be used to delete either a select records (with WHERE clause selecting the set of records to be deleted) or all the records (either with no WHERE clause or with a WHERE clause selecting all) whereas TRUNCATE can not be used in conjunction with WHERE and it always deleted all the records of a table. DROP command not only deletes all the rows, but the entire table (or any other database object).
• Affect on Triggers, Privileges, Indexes, etc. - DELETE comand causes all the DELETE triggers on the table to be fired. TRUNCATE doesn't cause any triggers to be fired on the table and DROP also prevents any DML triggers to be fired on the table (or any other DB object). DROP command not only removes the table, but also removes all the indexes of the table, privileges defined for the table, and of course all the rows of the table and that too it removes them completely from the database. As stated above, no rollback is possible in this case.
• Performance - DELETE being a DML statement will certainly be slower than TRUNCATE (a DDL statement). One of the reasons being, DELETE needs to maintain the undo space for rollback operation, which TRUNCATE doesn't need. DROP can't really be compared with DELETE and TRUNCATE as it not only removes all the rows of a table, but also the table, its indexes and priviledges.
• Removal of spaces - DELETE command simply deletes the rows and doesn't free up that space occuppied by the deleted records. It will obviously result in fragmentation and we'll need to compact the table to get rid of it. This fragmentation makes the queries slower as the pointer navigates these free rows as well. TRUNCATE on the other hand removes the spaces occuppied by the records as well.

What's a database transaction?

What's a database transaction?

A database allows multiple users to use it concurrently and hence while a user is making some changes to data in the DB, some other user might be accessing the same data concurrently. This can lead to inconsistent data and hence such a situation should be managed with proper care. Most of the DBMSs use the concept of transactions to handle such a situation, which helps them to maintain data consistency as well as data concurrency.

A transaction is a basically a set of one or more SQL stataments, which will either execute as an atomic unit or will simply rollback and will leave the database as it was prior to the start of execution of the set of SQL statements of the transaction. If all the statements of the transaction gets executed as a stomic unit then the transaction ends with 'commit' statement which makes the changes to the database permanent,otherwise the changes are rolled back using the 'rollback' statement and the database returns to the state it was before the start of the transaction.

The database management system must have the capability to allow access to only one transaction to update a data item at a time otherwise the atomicity of the transaction will be violated and consequently the data will become inconsistent. The DBMSs use the concept of 'locks' to ensure this. A lock is a database object used to ensure that only one transaction gets access to manipulate a data item at a time. Every transaction requires to obtain the lock associated with the data item it intends to change and only after acquiring that lock it can proceed with the changes it needs to make on that data item.

For example: a table lock will not allow that table to be dropped unless an uncommitted transaction on that table gets committed/rolled-back. Similarly a row lock will prevent that row to be modified (or even accessed in required) while another transaction is still modifying that row. The row will become accessible to any other transaction when the transaction accessing it either gets committed or rolled-back.

What is a Relational Database? What're Integrity Rules?

Relational Database

It's a database which stores and manages the information in tables, which are composed of rows and columns. The name 'Relational' came from that fact that the tables in such a database are nothing but a collection of similar type of information stored in form of rows. This is the reason why a table is also referred to as a relation in such a DB.

Integrity Rules

These are the rules which a relational database follows in order to stay accurate and accessible. These rules govern which operations can be performed on the data and on the structure of the database. There are three integrity rules defined for a relational databse,which are:-

• Distinct Rows in a Table - this rule says that all the rows of a table should be distinct to avoid in ambiguity while accessing the rows of that table. Most of the modern database management systems can be configured to avoid duplicate rows.
• Entity Integrity (A Primary Key or part of it can not be null) - this rule says that 'null' is special value in a relational database and it doesn't mean blank or zero. It means the unavailability of data and hence a 'null' primary key would not be a complete identifier. This integrity rule is also termed as entity integirty.
• Referential Integrity - this rule says that if a foreign key is defined on a table then a value matching that foreign key value must exist as th e primary key of a row in some other table.

Difference between Client-side & Server-side DB cursors?

Question: Difference between client side and server side DB cursors?

Answer: There is nothing like client side DB cursor. What you may think of a clinet side DB cursor is actually the current row of either the ResultSet in case of JDBC or the Result in case of ODBC. DB cursor is only a server side entity and it resides and runs only on the server. Read about DB Cursors in this article - Life Cycle of Cursors, Implicit vs Explicit, Static vs Dynamic >>

Oracle Database - How to tune for better performance?

Oracle Database - How to tune for better performance?

Why to tune?

Simply to improve performace of the overall system by enabling the best possible resource utilization. The resource here includes all the resources involved including Software, Hardware, and Human Resources. Thankfully the Oracle Databse can be tuned to a great extent and it's probably one of the biggest responsibilities of a DBA to ensure whether the DB is well tuned or not.

When to tune?

Whenever the DBA discovers that the DB is not well tuned :-) There can be a veriety of
reasons why the DB may not look like to be well tuned. Few of them might be:-

• The system is not running at a pace matching with what your business requires
• The system is too slow to respond to the users and thereby wasting the precious human time
• The hardware are probably not being utilized properly and hence hardware cost is rising

How to tune it?

There are so many areas where the DBA can concentrate on for tuning the Oracle DB. Few of these areas are quire dependent on each other, so it's DBA's responsibility to ensure that focusing on one area doesn't produce some side effects to any other tunes area. So, it's important to focus on these areas in a proper order. For example, you can't jump on to SQL Tuning before having a fixed Database Design for the obvious reason that it's pretty difficult (if at all possible) to have a refined SQL statement without having a refined database schema. Some of the areas to focus are:-

• Design of the Database - the design of the database if of utmost importance and the DBA should be very sure that the current design is what he can think of as the best possible considering so many factors including normalization (or selective denormalization to improve performance), access paths, replication, etc.
• SQL Tuning - probably the second most important area to focus on. There are a variety of ways how the performance of a SQL query can be improved and the DBA should not leave even a single stone unturned :-) Poor SQL statements can cause lot of performance problems during peak hours.
• Memory Tuning - it's important to allocate sufficient memory to the various memory structures of the Oracle Instance. These memory structures are Shared Pool, Buffer Cache, Redo Log Buffer, Large Pool, etc.
• Input/Output Tuning - Disk I/O is probably the most time consuming stuff involved in the entire operation of the DB, so the DBA must consider all the possible scenarios involving Disk I/O and try reduce them to minimum possible.
• Connection Tuning - DB locks and latches must be reduced to the minimum and for the least time possible.
• OS Tuning - finally the DBA might focus on the underlying OS and see if it's in the right shape to ensure the proper execution of the Oracle DB installed on the system.
  

All types of database files used in Oracle DB

All types of database files used in Oracle DB

The physical structure of the Oracle DB consists of three file types, which are:-

• Datafiles - as the name suggests, these type of files store the data on disk.These files are almost always written only by the Mandatory Background process named DBWR (Database Writer). There are only a few exceptions when these files are written by some other system processes. From the logical structure of Oracle DB, we can visualize that Datafiles reside in the logical container of the tables and indexes. This container is called 'Tablespaces'.
• Control files - these are binary files and they store information about the database, such as name of the database. The control files contain so vital information that the Oracle DB won't start if these files are not available. Probably that's the reason why Oracle allows to maintain duplicate copies of control files. This process is called multiplexing of control files and it'll always be a good idea to keep duplicate copies on separate disks for obvious reasons.
• Redo Log files - these files are used to record all the changes made to the Oracle DB. These files comes very handy in situation like DB crash. Suppose you have scheduled periodic backup every 15 days and if the disk crashes on say 14th day then it may be quite difficult to recover the data in absence of these redo log files. Redo log files will be of so much help in this case that you will simply need to restart the database to recover all the lost data. These Redo Log files are online log files and Oracle DB requires at least two online Redo Log files. When one is full it switches to the other. You can have multiplexed Redo Log files as well and for that'll be handy if these online Redo Log files themsevles get corrupted.

In addition to these three types of files, Oracle DB uses many other types of files as well and each of these files have their own special uses. Some of these file types are:-

• Parameter file - this file is also called init.ora in Oracle DB. It contains the configuration details which are used for during startup of the database. Some of the information contained by this file is: the location of the Control files, the size of RAM the Oracle DB would be using, location of the trace files, etc. As you can easily understand that without these information, the database won't start. Oracle DB supports two types of parameter files: One, Manual parameter file which is also known as PFILE and Two, server side parameter file aka SPFILE.
• Trace files - Oracle DB creates these files in many cases including session failure, disk crash, etc. It can be created by issuing commands by users of Oracle DB as well.
• Networking configuration files - tnsnames.ora and listener.ora fall in this category of files. As the name suggests, these files are used to store the configuration details of the different network components used by the underlying Oracle DB.
• Oracle Binary files - these binary files contain the instructions to run the Oracle DB.