Oracleで件数の多いテーブルを日付をキーに区切って検索するサンプル (改訂版)

bonlifeです。やたら件数が多いテーブルを検索したい時、何も考えずに「えいやっ！」とSQLを実行するとアイツが返って来ないことがありますよね。そのままボーッ待ってるとDB管理者から「ちょっと、変な検索してるでしょ、アナタ！」なんて言われてビクッとしたり。そもそも自分がDB管理者だったりする場合、諦めて session を kill してみたり。
そんな時は、こまめに分割して検索。例えばINDEXが効いてるDate列で分割。ここであえてのPythonです。持てる数少ない知識を総動員して簡単なスクリプトを書いてみました。慣れない dateutil 使ってみたり。 (PL/SQLでやれば良いじゃん！って声は聞こえないフリ。いつかちゃんとPL/SQL勉強しますから、今日のところは許してください。)

ora_select_by_date.py (横幅の制限でインデントがズレますがご愛嬌！)

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import cx_Oracle
import datetime
from dateutil import parser, relativedelta
from optparse import OptionParser

def get_timedelta(div_period):
    if div_period == 'year':
        return relativedelta.relativedelta(years=+1)
    elif div_period == 'month':
        return relativedelta.relativedelta(months=+1)
    elif div_period == 'day':
        return datetime.timedelta(1)
    elif div_period == 'hour':
        return datetime.timedelta(0,0,0,0,0,1)
    elif div_period == 'minute':
        return datetime.timedelta(0,0,0,0,1)
    elif div_period == 'second':
        return datetime.timedelta(0,1)

def result_generator(sql,
                     from_datetime,
                     to_datetime,
                     div_period):

    con = cx_Oracle.connect('user/pass@service_name')
    cur = con.cursor()

    td = get_timedelta(div_period)
    sub_from_datetime = from_datetime

    while sub_from_datetime < to_datetime:
        sub_to_datetime = sub_from_datetime + td
        cur.execute(sql,from_datetime=sub_from_datetime,
                    to_datetime=sub_to_datetime)
        for row in cur:
            yield ((sub_from_datetime, sub_to_datetime),row)
        sub_from_datetime = sub_to_datetime
    cur.close()
    con.close()

def main():
    usage = "usage: %prog [options] sql_file from_datetime to_datetime div_period\n\n" \
            "       div_period should be ['year','month','day','hour','minute','second']\n" \
            "       sql_file must contain ':from_datetime' and ':to_datetime'"
    version = "%prog 1.0"
    op = OptionParser(usage=usage,version=version)

    op.add_option("-v","--verbose",action="store_true",dest="verbose",
                      help="output detailed information")
    op.add_option("-t","--time",action="store_true",dest="print_datetime",
                      help="output each 'from' and 'to' datetime")
    (options, args) = op.parse_args()

    if len(args) != 4:
        op.print_help()
        sys.exit(1)

    sql           = file(args[0]).read()
    from_datetime = parser.parse(args[1])
    to_datetime   = parser.parse(args[2])
    div_period    = args[3]
    div_period_list = ['year','month','day','hour','minute','second']

    if div_period not in div_period_list:
        sys.stderr.write("ERR : Specified div_period is wrong\n")
        op.print_help()
        sys.exit(1)

    result = result_generator(sql,from_datetime,to_datetime,div_period)

    if options.verbose:
        print 'FROM            : %s' % from_datetime
        print 'TO              : %s' % to_datetime
        print 'DIVISION PERIOD : %s' % div_period
        print
        print sql
        print

    if options.print_datetime:
        for i in result:
            print '\t'.join(map(str,i[0])) + '\t',
            print '\t'.join([str(x) for x in i[1]])
    else:
        for i in result:
            print '\t'.join([str(x) for x in i[1]])

if __name__ == '__main__':
    main()

まずは、引数を指定せずに実行。Usageが表示されるので雰囲気を掴んでいただけるんじゃないでしょうか。

C:\...>ora_select_by_date.py
Usage: ora_select_by_date.py [options] sql_file from_datetime to_datetime div_period

       div_period should be ['year','month','day','hour','minute','second']
       sql_file must contain ':from_datetime' and ':to_datetime'

Options:
  --version      show program's version number and exit
  -h, --help     show this help message and exit
  -v, --verbose  output detailed information
  -t, --time     output each 'from' and 'to' datetime

以下のように :from_datetime, :to_datetime という文字列を入れたSQLを用意しておきます。

sample.sql

SELECT :from_datetime, :to_datetime
FROM   DUAL

で、以下のように実行。

C:\...>ora_select_by_date.py sample.sql 20070101 20070105 day
2007-01-01 00:00:00     2007-01-02 00:00:00
2007-01-02 00:00:00     2007-01-03 00:00:00
2007-01-03 00:00:00     2007-01-04 00:00:00
2007-01-04 00:00:00     2007-01-05 00:00:00

もうちょっとまともな感じのSQLを使ってオプションを指定した例も紹介しておきます。

sample2.sql

SELECT COUNT(*)
FROM   TABLE_A
WHERE  DATE_A >= :from_datetime
  AND  DATE_A <  :to_datetime

C:\...>ora_select_by_date.py -vt sample2.sql 20070101 20070101-0500 hour
FROM            : 2007-01-01 00:00:00
TO              : 2007-01-01 05:00:00
DIVISION PERIOD : hour

SELECT COUNT(*)
FROM   TABLE_A
WHERE  DATE_A >= :from_datetime
  AND  DATE_A <  :to_datetime

2007-01-01 00:00:00     2007-01-01 01:00:00     2
2007-01-01 01:00:00     2007-01-01 02:00:00     8
2007-01-01 02:00:00     2007-01-01 03:00:00     0
2007-01-01 03:00:00     2007-01-01 04:00:00     11
2007-01-01 04:00:00     2007-01-01 05:00:00     0

PL/SQLが苦手な私にとってはそこそこ便利。ただ、テーブルがそれほど大きくない場合は以下のように TRUNC() と GROUP BY の合わせ技の方が速いですのでご注意あれ。

SELECT   COUNT(*)
FROM     TABLE_A
WHERE    DATE_A >= TO_DATE('2007-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  AND    DATE_A <  TO_DATE('2007-01-01 05:00:00','YYYY-MM-DD HH24:MI:SS')
GROUP BY TRUNC(DATE_A,'HH24')

[参考URL]