Few more useful SQL

---Remove all varchar on column
to_number(translate(mb.msisdn,'#+= <=<IDEAXXXXXXXX.?"-*%@!$%^&*$abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),'999999999999')
 between om.msisdn_start and om.msisdn_end


create materialized view dilip.DILIP_user_mv
tablespace DILIP
refresh complete
start with sysdate
next  trunc(sysdate)+1+2/24+35/(24*60) 
----enable query rewrite
as 
SELECT  USER_INFO_ID,POINTS_BALANCE,LOGIN_ID,rnk FROM
(
SELECT user_info_id, points_balance, login_id, RANK() OVER ( ORDER BY points_balance DESC) rnk
FROM dilip.DILIP_user, dilip.DILIP_user123
WHERE user_info_id=id
)
WHERE rownum<=10



CREATE MATERIALIZED VIEW DILIP_LOG_MV   
    TABLESPACE TEST BUILD IMMEDIATE   REFRESH force  
    START WITH sysdate
    NEXT trunc(sysdate)+1+2/24+35/(24*60)   
  AS 
    SELECT OPERATOR_ID,CIRCLE_ID,KEYWORD,COUNT(MSISDN) 
    TOTAL_DOWNLOAD_COUNT     ,COUNT(DISTINCT MSISDN) 
    UNIQUE_USER_COUNT,trunc(RECVAT) recvat     
    FROM DILIP_LOG_report     
    WHERE trunc(RECVAT) between trunc(sysdate-60)     
    and trunc(sysdate-1)     GROUP BY OPERATOR_ID,CIRCLE_ID,
    KEYWORD,trunc(RECVAT)   


----Duplicate rows calculation based on combination of columns
--- Good performance most of time if we have index created on ------ columns inside where clause. select count(*) from hw_content_master t1
where exists (select 'x' from dilip_master t2
                 where t1.cont_id = t2.cont_id
                 and t1.rowid > t2.rowid
                -- and t1.active =1
              )   


----Rank Query
select mb.* from
(
SELECT user_info_id, points_balance, login_id, RANK() OVER ( ORDER BY points_test DESC) rnk
FROM dilip_user a, user_info b
WHERE a.user_info_id=b.id
) mb
mb.rownum < 11


---- All Foreign key reference on specific table

select
        a.tt,
        a.owner,
        b.table_name,
        a.constraint_name,
        b.column_name,
        b.position,
        a.r_constraint_name,
        c.column_name,
        c.position,
        c.table_name r_table_name,
        a.r_owner
from
        (select
                owner,
                constraint_name,
                r_constraint_name,
                r_owner,1 tt
        from
                dba_constraints
        where
                owner=upper('&&owner')
                and table_name=upper('&&table_name')
                and constraint_type!='C'
        union
        select
                owner,
                constraint_name,
                r_constraint_name,
                r_owner,2
        from
                dba_constraints
        where
                (r_constraint_name,r_owner) in
                (select
                        constraint_name,
                        owner
                from
                        dba_constraints
                where
                        owner=upper('&owner')
                        and table_name=upper('&table_name'))
        ) a,
        dba_cons_columns b,
        dba_cons_columns c
where
        b.constraint_name=a.constraint_name
        and b.owner=a.owner
        and c.constraint_name=a.r_constraint_name
        and c.owner=a.r_owner
        and b.position=c.position
order   by 1,2,3,4,5

---Traverse from child through parent

select * from (
SELECT rpad('*',2*level,'*') || username ,sys_connect_by_path( userid, '/' )rownum1 ,level rank,userid,username,user_level_higher 
from dilip_users  --where user
start with userid=10001  ---10001
CONNECT BY  userid=  prior user_level_higher
)  --where userid = 10002

where rank = 2