Show page
Old revisions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
dev:sql:examples:home [2011/04/22 16:19]
admin 		dev:sql:examples:home [2012/10/26 14:57] (current)
Line 11: Line 11:
   * Update Indexes Statements   * Update Indexes Statements
   * Select Specials (SAMPLE, RANDOM, EVERY)   * Select Specials (SAMPLE, RANDOM, EVERY)
 +  * CREATE DATABASE Statements
 +  * Connect Statements ​
  
 ===== Extract Omnidex DDL from existing Omnidex Environment ===== ===== Extract Omnidex DDL from existing Omnidex Environment =====
Line 17: Line 19:
   odxsql> connect myenv.xml;   odxsql> connect myenv.xml;
   odxsql> EXTRACT DDL to myfile.sql;   odxsql> EXTRACT DDL to myfile.sql;
 +</​code>​
 +
 +  EXTRACT DDL to myfile.sql WITH DELETE;
 +
 +===== CONNECT WITH MAINTENANCE =====
 +
 +The WITH MAINTENANCE simply means the same as exclusive access. ​ That is needed to execute the “INSTALL INDEXES” command, which resynchronizes the actual indexes with the XML file.
 +
 +===== Partition =====
 +
 +  > partition companies by "case when state in ('​CA','​CO','​TX'​) then '​01'​ when state in ('​NY','​FL','​WA'​) then '​02'​ else '​03'​ end" ;
 +Partitioned 31,548 rows from COMPANIES into 3 partitions
 +
 +
 +
 +<​code>​
 +partition households by "case when state in ('​CA','​CO','​TX'​) then '​01'​ when state in ('​AZ','​NM','​UT'​) then '​02'​ else '​03'​ end"
 +Partitioned 1,909 rows from HOUSEHOLDS into 3 partitions
 +> dir
 +HOUSEHOLDS_01.dat
 +HOUSEHOLDS_02.dat
 +HOUSEHOLDS_03.dat
 +
 +> partition households by state ;
 +Partitioned 1,909 rows from HOUSEHOLDS into 52 partitions
 +> dir
 +HOUSEHOLDS_AK.dat
 +HOUSEHOLDS_AL.dat
 +HOUSEHOLDS_AR.dat
 +HOUSEHOLDS_AZ.dat
 +HOUSEHOLDS_CA.dat
 +HOUSEHOLDS_CO.dat
 +HOUSEHOLDS_CT.dat
 +HOUSEHOLDS_DC.dat
 +HOUSEHOLDS_DE.dat
 +HOUSEHOLDS_FL.dat
 +HOUSEHOLDS_GA.dat
 +HOUSEHOLDS_HI.dat
 +HOUSEHOLDS_IA.dat
 +HOUSEHOLDS_ID.dat
 +HOUSEHOLDS_IL.dat
 +HOUSEHOLDS_IN.dat
 +HOUSEHOLDS_KS.dat
 +HOUSEHOLDS_KY.dat
 +HOUSEHOLDS_LA.dat
 +…
 +</​code>​
 +
 +===== Create DATABASE with environment variables =====
 +  Create database ​      "​star"​
 +  type         ​oracle
 +  version ​     "​{$ORAVER}"​
 +  userclass ​   "​DEFAULT"​ user     "​{star$(basename $PWD)}"​
 +                         ​password "​{star$(basename $PWD)}"​
 +  indexprefix ​ "​idx/​STAR"​
 +
 +===== Export using Oracle NLS date =====
 +
 +Oracle has a setting called NLS_DATE_FORMAT that controls what default date and time format to use in SQL statements. ​ We use that setting to pass criteria to Oracle. ​ We have a default setting, but you can control it yourself through an environment variable. ​ If you set that environment variable, we’ll do an ALTER SESSION with Oracle to pass on your new values. ​ Set the environment variable as follows:
 + 
 +  export DA_ORA_DATE_FMT=’format’
 + 
 +where ‘format’ is any valid NLS_DATE_FORMAT.
 + 
 +In my example, I set it as follows:
 + 
 +  export DA_ORA_DATE_FMT=’MON DD, YYYY HH24:​MI:​SS’
 + 
 +Here is an example of sending SQL statements through Omnidex using this technique. ​ Note that I’m using the EXTRACT function format a date to see the seconds and so forth.
 +
 +<​code> ​
 +> select extract ('MMM D, YYYY  HH:​0N:​0S.0F'​ from created) from mytable;
 + 
 +EXTRACT('​MMM D, YYYY  HH:0N:0S.0
 +--------------------------------
 +July 27, 2010  15:26:04.00
 +July 27, 2010  15:26:05.00
 +July 27, 2010  15:26:06.00
 + 
 +3 rows returned
 +> select extract ('MMM D, YYYY  HH:​0N:​0S.0F'​ from created) from mytable where created > 'Jul 27, 2010 15:​26:​04';​
 + 
 +EXTRACT('​MMM D, YYYY  HH:0N:0S.0
 +--------------------------------
 +July 27, 2010  15:26:05.00
 +July 27, 2010  15:26:06.00
 + 
 + 
 +2 rows returned
 +> 
 + 
 </​code>​ </​code>​
  
Line 23: Line 116:
  
   update indexes with max_threads=2;​   update indexes with max_threads=2;​
 +
 +Specifies a directory where several indexing logs are written as well as some internal logs such as dbinstal.log.
 +
 +  update indexes with log=odxlogs;​
 +
 +Update Indexes with limiting the rows being read and indexed per table.
 +<​code>​
 +os> odxsql
 +> connect myenv.xml;
 +> create directory odxlogs;
 +> setenv oa_max_rows=100000;​
 +> update indexes with log=odxlogs;​
 +> setenv oa_max_rows
 +> update indexes with log=odxlogs;​
 +> exit
 +os>
 +</​code>​
 +
 +
 +UPDATE INDEXES WITH BUF=n
 +
 +N is the number of MB to use in the build, and coincides with the BUF= in the build command in dbinstal.
 +
 +
  
 ===== $CONTAIN Function ===== ===== $CONTAIN Function =====
Line 149: Line 266:
  
   select * from $abort;   select * from $abort;
 +
 +
 +For example, “select count(*) from events where userid = 1 with optimization=none” will send it to Oracle and Oracle will use its index. ​ “select count(*) from events where userid = <​expression>​ with opt=none” will be processed with a full table scan, but with us processing the expression ourselves.  ​
 +
 +Yes, that’s correct. ​ We do not support the arithmetic expression below, partly because people want different units of measure. ​ In the example you use, I assume you mean “current_date minus 90 days”, but it could also mean “90 hours” or something different. ​ Since there was no SQL standard, we used our $calc_date or $compare_date functions to be explicit.  ​
 +
 +Yes, you must use the $CALC_DATE function to do date arithmetic, or you can use the $COMPARE_DATES routines. ​ The approaches you can take are:
 + 
 +Select count(*) from events where $compare_dates(created,​ current_date) > 90
 + 
 +Or 
 + 
 +Select count(*) from events where created > $calc_date(current_date,​ -90)
 +
  
 {{page>:​bottom_add&​nofooter&​noeditbtn}} {{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
dev/sql/examples/home.1303489195.txt.gz · Last modified: 2012/10/26 14:52 (external edit)
Sitemap Recent Changes