notebooks/index_creation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Column Indexes\n",
    "\n",
    "This is a short tutorial for creating indexes on table columns. In general, DESDM uses bitmap indexes, though in some cases b-tree indexes are used. One cool feature of Oracle's bitmap indexing is that individual indexes can be combined. For example, indexes on ```spread_model``` and ```spreaderr_model``` can lead to a performance increase for queries on the quantity ```spread_model + 3*spreaderr_model```. More details on indexing can be found [here](http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import easyaccess as ea"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "size = (100)\n",
    "x = np.random.normal(size=size)\n",
    "y = np.random.randint(0,size,size)\n",
    "z = y.astype(str)\n",
    "data = np.rec.fromarrays([x,y,z],names=['xcol','ycol','zcol'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = ea.connect(section='dessci',quiet=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "table_idx = 'mytable_index'\n",
    "query = \"\"\"\n",
    "--- Custom create table query\n",
    "CREATE TABLE %s (\n",
    "xcol NUMBER(6,4) NOT NULL, \n",
    "ycol BINARY_DOUBLE, \n",
    "zcol VARCHAR2(5) )\"\"\"%table_idx\n",
    "conn.cursor().execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[32m\n",
      " Inserted 100 rows and 3 columns into table MYTABLE_INDEX in 0.05 seconds\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "columns = data.dtype.names\n",
    "values = data.tolist()\n",
    "conn.insert_data(columns,values,table_idx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\u001b[32m\n",
      "1 rows in 0.09 seconds\u001b[0m\n",
      "\n",
      "      TABLE_NAME COLUMN_NAME INDEX_TYPE INDEX_NAME ITYP_NAME\n",
      "1  MYTABLE_INDEX        XCOL     BITMAP       XIDX      None\n",
      "\n"
     ]
    }
   ],
   "source": [
    "idxcol = 'xcol'\n",
    "idxname = 'xidx'\n",
    "query = 'CREATE BITMAP INDEX %s on %s(%s)'%(idxname,table_idx,idxcol)\n",
    "conn.cursor().execute(query)\n",
    "conn.onecmd('show_index %s'%table_idx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexes can also be created as a \"primary key\" field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "i = np.arange(size)\n",
    "data = np.rec.fromarrays([i,x,y,z],names=['key','xcol','ycol','zcol'])\n",
    "keycol = 'key'\n",
    "keyname = 'key_pk'\n",
    "table_key = 'mytable_key'\n",
    "query = \"\"\"\n",
    "--- Create table with primary key\n",
    "CREATE TABLE %s (\n",
    "key NUMBER(6,0) NOT NULL,\n",
    "xcol NUMBER(6,4) NOT NULL, \n",
    "ycol BINARY_DOUBLE, \n",
    "zcol VARCHAR2(5), \n",
    "CONSTRAINT %s PRIMARY KEY (%s) ) \"\"\"%(table_key,keyname,keycol)\n",
    "conn.cursor().execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24\n",
      " 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49\n",
      " 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74\n",
      " 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99]\n"
     ]
    }
   ],
   "source": [
    "print data['key']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[32m\n",
      " Inserted 100 rows and 4 columns into table MYTABLE_KEY in 0.05 seconds\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "columns = data.dtype.names\n",
    "values = data.tolist()\n",
    "conn.insert_data(columns,values,table_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\u001b[32m\n",
      "1 rows in 0.08 seconds\u001b[0m\n",
      "\n",
      "    TABLE_NAME COLUMN_NAME INDEX_TYPE INDEX_NAME ITYP_NAME\n",
      "1  MYTABLE_KEY         KEY     NORMAL     KEY_PK      None\n",
      "\n"
     ]
    }
   ],
   "source": [
    "conn.onecmd('show_index %s'%table_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn.cursor().execute('DROP TABLE %s PURGE'%table_idx)\n",
    "conn.cursor().execute('DROP TABLE %s PURGE'%table_key)\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}