Category: Indexing

Index [examples]

Published on by homunizamLeave a comment
Creates an index for the specified table
CREATE INDEX
// Create an index on staff number
CREATE INDEX staff_no;
  ON staff (staff_no)
 
// Create an index on descending hire date and ascending staff number
CREATE INDEX hiredate;
  ON staff (hiredate DESC, staff_no ASC)
 
// Create an index on last name converted to lower case
CREATE INDEX lname;
  ON staff (lower(lastname))
Close index files in the current workarea
CLOSE INDEX
use patrons index events, dates, names
seek "BALLET"
list while event = "BALLET"
// Close index files in the current workarea 
close index
list for event = "BALLET"
Display index information about the current table
DISPLAY INDEXES
use example
display indexes to file ind_info
List index information about the current table
LIST INDEXES
use example
list indexes to file ind_info
Delete an index tag
DELETE TAG
use accounts exclusive
delete tag zip
Create an index for a table on a specified key
INDEX
use patrons
index on event tag event

https://www.lianja.com/doc/index.php/INDEX

Rebuild all of the active index files
REINDEX
set exclusive on
use patrons index events, names, dates
reindex

https://www.lianja.com/doc/index.php/REINDEX

Open a list of index files associated with the active table
SET INDEX
use patrons
set index to names, events    && opens up the index files
seek "smith"

https://www.lianja.com/doc/index.php/SET_INDEX

Copy single indexes to a multiple index file
COPY INDEXES
use accounts
set index to ordno, invno
copy indexes ordno, invno

https://www.lianja.com/doc/index.php/COPY_INDEXES

Restrict access to a set of records based on their index keys
SET KEY
open database southwind
use products
set order tag productid
set key to range 20, 30
list
set order tag productname
set key to "Chai"
list
set key to range "A","C"
list

https://www.lianja.com/doc/index.php/SET_KEY

Select the master index file from the active index file list
SET ORDER
use patrons index address.dbx
set order to tag zip of address

https://www.lianja.com/doc/index.php/SET_ORDER

Search for specified key in the master index and if found, position the record pointer in the table
SEEK
use patrons
index on event to events
seek "BALLET"
 
index on date to dates
seek ctod("01/01/2000")
 
index on dtos(eventdate) + "/" + event to dates
seek "20010101/BALLET"

https://www.lianja.com/doc/index.php/SEEK

Search for a key in the master index file
FIND
use patrons index events, name
m_find = "OPERA"
find &m_find
if found()
    edit
else
    dialog message "Record not found."
endif

https://www.lianja.com/doc/index.php/FIND

Specify number of buffers to use for index files
SET ICACHE
set icache to 100
use patrons index events, dates, names
 
// Another example
use accounts index acc_prefix, paid_date
set icache to reccount()
reindex

https://www.lianja.com/doc/index.php/SET_ICACHE

Determine whether duplicate keys are to be included in index files
SET UNIQUE
use patrons
set unique on
index on name to persons

https://www.lianja.com/doc/index.php/SET_UNIQUE

Rebuilds an existing index file for the specified table
ALTER INDEX
// Rebuild the index staff_no index on staff table
ALTER INDEX staff_no;
  ON staff;
  REBUILD;
  SHARED

https://www.lianja.com/doc/index.php/ALTER_INDEX

Removes an index from a table
DROP INDEX
DROP INDEX staff_no ON staff

https://www.lianja.com/doc/index.php/DROP_INDEX

Table constraint to define an index key
CREATE TABLE contact;
  (LastName char(25), FirstName char(25),;
  INDEX FullName (LastName, FirstName))

https://www.lianja.com/doc/index.php/INDEX_Table_Constraint

Table constraint to define an index key
CREATE TABLE contact;
  (LastName char(25), FirstName char(25),;
  KEY FullName (LastName, FirstName))

https://www.lianja.com/doc/index.php/KEY_Table_Constraint

Column constraint to define table’s Primary Key
CREATE TABLE newcust;
  (acc_ref char(5);
  default strzero(seqno(),5);
  PRIMARY KEY,;
  acc_name char(20))

https://www.lianja.com/doc/index.php/PRIMARY_KEY_Column_Constraint

Table constraint to define table’s Primary Key
CREATE TABLE newcust;
  (acc_ref char(5) default strzero(seqno(),5),;
  acc_name char(20))
 
ALTER TABLE newcust;
  ADD PRIMARY KEY acc_ref+acc_name TAG RefName

https://www.lianja.com/doc/index.php/PRIMARY_KEY_Table_Constraint

Column constraint to define a column as a Foreign Key for a parent table
CREATE TABLE orderitem;
  (ord_ref char(5) FOREIGN KEY,;
  item_ref char(5) PRIMARY KEY)

https://www.lianja.com/doc/index.php/FOREIGN_KEY_Column_Constraint

Table constraint to define a Foreign Key
CREATE TABLE supplier;
 (SuppId int PRIMARY KEY,;
  SuppRef char(10) UNIQUE,;
  SuppName char(40) UNIQUE)
 
CREATE TABLE purchase_order;
 (POId int PRIMARY KEY,;
  PO_SuppRef char(10),;
  POtotal num(10,2))
 
ALTER TABLE purchase_order ADD FOREIGN KEY po_suppref TAG fk_po_suppref 
ALTER TABLE purchase_order ADD FOREIGN KEY (po_suppref) TAG fk_po_suppref;
 REFERENCES supplier TAG SuppRef

https://www.lianja.com/doc/index.php/FOREIGN_KEY_Table_Constraint

Table constraint to define a candidate index
CREATE TABLE newcust;
  (acc_ref char(5) default strzero(seqno(),5),;
  acc_name char(20))
ALTER TABLE newcust;
  ADD UNIQUE acc_ref+acc_name TAG RefName
 
CREATE TABLE contact;
  (ContRef char(5), LastName char(25), FirstName char(25),;
  UNIQUE FullName (LastName, FirstName, ContRef))

https://www.lianja.com/doc/index.php/UNIQUE_Table_Constraint

Column constraint to define a candidate index for a table
CREATE TABLE newcust;
  (acc_ref char(5) default strzero(seqno(),5) UNIQUE,;
  acc_name char(20) UNIQUE)

https://www.lianja.com/doc/index.php/UNIQUE_Column_Constraint

36 character unique GUID
GUID
create table country;
 (id char(36) description "ID" default guid(),;
  countryname char(100) description "Name",;
  countrycurr char(100) description "Currency",;
  countrycap char(100) description "Capital")
create index id on country (id)
insert into country (countryname, countrycurr, countrycap);
  values ("United Kingdom","Pound sterling","London"),;
    ("United States of America","United States dollar","Washington, D.C."),;
    ("France","Euro","Paris"),;
    ("Italy","Euro","Rome"),;
    ("Germany","Euro","Berlin"),;
    ("Brazil","Brazilian real","Brasilia"),;
    ("Thailand","Thai baht","Bangkok")
select * from country

https://www.lianja.com/doc/index.php/GUID()

Next unique sequence number from the specified table
NEXTVAL
CREATE TABLE cust (acc_num INT , acc_name char(20))
INSERT INTO cust (acc_num, acc_name) VALUES (NEXTVAL, "Smith")
INSERT INTO cust (acc_name) VALUES ("Brown")
INSERT INTO cust (acc_num, acc_name) VALUES (CURRVAL+2, "Jones")
SELECT * from cust

https://www.lianja.com/doc/index.php/NEXTVAL

Description of a table’s indices and statistics
SYSINDEXINFO
SELECT * FROM sysindexinfo

https://www.lianja.com/doc/index.php/SYSINDEXINFO

Description of the primary key columns in the table
SYSPRIMARYKEYS
SELECT * FROM sysprimarykeys

https://www.lianja.com/doc/index.php/SYSPRIMARYKEYS

Specify file extension for index filenames
SET INDEXEXT
set indexext to ".ntx", ".cdx"
index on name to names
use name index names

https://www.lianja.com/doc/index.php/SET_INDEXEXT

Create a single index file from an index tag
COPY TAG
//Open up dbf with a dbx file
use customer
copy tag zip to zip.ndx

https://www.lianja.com/doc/index.php/COPY_TAG

Active multiple index filename
MDX
?mdx(1, account)
invoice.dbx

https://www.lianja.com/doc/index.php/MDX()

Active multiple index filename
CDX
?cdx(1, account)
invoice.dbx

https://www.lianja.com/doc/index.php/CDX()

FOR condition that was used to create an index tag
FOR
set view to accounting
? for(3)
acct = "CAR"

https://www.lianja.com/doc/index.php/FOR()

Test for unique indexes
UNIQUE
use accounts
index on name to name unique
? unique(0)
.T.

https://www.lianja.com/doc/index.php/UNIQUE()

Index key expression
INDEXKEY
use accounts index acc_no, date_paid
? indexkey(1)
acc_no + dtos(date_rec)
set order to 2
? indexkey(indexorder())
dtos(date_paid) + str(amo_paid,11,2)
index on lower(left(company,20)) tag company
? indexkey()
lower(left(company,20))

https://www.lianja.com/doc/index.php/INDEXKEY()

Default index extension
INDEXEXT
? indexext()
.ndx

https://www.lianja.com/doc/index.php/INDEXEXT()

Name of the currently selected master index
INDEXORDER
open database southwind
use customers
set order tag companyname_search
? indexorder()
companyname_search

https://www.lianja.com/doc/index.php/INDEXORDER()

Number of index files matching the pattern
IFILECOUNT
? ifilecount("*.ndx")
         5

https://www.lianja.com/doc/index.php/IFILECOUNT()

Determine whether an index tag was created with the DESCENDING keyword
DESCENDING
set view to accounting
?descending(3)
.T.

https://www.lianja.com/doc/index.php/DESCENDING()

Index key expression
KEY
use accounts index acc_no, date_paid
? key(1)
acc_no + dtos(date_rec)
set order to 2
? key(indexorder())
dtos(date_paid) + str(amo_paid,11,2)
index on lower(left(company,20)) tag company
? key()
lower(left(company,20))

https://www.lianja.com/doc/index.php/KEY()

Index file name
NDX
use accounts index acc_no,date_paid
? ndx(1)
acc_no.ndx
set order to 2
? ndx(indexorder())
date_paid.ndx

https://www.lianja.com/doc/index.php/NDX()

Name of the currently selected master index
ORDER
open database southwind
use customers
set order tag companyname_search
? order()
companyname_search

https://www.lianja.com/doc/index.php/ORDER()

Name of an index file or index tag
TAG
?tag(2)
LAST_NAME
tag("alternate.dbx",3)
ZIPCODE

https://www.lianja.com/doc/index.php/TAG()

Check whether index exists
INDEXEXISTS
if indexexists("southwind","customers","products","productid")
    open database southwind
    use products order productid
else
    open database southwind
    use products
    index on productid tag productid
endif

https://www.lianja.com/doc/index.php/INDEXEXISTS()

Number of tags in the active .dbx file
TAGCOUNT
use accounts
? tagcount()
          4
set order to 0
?tagcount()
         0

https://www.lianja.com/doc/index.php/TAGCOUNT()

Number of an index tag
TAGNO
?tagno("city")
         3
?tagno("last_name", "customer.dbx", "customer")
         1

https://www.lianja.com/doc/index.php/TAGNO()

Key expression of an index tag
TAGEXPR
open database southwind
use customers in 1
select 2
use orders
?tagexpr(2,"customers")

https://www.lianja.com/doc/index.php/TAGEXPR()

FOR expression of an index tag
TAGFOR
open database southwind
use customers
? tagfor("customerid") // returns empty string
? tagfor("label_germany") // returns 'CONTAINS("Germany", .f.)'
? tagno("label_germany", "customers.dbx", "customers") // returns 'CONTAINS("Germany", .f.)'

https://www.lianja.com/doc/index.php/TAGFOR()

Key expression of an index tag
TAGKEY
open database southwind
use customers in 1
select 2
use orders
?tagkey(2,"customers")

https://www.lianja.com/doc/index.php/TAGKEY()

Test whether an index tag is unique
TAGUNIQUE
open database southwind
use customers in 1
select 2
use orders
?tagunique(2,"customers")

https://www.lianja.com/doc/index.php/TAGUNIQUE()

Character expression in inverse character sequence
DESCEND
index on descend(name) to descendname
seek descend("Smith")

https://www.lianja.com/doc/index.php/DESCEND()

Determine whether an index tag is a candidate index tag
CANDIDATE
if tagcount() > 0
    for i = 1 to tagcount()
        ? "Candidate status for " + tag() + " is " + ltos(candidate(i))
    next
endif

https://www.lianja.com/doc/index.php/CANDIDATE()

Function used in the creation and searching of descending order tag indexes
DBXDESCEND
index on name tag descendname descending
seek dbxdescend("Smith")

https://www.lianja.com/doc/index.php/DBXDESCEND()

Return optimum ICACHE size for a given index expression
ICACHE
use names index names
size = icache(indexkey(1))
set icache to size
reindex
use names
set icache to icache(name+left(address, 5))
index on name+left(address, 5) to names

https://www.lianja.com/doc/index.php/ICACHE()

Determine whether a specified key value already exists
KEYMATCH
m_val=part_no
if keymatch(m_val)
    dialog box "This part number already exists!"
endif

https://www.lianja.com/doc/index.php/KEYMATCH()

Verify presence of an index key
SEEK

 

use accounts in 1
use supp in 2 index name
select 1
display name
BCD CORPORATION
? iif(seek(name,supp),supp_name,"***name not on file***")
BCD CORPORATION

https://www.lianja.com/doc/index.php/SEEK()

create index lname;
  on example (lower(last_name))
alter index lname;
  on example;
  rebuild
drop index lname;
  on example

https://www.lianja.com/doc/index.php/Using_Recital_SQL

create index lname;
  on example (lower(last_name))
alter index lname;
  on example;
  rebuild
drop index lname;
  on example

https://www.lianja.com/doc/index.php/Using_Lianja_SQL

use orders
index on orderid tag orderid unique
index on orderid+dtos(orderdate) tag order_date
index on customerid+str(orderid) tag cust_order for empty(shippeddate)
index on dtos(orderdate) tag latest descending
use orders
reindex

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Lianja

Indexing

Published on by homunizam1 Comment

Q:
The last line below gives ‘Data type mismatch‘ error. I have also tried ‘(RID_Exe+MachineName)’ as per the docs but then I get ‘Expression was expected’ error.

Code:
create table SWMEXEMACHINES
alter table SWMEXEMACHINES add column RID_Exe numeric(18,0) not null 
alter table SWMEXEMACHINES add column MachineName char(30) not null 
alter table SWMEXEMACHINES add column Enabled bit null 
alter table SWMEXEMACHINES add primary key (RID_Exe,MachineName) tag pk_SWMEXEMACHINES


A:
You are trying to create an index on mixed data types. You need to make sure the expression results in a fixed length character string. You cannot concatenate a string to a numeric value in Lianja.

Code:
alter table SWMEXEMACHINES add primary key (str(RID_Exe),MachineName) tag pk_SWMEXEMACHINES

The indexes are much more functional in Lianja than other SQL databases as they can be created on very complex expressions.

Q:
I imported a VFP DBC into Lianja. Where do I configure the Indexes? I don’t see where do I set them.
A:
After you imported your VFP dbc the indexes that you had are recreated in Lianja format.
You can open the database with OPEN DATABASE in the console, open a table with USE, then use LIST STATUS to view the indexes.
You can create new indexes using the VFP-style INDEX ON command or the SQL CREATE INDEX command.

Q:
Would you say that I don’t need to create indexes in new tables I create in Lianja? Do LIanja needs the indexes to be created or Lianja creates them as needed?
A:
In most cases Lianja will build the indexes as you develop your app visually. There are cases however (e.g for SQL optimization) where you need to create indexes manually.

Q:
I cannot get this working. As far as I can tell FK’s need to work on indexes (where I am accustomed to adding them to columns). This example may look awkward but it shows what I want to achieve:

Code:
CREATE TABLE supplier (SuppId i PRIMARY KEY, SuppRef c(10) UNIQUE, SuppName c(40) UNIQUE)
CREATE TABLE purchase_order (POId i PRIMARY KEY, PO_SuppRef c(10), POtotal n(10,2))
CREATE INDEX sup_idx1 ON Supplier (SuppRef)
CREATE INDEX po_idx1 ON purchase_order (PO_SuppRef)
ALTER TABLE purchase_order ADD FOREIGN KEY po_idx1 TAG po_fk1 REFERENCES supplier TAG sup_idx1

But this gives an error about ‘TAG’ being an unrecognized phrase. Please help with the code to join
purchase_order.PO_SuppRef -> supplier.SuppRef
A:
Try this:

Code:
CREATE TABLE supplier;
 (SuppId int PRIMARY KEY, SuppRef char(10) UNIQUE, SuppName char(40) UNIQUE)
CREATE TABLE purchase_order;
 (POId int PRIMARY KEY,;
  PO_SuppRef char(10) FOREIGN KEY REFERENCES supplier TAG SuppRef,;
  POtotal num(10,2))

Note: UNIQUE creates a Tag index with the same name as the field.
Also, using in-built Lianja UI functionality such as the Relationship Builder, Instant Search, Instant Selections and Grid sorting indexes are generated and selected automatically.
The index is only created by

Code:
ALTER TABLE purchase_order
Code:
ADD FOREIGN KEY (po_suppref) [TAG fk1]

or when you specify the column FOREIGN KEY constraint when creating a column.

The persistent relationship on a particular column is handled by FOREIGN KEY …. REFERENCES or ALTER COLUMN … REFERENCES and does not create an index as an index is not required in the column’s table. An index in the referenced table is required and must be specified in the REFERENCES clause.

 

Indexing

Published on by homunizamLeave a comment

Indexing on a logical is not allowed for performance reasons.


if you really need to build the index you can convert the logical to a string, but we don’t recommend it unless there’s really no other way around it.


Q: 
LARGE table (over 4 millons records and over 1.5gb) with indexes.How do i replace te SET KEY TO xxx to get a similar performance? (and to get full index optimization)
A:
You can use SET FILTER, or you may find Virtual Tables to be helpful


Lianja will optimize on indexes when it can so in your case you just need to have an index on “country” and it will use that and not scan the whole table.
You can experiment using the “explain” command to see how it will perform the query.
So…
index on country tag country
Then…
explain select * from tablename where country=”USA”
Also worth noting is that Lianja will also use filtered indexes where possible.


Q:
In vfp you would define an index of type candidate. 
A:
Just use the INDEX ON command and the UNIQUE keyword, e.g. in the Console:
open database southwind
use employees
index on trim(lastname)+” “+firstname unique tag fullname
Or in SQL, specify the UNIQUE keyword in the CREATE INDEX command, e.g. in the Console:
create unique index sqlfullname on employees (trim(lastname)+” “+firstname)


You can index on logical fields – they just need to be converted to character first – use LTOS().


Q: 
How I can create a normal or production index for deleted records of lianja table?
A: 
Indexing on a logical is not allowed for performance reasons. 
index on etos(deleted()) to adu_del