Author Archives: admin

set unused oracle

Posted on by | Leave a reply

On Large tables dropping a column is time consuming as it needs to work on lot of records.To prevent this you can perform a logical delete of the column by Using the SET UNUSED.

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);

Once this is done the column wont be visible to users.

Later you can run a query to delete unused columns from tables

ALTER TABLE table_name DROP UNUSED COLUMNS;

In case you are working with table with less number of rows we can use drop command to drop the table column which performs Physical delete rather than using SET UNUSED which drops the column later

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

Alter table to change Column Type

ALTER TABLE emp MODIFY ename varchar2(50);

java.sql.SQLException: Illegal hour value

Posted on by | Leave a reply

MySQL TIMEDIFF function runs well in MySQL but with JDBC it generate SQL Exception.
The problem is that the TIMEDIFF(expr1,expr2) function returns expr1 тАУ expr2 expressed as a time value.

This value is handled by java.sql.Time. But TIMEDIFF( , ) may return (for example) 12:45:00 or 40:30:01 as the case may be. For first value it works but for the second value it is not a proper time value according to java.sql.Time, hence the exception.

To get rid of this problem is to use concat as follows

CONCAT('',TIMEDIFF(expr1,expr2))

Now the returned value will be a String instead of a Time and which prevents JDBC from parsing it.

Checking for NULL Value MySQL

Posted on by | Leave a reply

A NULL is a value for which comparison yields neither a true nor false result.NULL = NULL is not true. It’s not false either. The outcome of any comparison of one value to a NULL value is a NULL result.

If you want to check a Column for NULL value then use the following query

Query1

SELECT IFNULL(ColName, '') AS Names
  FROM TableName

Query2

SELECT CASE WHEN ColName IS NULL
            THEN ''
            ELSE ColName
       END AS TableName 
FROM Test

Query3

 SELECT COALESCE(ColName, 0) as 'ColName' FROM TableName;

Query4

 SELECT IF(ColName IS NULL, '', ColName)
   FROM TableName

How to add Foreign key to MySQL Table

Posted on by | Leave a reply

If you already created a Table but you havent added foreign key constraint then you can do that by
running the following statement in MySQL

Run this before you run the FK Query Constraint 

SET FOREIGN_KEY_CHECKS = 0;

Run the FK Query 

ALTER TABLE tbl2Name 
  ADD CONSTRAINT fkConstraint FOREIGN KEY(fkcolumnName) 
      REFERENCES tbl1Name(pkcolumnName);

Run the below code again

 SET FOREIGN_KEY_CHECKS = 1;

Set

Posted on by | Leave a reply

Set

  • Set prevents duplication.The common use of set is to check for duplication
  • Since its is helpful for lookups for duplicate value HashSet provides an optimized implementation
  • If you want the result to be sorted use TreeSet instead of HashSet
Posted in Set.

ArrayList vs LinkedList

Posted on by | Leave a reply

Why ArrayList Faster than LinkedList during Random Access?
ArrayList has direct references to every element in the list, so it can get the n-th element in constant time. LinkedList has to traverse the list from the beginning to get to the n-th element.

Why LinkedList faster than ArrayList during Insertion/Deletion?
ArrayList is slower because it needs to copy part of the array in order to remove the slot that has become free. If the deletion is done using the ListIterator.remove() API, LinkedList just has to manipulate a couple of references; if the deletion is done by value or by index, LinkedList has to potentially scan the entire list first to find the element(s) to be deleted.

  • LinkedList takes constant-time insertions or removals. I can walk the list forwards or backwards, but grabbing an element in the middle takes time proportional to the size of the list.
  • ArrayLists allows random access, so I can grab any element in constant time. But adding or removing from anywhere but the end requires shifting all the latter elements over, either to make an opening or fill the gap. Also, if I add more elements than the capacity of the underlying array, a new array (twice the size) is allocated, and the old array is copied to the new one, so adding to an ArrayList is O(n) in the worst case but constant on average.
  • Iterating over both the Types is equally cheap.
  • If you have large lists, the memory usage is different. Each element of a LinkedList has more overhead since pointers to the next and previous elements are also stored

When should I use LinkedList?

  • When you need efficient removal in between elements or at the start.
  • When you don’t need random access to elements, but can live with iterating over them one by one

When should I use ArrayList?

  • When you need random access to elements (“get the nth. element”)
  • When you don’t need to remove elements from between others. It’s possible but it’s slower since the internal backing-up array needs to be reallocated.
  • Adding elements is amortized constant time (meaning every once in a while, you pay some performance, but overall adding is instantly done)
Operation Linked List Array List
Access O(n) O(1)
Insertion Access time + O(1) Access time + O(n)
Deletion Access time + O(1) Access time + O(n)

Iterator vs ListIterator

Posted on by | Leave a reply

When you are simple moving through List but you are not modifying the List object foreach is more efficient.In case you want to perform operations on each element of list individually taking out the element in such case use Iterator.

List<Fruits> arrFruits = new ArrayList<Fruits>();
Iterator<Fruits> itFrt = arrFruits.iterator();

while(itFrt.hasNext())
{
 Fruits frt = itFrt.next();
 System.out.println(frt);
}

ListIterator

  • While using Iterator in particular to List using a ListIterator is more powerful over Iterator.
  • ListIterator is bidirectional
  • It also keep track of Indexes of next and previous elements
  • It can replace last element it visited using set method
 List<fruits> arrFruits     = fruits.arrayList(5);
 ListIterator<fruits> itFrt = arrFruits.listIterator();

 while(itFrt.hasNext())
 {
   System.out.println(it.next()); 
   System.out.println(it.nextIndex()); 
   System.out.println(it.previousIndex()); 
 }

List Notes

Posted on by | Leave a reply
  • List maintains elements in Particular Sequence.
  • Two types of List 1.ArrayList and 2.LinkedList
  • ArrayList is faster while accessing elements but slower while Insertion and Deletion
  • LinkedList is slow in random access but faster during insertion and deletion

Operations in ArrayList

class Fruits
{	
  String Name = "Fruit";
}

class Apple extends Fruits
{	
 public Apple()
 {
   Name = "Apple";
 }
}

class Orange extends Fruits
{	
 public Orange()
 {
  Name = "Orange";
 }
}

class Mango extends Fruits
{	
 public Mango()
 {
  Name = "Mango";
 }
}

Adding Element – List.add()

List<Fruits> arrFruits = new ArrayList<Fruits>();
Fruits objApple  = new Apple();
Fruits objMango  = new Mango();
arrFruits.add(objApple);
arrFruits.add(objMango);

Removing Element – List.remove()

arrFruits.remove(objApple);

Removing by Index – List.remove()

arrFruits.remove(1);

Index of Element – List.indexOf()

arrFruits.indexOf(objMango);

Index of Element – List.indexOf()

arrFruits.indexOf(objMango);

SubList – List.subList(StartPOS, EndPOS)

List<Fruits> arrNewFruits = arrFruits.subList(0,2);

containsAll – NewList.containsAll(OrgList)

 arrNewFruits.containsAll(arrFruits);

set Value at Index

 arrNewFruits.set(1, new Orange());

removeAll

 arrFruits.removeAll(arrFruits);

addAll

 arrFruits.addAll(arrNewFruits);

retainAll – set Intersection Operation
retainAll performs set Interscetion Operation by taking Two ArrayList. It Retains Boolean true if intersection finds element false otherwise. 

List<Integer> arrNums1 = new ArrayList<Integer>();
arrNums1.add(1);
arrNums1.add(2);
arrNums1.add(3);
		
List<Integer> arrNums2 = new ArrayList<Integer>();
arrNums2.add(1);

System.out.println(arrNums1.retainAll(arrNums2));
System.out.println(arrNums1);