Skip to main content

SQL Tips : EXTRACT function as alternative to TO_CHAR

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 count(*), extract(year from A.SC03TARIKH)
from smp.SC03F_TEMP a
group by extract(year from A.SC03TARIKH);
-- Time taken 27sec


ref : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm
Labels:

Comments

Post a Comment

Popular posts from this blog

Why I Can't Move/Import VM in XenServer.

Ok this is what i found out, the metadata export/import via ' Backup, Restore and Update ' menu in xsconsole OR using ' xe vm-export filename=xxx metadata=true vm= ' will export all vm's metadata eventhough you've specify the vm in the latter, the former is ok because it is backup & restore tool. Problem happens when you want to restore the metadata or import to another xenserver. If you follow the method from 'Backup and Restore' in the reference guide, it should be ok if you're using pool because all pools are using shared storage; and if you don't use pool, your xenserver store all the VMs in the shared storage ONLY, means the Local SR is not use at all. This way, you wont face any problem. But, what if you have a mix of local & shared storage use. 
Post a Comment
more »

SQL using partition and connect by clause.

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;
1 comment
more »

Use raw disk for Apache Trafficserver in Centos 7

I want to use raw disk for my Trafficserver 5.1.0. I've created a partition at /dev/sda5 and in order to allow trafficserver to use the disk, please follow these steps. 1) Edit/create a file in /etc/udev/rules.d . I named it 50-ats.rules eg. vi /etc/udev/rules.d/50-ats.rules 2) Add these text. SUBSYSTEM=="block", KERNEL=="sda5", GROUP:="trafficserver", OWNER:="nobody" note : - sda5 is the disk/partition I want to use with trafficserver. - OWNER:="nobody" is the default owner when you compile trafficserver.
Post a Comment
more »