os> odxsql odxsql> connect myenv.xml; odxsql> EXTRACT DDL to myfile.sql;
EXTRACT DDL to myfile.sql WITH DELETE;
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 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
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
…
Create database "star"
type oracle
version "{$ORAVER}"
userclass "DEFAULT" user "{star$(basename $PWD)}"
password "{star$(basename $PWD)}"
indexprefix "idx/STAR"
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.
> 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
>
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.
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>
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.
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 CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,"ALL_GIVEN_NAMES")'
QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,,"LIST=ALL_GIVEN_NAMES")'
Example 1: Basic search
select TOP 20 PRO.COMPANY, \
PRO.CONTACT, \
PRD.DESCRIPTION, \
ORD.ORDER_DATE, \
ORD.QUANTITY, \
ORD.AMOUNT, \
ORD.TOTAL \
from ORDERS_SVW ORD \
join PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
join PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
where ORD.ORDER_DATE BETWEEN 'Jan 1 2000' and 'Mar 31 2000' and \
PRO.STATE = 'CO' and \
PRO.CUST_SINCE = '1996'
Example 2: Same select list, but use $contains to allow synonyms
select TOP 20 PRO.COMPANY, \
PRO.CONTACT, \
PRD.DESCRIPTION, \
ORD.ORDER_DATE, \
ORD.QUANTITY, \
ORD.AMOUNT, \
ORD.TOTAL \
from ORDERS_SVW ORD \
join PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
join PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
where $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
$contains(PRO.STATE, 'COLORADO', 'synonyms=state_codes') and \
$contains(PRO.CUST_SINCE, '1996')
Example 3: Same select list, but use joins to more dimensions to allow description fields from other dimensions and snowflakes.
select TOP 20 PRO.COMPANY, \
PRO.CONTACT, \
PRD.DESCRIPTION, \
ORD.ORDER_DATE, \
ORD.QUANTITY, \
ORD.AMOUNT, \
ORD.TOTAL \
from ORDERS_SVW ORD \
join PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
join PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
join INCOMES INC on PRO.INCOME = INC.INCOME \
join MFRS on PRD.MFR = MFRS.MFR \
where $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
$contains(MFRS.DESCRIPTION, 'Dell') and \
$contains(INC.DESCRIPTION, '$50,000-$59,999')
Crosstab
<code>
select CAT.DESCRIPTION, \
ORD.OR_FQTR, \
COUNT(ORD.TOTAL) \
from ORDERS_SVW ORD \
join PROSPECTS PRO on ORD.ACCT = PROSPECTS.ACCT \
join CATEGORIES CAT on ORD.CATEGORY = CAT.CATEGORY \
where ORD.ORDER_DATE BETWEEN 'Jan 1 2000' and 'Mar 31 2000' and \
PRO.STATE = 'CO' and \
PRO.CUST_SINCE = '1996' \
group by CAT.DESCRIPTION, \
ORD.OR_FQTR
Example 2: List View Example #2 criteria shown with a crosstab of totals by income and profession
select INC.DESCRIPTION, \
PRF.DESCRIPTION, \
SUM(ORD.TOTAL) \
from ORDERS_SVW ORD \
join PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
join INCOMES INC on ORD.INCOME = INC.INCOME \
join PROFESSIONS PRF on ORD.PROFESSION = PRF.PROFESSION \
where $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
$contains(PRO.STATE, 'COLORADO', 'synonyms=state_codes') and \
$contains(PRO.CUST_SINCE, '1996') \
group by INC.DESCRIPTION, \
PRF.DESCRIPTION
select ORD.CUST_SINCE, \
ORD.REGION, \
COUNT(ORD.TOTAL) \
from ORDERS_SVW ORD \
join PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
join PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
join INCOMES INC on PRO.INCOME = INC.INCOME \
join MFRS on PRD.MFR = MFRS.MFR \
where $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
$contains(MFRS.DESCRIPTION, 'Dell') and \
$contains(INC.DESCRIPTION, '$50,000-$59,999') \
group by ORD.CUST_SINCE, \
ORD.REGION
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)