Skip to Main Content
Forums

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.

Fastest Way to Find Out Record For a given Time stamp From Big Table (10GB)

AshwinWarApr 24 2018 — edited Apr 26 2018

Dear Friends,

                          Please help me to find out the fastest way to find out record for a given time stamp from a huge table.

create table callrec (username varchar2(12), ipaddr varchar2(20), callstart date, callend date) ;

I need the fasted way to find out the username from which  call which was active at  4/17/2018 01:10:15 from IPADDRESS: 10.56.89.20

Output 24328507,

I'm using following SQL to get desired data :

select   USERNAME

from callrec

where CALLEND >= to_date('17-APR-2018 01:10:15','DD-MON-YYYY HH24:MI:SS')

and CALLstart <= to_date('17-APR-2018 01:10:15','DD-MON-YYYY HH24:MI:SS')

and ipaddress='10.56.89.20'

/

How I should rewrite the above query and whats the index requirement.

insert into callrec values('23682768''a','10.63.237.147',to_date('16-APR-2018 02:10:19','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:10:19','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28705863''a','10.60.1.110',to_date('17-APR-2018 01:05:24','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:10:18','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26407498''a','10.56.117.130',to_date('17-APR-2018 01:22:29','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:25:29','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('21043932','10.184.4.200',to_date('17-APR-2018 01:27:01','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:28:43','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28483667''a','10.58.243.36',to_date('17-APR-2018 01:21:44','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:25:30','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26205175''a','10.58.113.44',to_date('17-APR-2018 00:44:11','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:25:35','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('23861190','10.183.136.70',to_date('17-APR-2018 01:23:51','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:28:49','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('24328507''a','10.56.89.20',to_date('16-APR-2018 21:35:57','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:25:36','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('25434129''a','10.57.161.194',to_date('17-APR-2018 01:12:41','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:14:55','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('22044975','10.183.136.213',to_date('17-APR-2018 01:27:39','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:29:24','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28147576''a','10.58.176.230',to_date('17-APR-2018 01:01:36','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:26:10','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('2810736''a','10.60.7.50',to_date('17-APR-2018 01:25:17','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:26:10','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('25453970''a','10.57.163.148',to_date('17-APR-2018 01:09:08','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:15:30','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('24052040''a','10.57.53.242',to_date('17-APR-2018 01:23:27','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:26:11','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('25646718','10.184.5.32',to_date('17-APR-2018 01:28:23','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:29:24','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('27891402','10.181.147.163',to_date('17-APR-2018 01:29:24','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:29:24','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('29210127','10.10.164.0',to_date('17-APR-2018 01:29:50','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:30:01','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('24936812''a','10.56.3.127',to_date('16-APR-2018 23:36:51','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:26:47','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28991276''a','10.10.219.232',to_date('16-APR-2018 22:40:19','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:26:48','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28935106''a','10.10.221.243',to_date('17-APR-2018 01:25:30','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:26:48','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28984876''a','10.58.235.94',to_date('16-APR-2018 15:38:53','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:26:48','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28543152''a','10.63.138.226',to_date('17-APR-2018 01:03:03','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:26:49','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28471186','10.10.163.185',to_date('17-APR-2018 01:28:46','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:30:02','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28548011''a','10.60.13.138',to_date('17-APR-2018 01:09:19','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:30','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28408958''a','10.60.129.152',to_date('17-APR-2018 01:09:45','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:11:30','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28452578''a','10.60.6.42',to_date('17-APR-2018 01:09:37','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:31','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('27788572''a','10.10.35.82',to_date('16-APR-2018 02:11:30','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:31','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28754409''a','10.58.225.55',to_date('16-APR-2018 02:11:30','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:31','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28466095''a','10.60.1.180',to_date('17-APR-2018 00:46:32','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:30','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26834877''a','10.58.95.131',to_date('17-APR-2018 01:09:52','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:31','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('23696455','10.183.132.97',to_date('17-APR-2018 00:58:27','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:56','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28145772''a','10.60.1.3',to_date('17-APR-2018 01:04:50','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:05:45','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('27701237','10.184.28.11',to_date('16-APR-2018 09:54:06','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:58','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('23720218''a','10.56.185.19',to_date('17-APR-2018 01:05:24','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:05:45','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26157844''a','10.10.254.147',to_date('17-APR-2018 01:06:10','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:07:12','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28389739''a','10.58.217.132',to_date('17-APR-2018 00:39:10','DD-MON-YYYY HH24:MI:SS'),to_date('17

-APR-2018 01:05:45','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('21642332''a','10.57.243.3',to_date('17-APR-2018 00:54:32','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 00:55:04','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28575743''a','10.58.85.35',to_date('17-APR-2018 01:07:30','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:07:10','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('25062145','10.184.44.13',to_date('16-APR-2018 23:52:50','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:11:13','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28451128''a','10.60.20.54',to_date('16-APR-2018 22:17:09','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:00','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26855466''a','10.63.161.61',to_date('17-APR-2018 00:07:04','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:00','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26858078''a','10.60.143.68',to_date('17-APR-2018 00:07:13','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:01','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('23645515''a','10.62.6.206',to_date('17-APR-2018 01:05:34','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:07:10','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26572294''a','10.63.9.139',to_date('17-APR-2018 01:03:04','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:01','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('26718556''a','10.58.49.55',to_date('17-APR-2018 01:05:29','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:17','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('28046214''a','10.58.171.50',to_date('17-APR-2018 00:57:49','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:17','DD-MON-YYYY HH24:MI:SS'));

insert into callrec values('27692303''a','10.61.1.142',to_date('17-APR-2018 01:06:08','DD-MON-YYYY HH24:MI:SS'),to_date('17-

APR-2018 01:08:17','DD-MON-YYYY HH24:MI:SS'));
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 24 2018
Added on Apr 24 2018
16 comments
445 views