READ WIT ME

You want to create a sql to list data group in year (datetime datatype). Example as below : The table is smp.SC03F_TEMP with over 10 million rows. Usually, if there something deals with date we will use to_char function. As an alternative, you can use extract function to extract certain part of the date. In belows example, we extract the year of a column. As we can see the time taken is almost half for 10 million rows. Happy trying. select count(*) from smp.SC03F_TEMP; -- 10,866,921 records select count(*), to_char(A.SC03TARIKH, 'YYYY') from smp.SC03F_TEMP a group by to_char(A.SC03TARIKH, 'YYYY'); -- Time taken 54sec

select id, max(ltrim(sys_connect_by_path(gred,' , '),' , ')) as mygred from (select b.KJ19SIJIL||b.KJ19KOD as id, b.KJ19GRED as gred, row_number() over(partition by b.KJ19SIJIL||b.KJ19KOD order by b.KJ19GRED) as myrow from kj19setara b) start with myrow = 1 connect by prior myrow = myrow -1 and prior id = id group by id order by 1;

Here's a quick sql on how to search for session details including their running sql. The sql statement is as below : select a.terminal, a.program, a.username, to_char((a.last_call_et/3660),990.99) LastCall, a.logon_time, a.sql_hash_value, decode(a.sql_hash_value,'0',null,(select b.sql_text from v$sql b where b.hash_value = a.sql_hash_value and rownum = 1)) SQL_statement from v$session a where a.terminal like '%' order by 4 desc; a.terminal - machine name a.program - what program taht make connection to our database. LastCall - last time this connection make an sql call. The column is in seconds so we devide with 3660 to convert it to hours. a.logon_time - logon time for this particular connection. a.sql_hash_value - the key to link between v$sql & v$session. Just for checking purpose. SQL_statement - sql statement called by this connection. So, basically this sql will retrieve all connections sorted by 'LastCall' since i w...