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 [2010/07/02 06:39]
tdo 		dev:sql:examples:home [2012/10/26 14:57] (current)
Line 3: Line 3:
 ====== Omnidex SQL Examples ====== ====== Omnidex SQL Examples ======
 {{page>:​sql_bar&​nofooter&​noeditbtn}} {{page>:​sql_bar&​nofooter&​noeditbtn}}
-===== $CONTAIN Function ===== 
  
-  ​SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,'fred,'​SYNONYMS=ALL_GIVEN_NAMES'​)+  ​* Select Statements 
 +  * Qualify Statements 
 +  * Export Statements 
 +  * Extract Statements 
 +  * Debugging Statements 
 +  * Update Indexes Statements 
 +  * Select Specials ​(SAMPLE, RANDOM, EVERY) 
 +  * CREATE DATABASE Statements 
 +  * Connect Statements ​
  
-  SELECT FIRST_NAME FROM CUSTOMERS WHERE \ +===== Extract Omnidex DDL from existing Omnidex Environment ​===== 
-      $CONTAINS(FIRST_NAME,'​fred,'​SYNONYMS','​LIST=ALL_GIVEN_NAMES'​) +<​code>​ 
-===== Qualify Statement ​=====+  os> odxsql 
 +  odxsql> connect myenv.xml;​ 
 +  odxsql> EXTRACT DDL to myfile.sql;​ 
 +</​code>​
  
-  ​QUALIFY CUSTOMERS WHERE FIRST_NAME = '​SYNONYMS(fred,"​ALL_GIVEN_NAMES"​)'​+  ​EXTRACT DDL to myfile.sql WITH DELETE;
  
-  QUALIFY CUSTOMERS WHERE FIRST_NAME ​= 'SYNONYMS(fred,,"​LIST=ALL_GIVEN_NAMES"​)'+===== 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
  
-  SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,​ \ 
-     '​(SYNONYMS(fred,"​ALL_GIVEN_NAMES"​))'​) ​ 
  
-==== $CONTAINS ==== 
  
-  $contains(order_date,​ '​1st_qtr_1990',​ '​synonyms=time_periods,​exclusive'​) ​   
 <​code>​ <​code>​
-;set autoexplain on +partition households by "case when state in ('​CA'​,'​CO'​,'​TX'​) then '​01'​ when state in ('​AZ','​NM','​UT'​) then '​02'​ else '​03'​ end" 
-set explain counts, text, $ox +Partitioned 1,909 rows from HOUSEHOLDS into 3 partitions 
-set pagelength 0 +> dir 
-set timer on+HOUSEHOLDS_01.dat 
 +HOUSEHOLDS_02.dat 
 +HOUSEHOLDS_03.dat
  
-connect star+> 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 =====
-; +
-; DESIGN - Prompts +
-; +
-; These samples presume the following prompts on the screen: +
-; +
-;   ​Time +
-;     - Dates or Date Ranges, supported by synonyms for things like  +
-;       ​THIS_MONTH,​ LAST_QUARTER,​ etc.  Always try to encourage a time filter. +
-; +
-;   ​Company +
-;     - Company Name +
-;     - Region, State, Zip +
-; +
-;   ​Personal Demographics +
-;     - Income +
-;     - Profession +
-;     - Cust Since +
-; +
-;   ​Product Info +
-;     - Product Name +
-;     - Product Category +
-;     - Manufacturer +
-; +
-; +
-; +
-;                                                          Counts  +
-;   Time Period: ​     [THIS_MONTH] ​                       [_____] ​  +
-;   ​Company: ​         [_______________________________] ​  ​[_____] +
-;   ​Region: ​          ​[__] ​  ​State:​ [__]   Zip: [_____] ​  ​[_____] +
-;   ​Profession: ​      ​[_______________________________] ​  ​[_____] +
-;   ​Income: ​          ​[_______________________________] ​  ​[_____] +
-;   Cust Since: ​      ​[____] ​                             [_____] +
-;   ​Product Name:     ​[_______________________________] ​  ​[_____] +
-;   ​Product Category: [_______________________________] ​  ​[_____] +
-;   ​Manufacturer: ​    ​[_______________________________] ​  ​[_____] +
-; +
-; +
-;​------------------------------------------------------------------------------ +
-; +
-; DESIGN - Lists +
-; +
-; These samples presume the following list view: +
-; +
-; +
-; [Company] [Contact] ​ [Product] ​ [Date] ​ [Quantity] ​ [Amount] ​ [Tax]  [Total] +
-; +
-; +
-; There is no restriction on the fields that can be shown in the list view. It +
-; is possible to include any field from ORDERS_SVW, PROSPECTS, PRODUCTS, or  +
-; any of the other tables that join to ORDERS_SVW. ​  +
-; +
-;  +
-;​------------------------------------------------------------------------------ +
-; +
-; DESIGN - Crosstab +
-; +
-; For simplicity, the crosstab always summaries either counts or the TOTAL  +
-; column (the result of product cost times quantity, not including sales tax.   +
-; These samples presume the following crosstab choices: +
-; +
-;   X Axis: +
-; +
-;     ​Region ​    +
-;     ​State ​     +
-;     Zip +
-;     ​Income +
-;     ​Profession +
-;     Cust Since +
-;     ​Product Name +
-;     ​Product Category +
-;     ​Manufacturer +
-; +
-;  Y Axis  +
-;  (Shorter list to avoid too much scrolling to the right) +
-;      +
-;     ​Fiscal Year +
-;     ​Fiscal Quarter +
-;     ​Region ​      +
-;     ​Income  +
-;     ​Profession +
-;     Cust Since +
-; +
-;​==============================================================================+
  
-; +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’ 
-; LIST VIEW +  
-; +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.
  
-; Example 1: Basic search +<​code>​  
-</code? +> select extract ('MMM D, YYYY  HH:​0N:​0S.0F'​ from created) from mytable; 
-<​code ​slq>+  
 +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> 
 + 
 +===== Updates Indexes ===== 
 +  update indexes with MAXTHREADS=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 ===== 
 + 
 +  SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,'​fred,'​SYNONYMS=ALL_GIVEN_NAMES'​);​ 
 + 
 +  SELECT FIRST_NAME FROM CUSTOMERS WHERE  
 +      $CONTAINS(FIRST_NAME,'​fred,'​SYNONYMS','​LIST=ALL_GIVEN_NAMES'​);​ 
 + 
 +  SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,​  
 +     '​(SYNONYMS(fred,"​ALL_GIVEN_NAMES"​))'​);​  
 + 
 +===== Qualify Statement ===== 
 + 
 +  QUALIFY CUSTOMERS WHERE FIRST_NAME = '​SYNONYMS(fred,"​ALL_GIVEN_NAMES"​)'​ 
 + 
 +  QUALIFY CUSTOMERS WHERE FIRST_NAME = '​SYNONYMS(fred,,"​LIST=ALL_GIVEN_NAMES"​)'​ 
 + 
 + 
 +===== Searches ===== 
 +Example 1: Basic search 
 +<​code>​
 select ​       TOP 20 PRO.COMPANY,​ \ select ​       TOP 20 PRO.COMPANY,​ \
               PRO.CONTACT,​ \               PRO.CONTACT,​ \
Line 140: Line 176:
 </​code>​ </​code>​
  
-; Example 2: Same select list, but use $contains to allow synonyms +Example 2: Same select list, but use $contains to allow synonyms 
-<​code ​sql>+<​code>​
 select ​       TOP 20 PRO.COMPANY,​ \ select ​       TOP 20 PRO.COMPANY,​ \
               PRO.CONTACT,​ \               PRO.CONTACT,​ \
Line 156: Line 192:
               $contains(PRO.CUST_SINCE,​ '​1996'​)               $contains(PRO.CUST_SINCE,​ '​1996'​)
 </​code>​ </​code>​
 +Example 3: Same select list, but use joins to more dimensions to allow description fields from other dimensions and snowflakes.  ​
 +
 <​code>​ <​code>​
-; Example 3: Same select list, but use joins to more dimensions to allow  ​ 
-;            description fields from other dimensions and snowflakes. ​ The 
-;            joins are added on an 'as needed'​ basis depending on whether 
-;            a particular prompt was used. 
-</​code>​ 
-<code sql> 
 select ​       TOP 20 PRO.COMPANY,​ \ select ​       TOP 20 PRO.COMPANY,​ \
               PRO.CONTACT,​ \               PRO.CONTACT,​ \
Line 178: Line 210:
               $contains(MFRS.DESCRIPTION,​ '​Dell'​) and \               $contains(MFRS.DESCRIPTION,​ '​Dell'​) and \
               $contains(INC.DESCRIPTION,​ '​$50,​000-$59,​999'​) ​               $contains(INC.DESCRIPTION,​ '​$50,​000-$59,​999'​) ​
-</code?+</code>
  
 +Crosstab
 <​code>​ <​code>​
-;​============================================================================== 
-; 
-; CROSSTAB VIEW 
-; 
-; 
-============================================================================== 
  
-; Example 1: List View Example #1 criteria shown with a crosstab of counts by  +<​code>​
-;            product category and fiscal quarter +
-</code> +
-<code sql>+
 select ​       CAT.DESCRIPTION,​ \ select ​       CAT.DESCRIPTION,​ \
               ORD.OR_FQTR,​ \               ORD.OR_FQTR,​ \
Line 203: Line 227:
   group by    CAT.DESCRIPTION,​ \   group by    CAT.DESCRIPTION,​ \
               ORD.OR_FQTR               ORD.OR_FQTR
-<​code>​+</code>
               ​               ​
-  ; Example 2: List View Example #2 criteria shown with a crosstab of totals by  +Example 2: List View Example #2 criteria shown with a crosstab of totals by income and profession
-  ;            ​income and profession+
  
-<​code ​SQL>+<​code>​
 select ​       INC.DESCRIPTION,​ \ select ​       INC.DESCRIPTION,​ \
               PRF.DESCRIPTION,​ \               PRF.DESCRIPTION,​ \
Line 224: Line 247:
 ===== criteria shown with a crosstab of counts by cust_since and region ===== ===== criteria shown with a crosstab of counts by cust_since and region =====
  
-<​code ​SQL>+<​code>​
 select ​       ORD.CUST_SINCE,​ \ select ​       ORD.CUST_SINCE,​ \
               ORD.REGION, \               ORD.REGION, \
Line 240: Line 263:
 </​code> ​             ​ </​code> ​             ​
        
 +===== Force a system segmentation violation to test abnormal terminations =====
 +
 +  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.1278052795.txt.gz · Last modified: 2012/10/26 14:52 (external edit)
Sitemap Recent Changes