Some Oracle Query Tricks

----Get factorial of number using query:
----------------------------------------------->
with t as (select level as l from dual connect by level <=&value)
select EXP (SUM (LN (l))) MULTIPLY from t


----Get ORACLE_HOME path using query:
----------------------------------------------->
select substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) ORACLE_HOME from dba_libraries where library_name = 'DBMS_SUMADV_LIB';

Duplicate Index / Redundant Index Oracle

what is duplicate index ?
This is when table has multiple indexes defined on the same columns. The indexes may have with different names.
for example :
first index is created on columns : index1(a,b,c)
second index is created on columns : index2(a,b)
so as above "a" and "b" columns are mutual of each other and place in same order, therefore second index is duplicate of first:
note: index3(a,b) and index4(b,a), both have different definition.

How to identify duplicate index (oracle):
source::::
http://www.dba-oracle.com/t_detecting_duplicate_indexes.htm
select /*+ rule */
   a.table_owner,
   a.table_name,
   a.index_owner,
   a.index_name,
   column_name_list,
   column_name_list_dup,
   dup duplicate_indexes,
   i.uniqueness,
   i.partitioned,
   i.leaf_blocks,
   i.distinct_keys,
   i.num_rows,
   i.clustering_factor
from
  (
   select
      table_owner,
      table_name,
      index_owner,
      index_name,
      column_name_list_dup,
      dup,
      max(dup) OVER
       (partition by table_owner, table_name, index_name) dup_mx
   from
      (
       select
          table_owner,
          table_name,
          index_owner,
          index_name,
          substr(SYS_CONNECT_BY_PATH(column_name, ','),2) 
          column_name_list_dup,
          dup
       from
          (
          select
            index_owner,
            index_name,
            table_owner,
            table_name,
            column_name,
            count(1) OVER
             (partition by
                 index_owner,
                 index_name) cnt,
             ROW_NUMBER () OVER
               (partition by
                  index_owner,
                  index_name
                order by column_position) as seq,
             count(1) OVER
               (partition by
                  table_owner,
                  table_name,
                  column_name,
                  column_position) as dup
   from
      sys.dba_ind_columns
   where
      index_owner not in ('SYS', 'SYSTEM'))
where
   dup!=1
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
)) a,
(
select
   table_owner,
   table_name,
   index_owner,
   index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
(
select index_owner, index_name, table_owner, table_name, column_name,
count(1) OVER ( partition by index_owner, index_name) cnt,
ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq
from sys.dba_ind_columns
where index_owner not in ('SYS', 'SYSTEM'))
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
) b, dba_indexes i
where
    a.dup=a.dup_mx
and a.index_owner=b.index_owner
and a.index_name=b.index_name
and a.index_owner=i.owner
and a.index_name=i.index_name
order by
   a.table_owner, a.table_name, column_name_list_dup;
  
  
  
  
  
For a Particular Schema:
  
  
select /*+ rule */
 a.table_name, a.index_name, column_name_list, column_name_list_dup, dup duplicate_indexes,
 i.uniqueness, i.partitioned, i.leaf_blocks, i.distinct_keys, i.num_rows, i.clustering_factor
from
  (select
  table_name, index_name,
  column_name_list_dup, dup,
  max(dup) OVER (partition by table_name, index_name) dup_mx
   from
      (select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2)  column_name_list_dup, dup
       from
   (select
    index_name, table_name, column_name,
    count(1) OVER (partition by index_name) cnt,
    ROW_NUMBER () OVER (partition by index_name order by column_position) as seq,
    count(1) OVER (partition by table_name, column_name, column_position) as dup
   from
    user_ind_columns
   )
  where
   dup!=1
   start with seq=1
   connect by prior seq+1=seq
   and prior index_name=index_name
  )) a,
(
select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
 ( select index_name, table_name, column_name,
  count(1) OVER ( partition by index_name) cnt,
  ROW_NUMBER () OVER ( partition by index_name order by column_position) as seq
  from user_ind_columns
 )
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_name=index_name
) b, user_indexes i
where
    a.dup=a.dup_mx
and a.index_name=b.index_name
and a.index_name=i.index_name
order by
   a.table_name, column_name_list_dup;  

Sort Characters (of datatype char, varchar) in order of month name

When we generally do order by .. when it is string or varchar/nvarchar datatype then oracle or any db server will do order in way, that occurance of charater is priority.

for example :
A, B, C, D, E

In case if integer numbers are saved in Varchar database type then it will do the order as below:
1, 10, 11, 2, 22, 3, 4, 5, 55


In case if integer numbers are saved in Int database type then it will do the order as below:
1, 2, 3, 4, 5, 10, 11, 22, 55

Create table having datatype varchar2 or any of yours choice:

create table month_sort (mon1 varchar2(3));
insert into month_sort values ('MAR');
insert into month_sort values ('JUN');
insert into month_sort values ('MAY');
insert into month_sort values ('FEB');
insert into month_sort values ('JAN');
insert into month_sort values ('DEC');
insert into month_sort values ('SEP');
insert into month_sort values ('OCT');

::: this is a character sorting
select * from month_sort order by 1;

::: this is a character sorting in order of month name
select mon1 ,row_number() over (partition by  to_date(mon1,'mon') order by  to_date(mon1,'mon')) from month_sort;