Thursday, January 21, 2016

What's the difference between MyISAM and InnoDB?

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.
INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB



MySQL and Storage Engines
MyISAM and InnoDB are two popular storage engines. A storage engine (or "database engine") is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.

There are other engines available, namely CSV, MRGMYISAM, BLACKHOLE, MEMORY, ARCHIVE and PERFORMANCESCHEMA, each are more suited in certain situations more than others - there is no one engine that is best at everything.

Using multiple storage engines
But you do not have to choose one engine over another for the entire schema, it is perfectly valid to choose one engine for one table, and another engine for a different table, all within the same schema. Be careful in writing your application, however, as different engines will accept different operations.

Also, if you are hosting tables using different storage engines on the same server, the server’s memory resources will now have to be split between MyISAM and InnoDB

MyISAM vs InnoDB
Today we will focus on MyISAM vs InnoDB. The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-text searches.

Referential Integrity
Referential integrity ensures that relationships between tables remains consistent. More specifically, this means when a table (e.g. Listings) has a foreign key (e.g. Product ID) pointing to a different table (e.g. Products), when updates or deletes occur to the pointed-to table, these changes are cascaded to the linking table. In our example, if a product is renamed, the linking table’s foreign keys will also update; if a product is deleted from the ‘Products’ table, any listings which point to the deleted entry will also be deleted. Furthermore, any new listing must have that foreign key pointing to a valid, existing entry.

InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not.

Transactions & Atomicity
Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.

MyISAM do not support transactions whereas InnoDB does.

If an operation is interrupted while using a MyISAM table, the operation is aborted immediately, and the rows (or even data within each row) that are affected remains affected, even if the operation did not go to completion.

If an operation is interrupted while using an InnoDB table, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.

Table-locking vs Row-locking
When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.

When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

This feature in InnoDB is known as concurrency. As great as concurrency is, there is a major drawback that applies to a select range of tables, in that there is an overhead in switching between kernel threads, and you should set a limit on the kernel threads to prevent the server coming to a halt.

Transactions & Rollbacks
When you run an operation in MyISAM, the changes are set; in InnoDB, those changes can be rolled back. The most common commands used to control transactions are COMMIT, ROLLBACK and SAVEPOINT.
1. COMMIT - you can write multiple DML operations, but the changes will only be saved when a COMMIT is made
2. ROLLBACK - you can discard any operations that have not yet been committed yet
3. SAVEPOINT - sets a point in the list of operations to which a ROLLBACK operation can rollback to

Reliability
MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.

InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the reply of those logs.

FULLTEXT Indexing
InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4. As of the writing of this post, many shared hosting providers’ MySQL version is still below 5.6.4, which means FULLTEXT indexing is not supported for InnoDB tables.

However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL. Not that a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.

Conclusion
In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.


Tuesday, January 19, 2016

UTL_RAW Functions in Oracle with Syntax

UTL_RAW.TO_RAW


Syntax
result := PGM_UTL.TO_RAW(input);


UTL_RAW.BIT_AND

Syntax
result := UTL_RAW.BIT_AND(input1, input2);


UTL_RAW.BIT_COMPLEMENT

Syntax
result := UTL_RAW.BIT_COMPLEMENT(input);


UTL_RAW.BIT_OR


Syntax
result := UTL_RAW.BIT_OR(input1, input2);

UTL_RAW.BIT_XOR


Syntax
result := UTL_RAW.BIT_XOR(input1, input2);


UTL_RAW.CAST_TO_RAW


Syntax
result := UTL_RAW.CAST_TO_RAW(input);


UTL_RAW.CAST_TO_VARCHAR2

Syntax
result := UTL_RAW.CAST_TO_VARCHAR2(input);

UTL_RAW.COMPARE

Syntax
result := UTL_RAW.COMPARE(input1, input2[, pad]);


UTL_RAW.CONCAT


Syntax
result := UTL_RAW.CONCAT(input1, ...  input12);


UTL_RAW.CONVERT


Syntax
result := UTL_RAW.CONVERT(input, new_charset, old_charset);

UTL_RAW.COPIES

Syntax
result := UTL_RAW.COPIES(input, number);

UTL_RAW.LENGTH 

Syntax
result := UTL_RAW.LENGTH(input);


UTL_RAW.OVERLAY

Syntax
result := UTL_RAW.OVERLAY(new_bytes, input, position, length, pad);


UTL_RAW.REVERSE

Syntax
result := UTL_RAW.REVERSE(input);

UTL_RAW.SUBSTR


Syntax
result := UTL_RAW.SUBSTR(input, position[,length]);


UTL_RAW.TRANSLATE

Syntax
result := UTL_RAW.TRANSLATE(input, match, replace_bytes);


UTL_RAW.TRANSLITERATE


Syntax
result := UTL_RAW.TRANSLITERATE (input, replace_bytes, match, pad);


UTL_RAW.XRANGE

Syntax
result := UTL_RAW.XRANGE(start, end);

Monday, December 21, 2015

Cursors in oracle


Implicit cursors

Explicit cursors

Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:

Attribute Description
%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

Example:
We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.
If you check the records in customers table, you will find that the rows have been updated:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+
Explicit Cursors
Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;
Working with an explicit cursor involves four steps:

Declaring the cursor for initializing in the memory

Opening the cursor for allocating memory

Fetching the cursor for retrieving data

Closing the cursor to release allocated memory

Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:

CURSOR c_customers IS
   SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:

OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:

FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:

CLOSE c_customers;
Example:
Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota  
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

Oracle Hints

select /*+ index(customer cust_primary_key_idx) */ * from customer;


/*+ ALL_ROWS */

Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput .


/*+ CHOOSE */

Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement


/*+ FIRST_ROWS */

Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). It will also force the optimizer to make use of index, if available. There are other versions of FIRST_ROWS hints. This hint is useful in an OLTP environment when the user cannot wait till the last row is fetched. This is mainly used in Java lookup screens. If there are some calculations then this hint should not be used.


Test your PL/SQL knowledge, Which code runs faster?
/*+ RULE */

Explicitly chooses rule-based optimization for a statement block


/*+ AND_EQUAL(table index) */

Explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes


/*+ CLUSTER(table) */

Explicitly chooses a cluster scan to access the specified table


/*+ FULL(table) */

Explicitly chooses a full table scan for the specified table


/*+ HASH(table) */

Explicitly chooses a hash scan to access the specified table


/*+ HASH_AJ(table) */

Transforms a NOT IN sub query into a hash anti join to access the specified table


/*+ HASH_SJ (table) */

Transforms a NOT IN sub query into a hash anti-join to access the specified table


/*+ INDEX(table index) */

Explicitly chooses an index scan for the specified table


/*+ INDEX_ASC(table index) */

Explicitly chooses an ascending-range index scan for the specified table


/*+ INDEX_COMBINE(table index) */

If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate. If particular indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.


/*+ INDEX_DESC(table index) */

Explicitly chooses a descending-range index scan for the specified table


/*+ ROWID(table) */

Explicitly chooses a table scan by ROWID for the specified table


/*+ USE_CONCAT */

Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the

UNION ALL set operator


/*+ ORDERED */

Causes Oracle to join tables in the order in which they appear in the FROM clause


/*+ STAR */

Forces the large table to be joined using a nested-loop join on the index


/*+ DRIVING_SITE (table) */

Forces query execution to be done at a different site from that selected by Oracle


/*+ USE_HASH (table) */

Causes Oracle to join each specified table with another row source with a hash join


/*+ USE_MERGE (table) */

Causes Oracle to join each specified table with another row source with a sort-merge join


/*+ USE_NL (table) */

Causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table


/*+ APPEND */ , /*+ NOAPPEND */

Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.


/*+ NOPARALLEL(table) */

Disables parallel scanning of a table, even if the table was created with a PARALLEL clause


/*+ PARALLEL(table, instances) */

This allows you to specify the desired number of concurrent slave processes that can be used for the operation. DELETE, INSERT, and UPDATE operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION PARALLEL DML to enter this mode.)


/*+ PARALLEL_INDEX */

Allows you to parallelize fast full index scan for partitioned and non-partitioned indexes that have the PARALLEL attribute


/*+ NOPARALLEL_INDEX */

Overrides a PARALLEL attribute setting on an index


/*+ CACHE */

Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed


/*+ NOCACHE */

Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed


/*+ MERGE (table) */

Causes Oracle to evaluate complex views or sub queries before the surrounding query


/*+ NO_MERGE (table) */

Causes Oracle not to merge mergeable views

oracle pl sql developer roles and responsibilities

Created new procedures, functions, triggers, packages, and SQL*Loader scripts, and modified existing codes, tables, views, and SQL*Loader scripts
Incorporated business rules and constraints and created new tables for all modules.
Worked on claims processing reports on investigation and payment.
Generated claims report weekly, monthly and yearly.
Worked on reports to give parallel comparison of different-2 fiscal year of claims.
Fine tuned long running claims reports.
Developed expense reports to provide claims from accessing different-2 tables.
Wrote heavy stored procedures using dynamic SQL to populate data into temp tables from fact and dimensional tables for reporting purpose.
Tuned SQL queries to improve response time.
Wrote UNIX shell scripts to run SQL scripts daily.
Fine tuned SQL queries using hints for maximum efficiency and performance.
Developed Claims reports that would produce Excel outputs for users.
Developed a customer data warehouse to understand customer behavior to various Claims related programs and identify critical
customers in support of improved decision-making across the organization
Extensively worked in performance tuning of programs and processes.
Worked closely with Administrator for proper backup and recovery plans.
Monitored performance and changed performance requirements through application of Database tuning and performance optimization techniques.
Worked under tight schedules for successful development and implementation.

Thursday, December 17, 2015

List available listeners on a server:

$ ps -ef | grep listener | grep -v grep
  oracle 23879    1  0   Feb 24 ?  33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit
  oracle 27939    1  0 05:44:02 ?  0:00  /8.1.7/bin/tnslsnr listener_db2 -inherit
  oracle 23536    1  0   Feb 12 ?  4:19  /8.1.7/bin/tnslsnr listener_db3 -inherit
  oracle 28891    1  0   Mar 03 ?  0:01  /8.1.7/bin/tnslsnr listener_db4 -inherit