Monday, May 20, 2013

IMP-00037: Character set marker unknown

Solution to IMP-00037: Character set marker unknown problem.


Today while migrating data using imp/exp from oracle 9i to 11g, I faced the following issue
IMP-00037: Character set marker unknown
IMP-00000: Import terminated unsuccessfully

This issue comes into picture when dump (.dmp) file is corrupted.
In my case, it was not the dmp file corruption. I forget to de-compress the file (exp.dmp).

So i used gunzip first to decompress the .dmp file before importing, then I was able import successfully.



Friday, May 17, 2013

Find Next Higher Number With Same Digits

Write a program to find the next highest number by rearranging the digits of a number. For instance, 
If input is 13483 then output must be 13834.

Test cases:
Input     Output
3971     7139
83971   87139
54321   54321
405321 410235


Logic:
Let take a numner 12543 and resulting next higher number is 13245. 
Scan the digits from the tenths digit going towards left (which is 4 in our case) 
At each iteration we check the right digit of the current digit we are at and if the value of the right is greater then current we stop other continue to left 
4>3 continue 
5>4 contine 
2>5 stop 
2 is our pivot element. 
from the digit 2 to the right we find the smallest higher digit of resulting number 2 which 3. 
swap the 3 with 2, it will become 13542. now 3 is our pivot element. 
Now revere the number to right pivot element i.e 3 , it comes 13245 result. 
Note : in case of repeating digit like 147553 we need to swap the 4 with the rightmost digit, otherwise we don't get the highest number. 
Time complexity would be O(n)


Code

If you know anyone who has started learning Java, why not help them out! Just share this post with them. 
Thanks for studying today!...

PL SQL histogram


Histogram
Histograms are feature in CBO( cost based optimizer ) and it helps to optimizer to determine how data are skewed(distributed) with in the column.Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed.Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order.

Advantages
1. Histograms are useful for Oracle optimizer to choose the right access method in a table.
2. It is also useful for optimizer to decide the correct table join order. When we join multiple tables, histogram helps to minimize the intermediate result set.
 Since the smaller size of the intermediate result set will improve the performance.

Method_opt Parameter: This is the parameter which tells about creating histogram while collecting the statistics. 
The default is FOR ALL COLUMNS SIZE AUTO in Oracle10g. 

We have one table containing 3.6 million record, and one columns emp_status is highly skewed, it two distinct values (N,Y). we have bitmap index on emp_status.

1. Let generate the statistics without any histogram. without histogram oracle assume that data is evenly distributed and optimizer think that we have
1.8 million record for  emp_status=y and 1.8 for emp_status=n

SQL> select count(*),emp_status from pardeep.emp 2 group by emp_status;

COUNT(*) E
---------- -
1 N
3000000 Y

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'pardeep', TABNAME => 'EMP',ESTIMATE_PERCENT =>10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);
PL/SQL procedure successfully completed.

SQL> select ename from pardeep.emp where emp_status='Y';
3000000 rows selected.

Examplain plain
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1832K 15M 5374 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 1832K 15M 5374 (5) 00:01:05

SQL> select ename from scott.emp where emp_status='N';

Examplain plain
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1832K 15M 5374 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 1832K 15M 5374 (5) 00:01:05


Conclusion : Optimizer is using full scan for the query which return 3000000 as well as it using full table scan for query which return only 1 record.

2.  Let us generate the statistics with histogram and see what kind of execution path optimizer is using
FOR COLUMN SIZE 2 EMP_STATUS will create two bucket for column emp_status. 
If we are not sure the distinct number of values in the column, then we can use AUTO option to collect histogram.

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'pardeep', TABNAME => 'EMP',ESTIMATE_PERCENT =>10, METHOD_OPT => 'FOR COLUMNS SIZE 2 EMP_STATUS',CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select ename from pardeep.emp where emp_status='Y';

3670016 rows selected.

Examplain plain
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 3681K 31M 5375 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 3681K 31M 5375 (5) 00:01:05


SQL> select ename from pardeep.emp where emp_status='N';

Examplain plain
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 9 1 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 9 1 (0) 00:00:01
2 BITMAP CONVERSION TO ROWIDS
* 3 BITMAP INDEX SINGLE VALUE IDX_EMP


Conclusion : Optimizer is using full scan for the query which return 3000000 records. optimizer is using index scan for other query which retrun 1 record.

Data dictionary objects for Histogram: 

  • user_histograms
  • user_part_histograms
  • user_subpart_histograms
  • user_tab_histograms
  • user_tab_col_statistics



if you find this information useful, please comment.

Wednesday, May 15, 2013

why static is not allowed in inner class

why static is not allowed in inner class or why static final is allowed in inner class

The idea behind inner classes ( non static inner class) is to operate in the context of the enclosing instance. Somehow, allowing static variables and methods contradicts this motivation.
http://docs.oracle.com/javase/specs/jls/se5.0/html/classes.html#8.1.3

Inner class are like an instance attribute of enclosing object. Also static means to work without instance at first place.

So it's doesn't make sence to allow staic feature in inner classes.

Let take this example:

 class pardeep{
   public String name;
  }

If you create two instance of this pardeep

pardeep a= new pardeep();
a.name="kumar";

pardeep b=new pardeep();

b.name="kumarOne";

It is clear the each has own value for the property name.


The same happens with inner class, each inner class instance is independent of the inner class instance.


So if you try to create counter(static) attribute , there is now way to share the value across two different instances.


class Pardeep {

  public string name;
  class kumar{
    static counter;
  }
}

when you create two instance a and b , what would be the correct value for the static variables counter? It is not possible to determine, because existence of

Kumar class depends completely on each of the enclosing object.
That why static is not allowed in inner.

Note: This is also the reason that when class is declared static, it doesn't need any living instance to live itself.


If this counter is constant, then there will be no problem because value is not going to be changed.

That why final make them(counter) constant once initilazed, is a compile time contant value.
i.e, final static counter;
That why final static is allowed in inner class


If you know anyone who has started learning Java, why not help them out! Just share this post with them.Thanks for studying today!...