Monday, December 21, 2015

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

Tuesday, December 15, 2015

Difference Between MyISAM And InnoDB - MySQL


MyISAM Advantage
1. Faster than InnoDB for search results
2. support Full-text indexing
3. When use "select" statement, gives faster results as compare to Innodb

InnoDB Advantage
1. Support for transactions (ACID: Atomicity, Consistency, Isolation, Durability).
2. Better "Row-level locking"
3. Foreign key constraints
4. Repair better incase of table corruption
5. Support Rollback abilities
6. Automatic crash recovery

MyISAM Disadvantage
1. Cascading delete/update not support
2. NOt support ACID compliance
3. When we use indexing, insert/update/delete become bit slow
4. Not Support Row Locking


InnoDB Disadvantage
1. Not support full text indexing (<5.6 mysql version)
2. Can't compressed
3. Search Slow as compare to MyISAM

Change table engine to MyISAM
ALTER TABLE `honnikery` ENGINE = MYISAM

Change table engine to innodb
ALTER TABLE `honnikery` ENGINE = innodb


MySQL Interview Question and Answers

1. What’s MySQL?
  MySQL the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers, that builds its business by providing services around the MySQL database management system.

2. Why use the MySQL Database Server?
  The MySQL Database Server is very fast, reliable, and easy to use. it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything.

3. What is the technical features of MySQL Server?
  The MySQL Database Software is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

4. What are the column comparisons operators?
  The = , ‹›, ‹=, ‹, ›=, ›,‹‹,››, ‹=›, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements.

5. How do you get the number of rows affected by query?
  SELECT COUNT (user_id) FROM users;

6. What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
You can only use the comparison operators = and ‹=›.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.

7. How do you return the a hundred books starting from 25th?
  SELECT book_title FROM books LIMIT 25, 100;

8. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
  SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8).

9. What is the default port for MySQL Server?
  The default port is 3306.

10. How would you select all the users, whose phone number is null?
  SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

11. What are ENUMs used for in MySQL?
  You can limit the possible values that go into the table.
CREATE TABLE months (month ENUM ’January’, ’February’, ’March’,); INSERT months VALUES (’April’).

12. What are the advantages of Mysql comparing with oracle?
  MySql is Open source, which can be available any time. Provides Gui with Command Prompt. Supports the administration using MySQL Admin,MySQL Query Browser.Oracle is best database ever in Software development.

13. What is the difference between CHAR_LENGTH and LENGTH?
  The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

14. How are ENUMs and SETs represented internally?
  As unique integers representing the powers of two, due to storage optimizations.

15. How do you change a password for an existing user via mysqladmin?
  mysqladmin -u root -p password "newpassword"

16. If the value in the column is repeatable, how do you find out the unique values?
  SELECT DISTINCT user_firstname FROM users;

17. Explain the difference between FLOAT, DOUBLE and REAL?
  FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

18. How do you get the current version of mysql?
  SELECT VERSION();

19. Is Mysql query has LETTERCASE?
  No.
Ex :
SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
SeLeCt vErSiOn(), current_DATE;

20. What is the LIKE?
  A LIKE pattern match, which succeeds only if the pattern matches the entire value.

21. Differentiate the LIKE and REGEXP operators?
  SELECT * FROM pet WHERE name REGEXP "^b";
  SELECT * FROM pet WHERE name LIKE "%b";

22. What are the String types are available for a column?
  The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

23. What is the REGEXP?
  A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested.

24. What is the difference between CHAR AND VARCHAR?
  The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare when you create the table.
The length can be any value between 1 and 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

25. How quoting and escaping work in SELECT QUERY?
  SELECT ‘hello’, ‘“hello”’,‘““hello””’, ‘hel‘‘lo’, ‘\‘hello’.

26. What is the difference between BLOB AND TEXT?
  A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.

27. How we get Sum of column?
SELECT SUM(*) FROM [table name];

28. How do you get current user in mysql?
  SELECT USER();

29. How would you change a table to InnoDB?
  ALTER TABLE name_file ENGINE innodb;

30. How do you concatenate strings in MySQL?
  CONCAT (string1, string2, string3)

31. what is difference between primary key and candidate key?
  Primary Key
- are used to uniquely identify each row of the table. A table can have only one primary Key.
Candidate Key
- primary key is a candidate key. There is no difference. By common convention one candidate key is designated as a “primary” one and that key is used for any foreign key references.

32. How do you get the month from a timestamp?
  SELECT MONTH(january_timestamp) from tablename;

33. What do % and _ mean inside LIKE statement?
  % corresponds to 0 or more characters, _ is exactly one character.

34. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
  999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

35. How do you get the current date in Mysql?
  SELECT CURRENT_DATE();

36. What is the difference between mysql_fetch_array and mysql_fetch_object?
  mysql_fetch_array(): - returns a result row as a associated array, regular array from database.
mysql_fetch_object: - returns a result row as object from database.

37. You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
  SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS();

38. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)?
  It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

39. How do you display the list of database in mysql?
  SHOW DATABASES;

40. How do you display the structure of the table?
  DESCRIBE table_name;

41. How do you find out which auto increment was assigned on the last insert?
  SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

42. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
  On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

43. How many drivers in Mysql?
There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are
PHP Driver
ODBC Driver
JDBC Driver
ado.net5.mxj
CAPI1PHP DRIVER
PERL DRIVER
PYTHON DRIVER
RUBY DRIVER
C WRAPPER

44. How do you run batch mode in mysql?
  mysql < batch-file >;
mysql < batch-file > mysql.out

45. What Storage Engines do you use in MySQL?
  Storage engines used to be called table types.
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employs different storage mechanisms, indexing facilities, locking levels and ultimately provides a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

46. Where MyISAM table is stored?
Each MyISAM table is stored on disk in three files.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension

47. Define Primary key?
  MYSQL allows only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself.A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.

Tuesday, December 8, 2015

MySQL Training MySQL Development and Administration

Work with any database
Use MySQL Workbench to work with a database and run SQL statements
Retrieve, add, update, and delete data from a database
Work with summary queries, subqueries, data types, and functions
Use MySQL Workbench to create an EER (enhanced entity-relationship) model for their database
Design and implement their own databases
Use MySQL to create stored procedures, functions, triggers, and events
Manage transactions and locking
Perform core administrative tasks, including monitoring, configuring, logging, and adding/removing users
Secure, back up, and restore a database
MySQL Training Outline

Introduction
An Introduction to Relational Databases and SQL
An Introduction to Client/Server Systems
An Introduction to the Relational Database Model
An Introduction to SQL and SQL-Based Systems
The SQL Statements
How to Use SQL From An Application Program
MySQL Workbench and Other Development Tools
An Introduction to MySQL Workbench
Using Workbench to Run SQL Statements
Using the Reference Manual
Command Line Client
How to Retrieve Data From A Single Table
An Introduction to the SELECT Statement
The SELECT Clause
The WHERE Clause
The ORDER BY Clause
The LIMIT Clause
How to Retrieve Data From Two Or More Tables
Inner Joins
Outer Joins
Other Skills For Working With Joins
How to Work With Unions
How to Insert, Update, and Delete Data
Creating Test Tables
Inserting New Rows
Updating Existing Rows
Deleting Existing Rows
Coding Summary Queries
Aggregate Functions
How to Group and Summarize Data
Coding Subqueries
An Introduction to Subqueries
How to Code Subqueries In the WHERE Clause
How to Code Subqueries In Other Clauses
Working With Complex Queries
The Data Types
Converting Data
Working With Data Types
How to Use Functions
String Data
Numeric Data
Date/Time Data
Other Functions You Should Know About
How to Design A Database
Design A Data Structure
Normalize A Data Structure
Using MySQL Workbench For Database Design
Creating Databases, Tables, and Indexes
Databases
Tables
Indexes
A Script That Creates A Database
How to Use MySQL Workbench
Character Sets and Collations
Storage Engines
How to Create Views
An Introduction to Views
How to Work With Views
Language Skills For Writing Stored Programs
An Introduction to Stored Programs
How to Write Procedural Code
Transactions and Locking
How to Work With Transactions
How to Work With Concurrency and Locking
How to Create Stored Procedures and Functions
Coding Stored Procedures
Coding Stored Functions
How to Use Workbench With Procedures and Functions
How to Create Triggers and Events
An Introduction to Database Administration
Database Administration Concepts
Monitoring the Server
Configuring the Server
Working With Logging
How to Secure a Database
An Introduction to User Accounts
Working With Users and Privileges
How to Use MySQL Workbench
How to Backup and Restore a Database
Strategies For Backing Up and Restoring a Database
How to Back Up a Database
Restoring a Database
Importing and Exporting Data
How to Check and Repair Tables
Conclusion

MySQL Training Topics.

Introduction
An Introduction to Relational Databases and SQL
An Introduction to Client/Server Systems
An Introduction to the Relational Database Model
An Introduction to SQL and SQL-Based Systems
The SQL Statements
How to Use SQL From An Application Program
MySQL Workbench and Other Development Tools
An Introduction to MySQL Workbench
Using Workbench to Run SQL Statements
Using the Reference Manual
Command Line Client
How to Retrieve Data From A Single Table
An Introduction to the SELECT Statement
The SELECT Clause
The WHERE Clause
The ORDER BY Clause
The LIMIT Clause
How to Retrieve Data From Two Or More Tables
Inner Joins
Outer Joins
Other Skills For Working With Joins
How to Work With Unions
How to Insert, Update, and Delete Data
Creating Test Tables
Inserting New Rows
Updating Existing Rows
Deleting Existing Rows
Coding Summary Queries
Aggregate Functions
How to Group and Summarize Data
Coding Subqueries
An Introduction to Subqueries
How to Code Subqueries In the WHERE Clause
How to Code Subqueries In Other Clauses
Working With Complex Queries
The Data Types
Converting Data
Working With Data Types
How to Use Functions
String Data
Numeric Data
Date/Time Data
Other Functions You Should Know About
How to Design A Database
Design A Data Structure
Normalize A Data Structure
Using MySQL Workbench For Database Design
Creating Databases, Tables, and Indexes
Databases
Tables
Indexes
A Script That Creates A Database
How to Use MySQL Workbench
Character Sets and Collations
Storage Engines
How to Create Views
An Introduction to Views
How to Work With Views
Language Skills For Writing Stored Programs
An Introduction to Stored Programs
How to Write Procedural Code
Transactions and Locking
How to Work With Transactions
How to Work With Concurrency and Locking
How to Create Stored Procedures and Functions
Coding Stored Procedures
Coding Stored Functions
How to Use Workbench With Procedures and Functions
How to Create Triggers and Events
Conclusion