Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Analytic functions - help needed

VladaSep 7 2007 — edited Sep 12 2007
Hi, everybody. I have large table 20M+ records. The table is like
DT DATE,
NO NUMBER,
ATR1 char(1),
ATR2 char(1)

unique is DT,NO

I need write select statement which compute count of UNIQUE NO in date interval by WINDOW

Interval is defined by start date, end date, lenght and step. For example
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute

In result:
DT_FROM DT_TILL AB
1. 1. 2006 0:00:00 1. 1. 2006 0:59:00 6
1. 1. 2006 0:01:00 1. 1. 2006 1:00:00 6
1. 1. 2006 0:02:00 1. 1. 2006 1:01:00 6
1. 1. 2006 0:03:00 1. 1. 2006 1:02:00 6
1. 1. 2006 0:04:00 1. 1. 2006 1:03:00 7
1. 1. 2006 0:05:00 1. 1. 2006 1:04:00 7
1. 1. 2006 0:06:00 1. 1. 2006 1:05:00 8
.
.
1. 1. 2006 23:57:00 2. 1. 2006 0:56:00 14
1. 1. 2006 23:58:00 2. 1. 2006 0:57:00 13
1. 1. 2006 23:59:00 2. 1. 2006 0:58:00 13

There is two problems:
1) Analytic function count not supported distinct count over WINDOW
2) Because I need every interval (interval for wich no data in my table) , i must outer join this table with dynamic generated intervals (using dual connect by level). This is very slow process (for inteval of 1 year)
select
count(*) over (order by dt RANGE BETWEEN CURRENT ROW AND INTERVAL '60' MINUTE FOLLOWING) TAB
FROM (SELECT TRUNC (:dfrom) + (LEVEL - 1) / 1440 dfrom,
TRUNC (:dfrom) + (LEVEL - 1) / 1440 + 59/24/60 dtill,
level lvl
FROM DUAL
CONNECT BY LEVEL <= Trunc((:DTILL - :DFROM) * 1440 / :STP + 1)) TA
LEFT OUTER JOIN
d_lps_secx_c TB -- this table has 20M+ records
ON dt = dfrom
order by ta.dfrom

Comments

807557
If you have access to both Storeg Systems from the hosts, or access from host to host, you can try something like (assuming autofs is running):

1. Login into new server
# (cd /net/old-server/your-directory;find .)|cpio -pdv /new-directory
OR
# cd /destination-directory
# (cd /net/old-server/source-directory; tar cEf - .)|tar xvf -
If you do not have autofs running and do not want to have it running or any other problem with autofs, you can use SSH to transfer files using tar
http://www.cyberciti.biz/faq/howto-use-tar-command-through-network-over-ssh-session/

If You still have problems with SSH, you can also use tar on top of telnet

Cheers
Andy
807557
Quoting cpio man page:

281 ... " .....................................To transfer a large file
282 ... (8 Gb - 1 byte), the header format can be
283 ... tar|TAR, ustar|USTAR, or odc only."

"header format" configured with -H option.
SEE ALSO ...largefile(5).


737 ... " The new pax(1) format, with a command that supports it (for
738 ... example, pax , tar, or cpio), should be used for large
739 ... files. The cpio command is no longer part of the current
740 ... POSIX standard and is deprecated in favor of pax. "
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 10 2007
Added on Sep 7 2007
25 comments
4,617 views