CODEWITHSUNDEEP
sqloracle-database
user4441082
user4441082

Reputation: 371

Create index to speed up query

I am using Oracle to calculate human walking speed. I have a table like:

Name   timestamp                    latitude longitude
Billy  2014-10-10  10:10:12             x1       y1
Billy  ...                              x2       y2
... 
Jim                                     k1       m1
Jim                                     k2       m2
...
Kate                                    l1       n1
Kate                                    l2       n2

I want to select the people whose average speed in any one minute is between 5-7 m/min. Here is my code to calculate the distance from point A to Point B using Haversin'law. 

    CREATE INDEX PIndex
ON  table1 (phoneid,timestamp,gps_latitude,gps_longitude)

    SELECT a.phoneid
           ,a.gps_latitude as alat
         ,a.gps_longitude as along
           ,b.gps_latitude as blat
          ,b.gps_longitude as blong
          ,3956 * 2 * ASIN(SQRT( POWER(SIN((alat - abs(blat)) * pi()/180 / 2), 2)  + COS(along * pi()/180 ) * COS(abs(blong) * pi()/180)
            * POWER(SIN((along - blong) * pi()/180 / 2), 2) ))
       AS distance
       From Table1  a
       inner join Table1 b
       on a.phoneid = b.phoneid
       and a.timestamp = DATEADD(mi,1,b.timestamp)
      Where distance between 5 and 7

I have two questions on that:

(1) If my table is extremely large and updated every second.(say 10^9 rows). I want to create an index to speed up the query. How to achieve that in my select please?

(2) To solve the speed problem. Is there any method like using Java to cut the table into block to speed up the query please?

Upvotes: 0

Views: 323

Answers (2)

Non Plus Ultra
Non Plus Ultra

Reputation: 906

You have a daunting task. There probably is no simple one fits all solution. You can start with a function based index on

CREATE INDEX timestamp_fb_idx ON table1 (DATEADD(mi,1,timestamp))

And see if it makes any difference.

Upvotes: 2

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

First of all, I feel curious how you intend to get 10^100 rows. If Earth has, say, 10^10 of people, you'll need about 10^90 rows for every people. If people's coords will be stored once per second, they should live about 10^80 years to fill the database.

If thinking about this query only, best way of indexing is to make this table index-organized using join condition fields as a primary key.

Generally, speed up of this query should be based on iteration instead of joining. But task in general is too absurd to be true. Eighter it's a learning task (with hundreds of rows) or you miss many important parts of it.

Upvotes: 0

Related Questions