Sybase SQL Anywhere – Le novità della versione 11

Dallo white paper:  TOP 10 COOL NEW FEATURES 

UNLOAD to a Variable

I comandi LOAD e UNLOAD ora lavorano tanto con le variabili quanto con i file. Questo non significa solo che è possibile usare una variabile per tenere la specifica di un file, significa loading e unloading di dati memorizzati nelle variabili così come nei files. Si possono UNLOAD tutte le righe e le colonne di una tabella in una stringola variabile di tipo stringa, e al contrario: LOAD una tabella da una stringa:

BEGIN DECLARE s LONG VARCHAR; UNLOAD TABLE t1 TO s; LOAD TABLE t2 FROM s; END;

Ecco alcuni vantaggi nell’utilizzo di variabili al posto di file:

E sì, funziona con UNLOAD SELECT così come con UNLOAD TABLE, così tu puoi scrivere una query per avere un controllo completo di cos’è memorizzato nella variabile. Questa funzionalità è impressionanante. Per anni è stata richiesta e averla a disposizione apre infinite possibilità! Precedentemente erano già state introdotti funzionalità che risolvevano alcuni problemi come il miglioramente della funzione di aggregazione LIST. Usando le funzioni LIST e STRING insieme con il delimitatore di LIST e la clausola ORDER BY è possibile eseguire una manipolazione di stringa orientata all’insieme. Per esempio, si può mettere in join molte tabelle e mettere i dati in una pagina Web ben formattata in HTML con una singola SELECT.

Non sono discorsi, ma fatti. Vantaggi si sono avuti a suo tempo con i miglioramenti introdotti nella funzione LIST, altrettanti vantaggi si avranno ora con “UNLOAD to a variable”.

• FROM OPENSTRING

  La nuova clausola OPENSTRING permette di far riferimento a un file o a una variabile nella clausola FROM di una SELECT e fa si che SQL Anywhere tratti i dati presenti in quel file o in quella variabile come un insieme di righe e di colonne. E’ qualcosa di simile all’uso delle proxy table per trattare un file come una tabella per mezzo del middleware di accesso remoto ODBC, ma è un modo più facile e flessibile, oltre al fatto che OPENSTRING consente di utilizzare esattamente allo stesso modo anche le variabili.

  Questa caratteristicha è davvero stupenda! Osserviamo il seguente esempio di uso di OPENSTRING congiuntamente con UNLOAD SELECT INTO una variabile (usiamo SYSDOMAIN giusto come esempio):
  CREATE VARIABLE @var LONG VARCHAR; UNLOAD SELECT * FROM SYSDOMAIN INTO VARIABLE @var; CREATE VIEW V AS SELECT * FROM OPENSTRING ( VALUE @var ) WITH ( domain_id SMALLINT,   domain_num VARCHAR ( 128 ),   type_id SMALLINT, “precision” SMALLINT ) AS T;Copia il set result nella variabile di connection-level @var, poi usa una vista V per fare riferimento a quel set result. Si può creare il valore per @var in una procedura connessa. Questo può essere un buon modo per gestire il settaggio dell’opzione locale o delle cache di connessione locale di dati a caldo (evitando la contesa di righe a caldo). E’ bene stari attenti al modo in cui si usa questa possibilità, ma OPENSTRING è effettivamente un’utensile pulito da tenere nella tasca dei pantaloni, pronto all’uso.

E’ curioso come queste nuove funzionalità troveranno applicazione nel mondo reale. Non è facile prevedere cosa diverrà di moda. Qualche volta, qualche volta le nuove funzionalità. come nel caso della funzione LIST prendono il volo, altre volte no come l’incubo sintattico che è l’unione ricorsiva nello standard ANSI. Riteniamo che OPENSTRING possa divenire una delle più popolare, lo stesso UNLOAD into una variabile.

C’è un altra caratteristica che varrebbe la pena menzionare, non è presente nelle Top 10 poiché era stata introdotta senza clamore nella versione 10.0.1: i trigger INSTEAD OF che consentono di scrivere triggers su tabelle e viste e che sono eseguiti al posto della corrispondente azione del comando da cui sono scatenati. In altre parole, se c’è un trigger INSTEAD OF INSERT ON T, quando è eseguito il comando INSERT T questi scatena il trigger ma la riga non viene inserita. Ogni azione che si voglia eseguire come risultato dell’INSERT T deve essere inserita dentro il trigger. La parte cool è che questo permette di scrivere trigger sulle viste, anche su viste che non sono aggiornabili. Il fatto che la vista sia aggiornabile non ha importanza, infatti mettendo un trigger all’INSERT, all’UPDATE o alla DELETE questo non provoca l’inserimento, la modica o la cancellazione se esiste il corrispondente trigger INSTEAD OF, e quelle azioni si possono fare se lo si desidera solo all’interno del trigger (eccetto per la COMMIT, naturalmente, questa restrizione continua a valere).

• Viste materializzate immediate

  Di seguito un esempo di vista materializzata utilizzata per creare una lista di impiegato di un dipartimento:

  CREATE MATERIALIZED VIEW DeptList AS SELECT Departments.DepartmentID, Employees.EmployeeID, Departments.DepartmentName, Employees.GivenName, Employees.Surname, Employees.Phone FROM Departments INNER JOIN Employees ON Employees.DepartmentID = Departments.DepartmentID;

  Tutte le viste materializzate devono essere inizializzate prima di poter essere utilizzate:

  REFRESH MATERIALIZED VIEW DeptList;

  Con una vista materializzata ordinaria (manuale), se la tabella sottostante viene modificata e sulla vista non avviene un’altro REFRESH si vedranno i vecchi dati:

  UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 1336; SELECT * FROM DeptList ORDER BY DepartmentName, Surname, GivenName; DepartmentID EmployeeID DepartmentName GivenName Surname Phone ============ ========== ============== ========= ======= ========== 300          1336       Finance        Janet     Bigelow 6175551493

  A partire da SQL Anywhere 11, si possono cambiare le viste in modo che gli update delle tabelle sottostanti siano immediatamente applicati alla vista materializzata; ecco i passi che devono essere fatti:

  TRUNCATE TABLE DeptList; CREATE UNIQUE INDEX xid ON DeptList ( DepartmentID, EmployeeID ); ALTER MATERIALIZED VIEW DeptList IMMEDIATE REFRESH; REFRESH MATERIALIZED VIEW DeptList;

  La TRUNCATE TABLE è necessaria se la vista manuale è stata già popolata con i dati: la vista deve essere vuotata prima di cambiarla a “immediata”. La CREATE UNIQUE INDEX è un ulteriore requisito per le viste “immediate”: SQL Anywhere deve poter trovare le righe quanto ne ha necessità per aggiornarle. L’ALTER cambia il refresh mode della vista a IMMEDIATE, e una volta cambiato il REFRESH è necessario inizialmente popolare la vista… sì, si devono fare diverse cose, ma queste devono essere fatte una volta.

  Ora, se si aggiorna una tabella base e si effettua una query sulla vista, i nuovi dati sono giusti senza che sia necessario fare altro.

  explicit REFRESH: UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 1336; SELECT * FROM DeptList ORDER BY DepartmentName, Surname, GivenName;

   
   
  DepartmentID EmployeeID DepartmentName GivenName Surname Phone
  ============ ========== ============== ========= ======= ==========
  400          1336       Marketing      Janet     Bigelow 6175551493

  Ecco la vera ragione per cui le viste materializzate sono così interessanti: non è necessario che si sappia che la vista esiste per averne beneficio.

• Compatibilità con le versioni precedenti

  Se si ha un database creato con la versione 10, lo si può far girare con SQL Anywhere 11 senza doverlo ricostruire. Ciò è imporante con database di grandi dimensioni: si può partire iniziando a testare la versione 11 senza perdere tempo. Quando fu lanciata la versione 10, fu una sorpresa che fosse un prerequisito dover ricostruire ogni database creato con versioni precedenti. Con la versione 9 era di moda utilizzare i vecchi file di database anche quelli creati con la versione 5.5. La versione 10 ha rotto il cammino delle compatibilità e lo ha fatto per diverse buone ragioni: eliminare un mucchio di codice vetusto nel motore del database, eliminare lo sforzo di manutenere quel vecchio codice e liberare tempo da spendere in aree molto più importanti… come le nuove potenti caratteristiche.

  Con la versione 11 è partito un nuovo cammino di compatibilità: nessuna ricostruzione è richiesta per andare dalla 10 alla 11. Naturalmente se si salta dalla 9 alla 11 si deve ricostruire. E qui c’e’ un punto da sottolineare: se le performace sono importanti nel Vostro progetto vi consigliamo di ricostruire comunque anche passando dalla 10 alla 11. Il solo modo per avvantaggiarsi dei miglioramenti del nuovo formato del database della versione 11 è fare un completo unload/reload.

• Stored Procedures in Perl, PHP, C#, VB

  Con la versione 11 di SQL Anywhere ora si possono scrivere stored procedures in nove linguaggi diversi: sono stati aggiunti Perl, PHP, C# e Visual Basic, mentre continuano ad essere supportati Java, C, C++ e naturalmente lo standard SQL ANSI/Watcom e il Transact SQL. In realtà si dovrebbe parlare di più di nove linguaggi; per esempio ci dovrebbe lavorare una versione .Net 2.0 CLR del Fortran, quel “dovrebbe lavorare” è un espressione tipica del modo di esprimersi usato dal Supporto Tecnico. Le particolarità con cui le stored procedure sono supportate nei differenti linguaggio variano; per esempio, il VB può ritornare i visual set, mentre il Perl può ritornare solo le stringhe di tipo LONG VARCHAR. E’ importante anche notare che le procedure scritte in ogni linguaggio non-SQL ora girano in ambienti eseguibili che sono completamente separati dal database server stesso. Ciò significa che se una store procedure crasches, questo non provoca la caduta del database. E ancora una volta i dettagli variano; per esempio si ha un ambiente esterno Perl per ogni connessione, mentre con VB c’è solo un ambiente esterno inizializzato per ogni database. La tabella seguente mostra una panoramica di quel che accade a seconda del linguaggio con cui è scritta la stored procedure.. Ambiente esterno                   Le procedure possono ritornare ========          ===========      ============= Perl              connection       LONG VARCHAR PHP               connection       LONG VARCHAR C#, VB            database         result set Java              database         result set C, C++            connection       result set SQL               n/a              result set La possibilità di utilizzare chiamate a store procedure scritte in linguaggi diversi è un grande traguardo. Precedentemente le procedure in C e in C++ giravano dentro il database server, con risultati catastrofici nel caso in cui il codice provocasse un crache: non si trattava di una semplice caduta della connessione utente, ma della caduta del server con effetti negativi per tutti.

  Un altro importante traguardo che è stato raggiunto è l’uso della connessione corrente al database in una procedura scritta in un linguaggio non-SQL. In altre parole, non si deve avviare una nuova connessione per passare un comando SQL al database che aveva chiamato la procedura. Giusto per fare un esempio del grande obiettivo raggiunto: si potrebbero mettere nel database le API che realizzano un utility che confronta due file lavorando con documenti immagazzinati nei blob nel database, così l’HTTP server built-in in SQL Anywhere potrebbe utilizzare i risultati del confronto attraverso chiamate web services dal browser.

• In-Memory Server Mode

  L’opzione “in-memory server mode” è un modo totalmente nuovo di operare per migliorare la velocità d’inserimento e aggiornamento. Si può creare e aggiornare un file di database nel modo classico di SQL Anywhere e poi farlo girare in una delle due modalità possibili: “Never Write” o “Checkpoint Only”. Si può passare da una modalità all’altra e tornare indietro a quella classica fermando e facendo ripartire il modotre…. E’ lo stesso software, che usa differenti parametri da linea di comando. Attenzione: SQL Anywhere in questo modo non sta introducendo alcun tipo di motore non-transactional auto-commit come MyISAM e MySQL. SQL Anywhere continua ad essere un sistema di gestione di dati fully-relational, fully transactional, e fully ACId anche se sta girando “in-memory mode”.

  ACId significa Atomica, Consistente, Isolata e Persistente, le quattro caratteristiche della transazione. Persistenza significa che il COMMIT è permanente. Stiamo utilizzao la lettera minusca per la “d” in ACId poiché la modalità “Checkpoint Only” preserva la persistenza della transazione fino all’ultimo checkpoint, ma non permette il ripristino dopo quel punto.

  L’In-memory mode è molto efficace quando la RAM cache del database è abbastanza grande da contenere l’intero database, quando si stanno facendo un mucchio di inserimenti, aggiornamenti e commit, e quando si è disposti a sacrificare un po’ di sicurezza per ottenere la massima velocità. Si pensi per esempio ad un’applicazione di monitoraggio continuo come ad una tipica applicazione candidata, dove un enorme numero di dati viene ricevuto in un lasso di tempo molto breve,dove è fondamentale stare al passo con l’input dei dati piuttosto che garantire sempre che il 100% dei dati verrebbe ripristinato di fronte ad un crash. Entrabi gli in-memory modes consentono l’aggiornamento dei dati nella cache; in altre parole il ““Never Write” non significa “read only”. Con il Checkpoint Only mode, l’aggiornamento dei dati sul file è fatto al momento del CHECKPOINT. Con Never Write, sta a noi salvare i dati se la performance è importante; ci sono tre modi veloci per farlo: effettuare il backup del database, utilizzare l’utility di unload del database o attraverso comandi specifici di UNLOAD TABLE.

• Comando MERGE

  The new MERGE statement brings the power of set-oriented processing to bear on INSERT, UPDATE and DELETE logic when you need to copy data from one table to another. Here is a simple example that starts with an input table containing a set of transactions:

  CREATE TABLE transaction (
  part_number INTEGER NOT NULL PRIMARY KEY,
  action VARCHAR ( 6 ) NOT NULL,
  description LONG VARCHAR NULL,
  price DECIMAL ( 11, 2 ) NULL );
  INSERT transaction VALUES ( 1, ‘Add’, ‘widget’, 10.00 );
  INSERT transaction VALUES ( 2, ‘Add’, ‘gizmo’, 20.00 );
  INSERT transaction VALUES ( 3, ‘Add’, ‘thingie’, 30.00 );
  INSERT transaction VALUES ( 4, ‘Add’, ‘bouncer’, 40.00 );

  Here’s the output table, plus the MERGE statement which turns the four input ‘Add’ transactions into INSERT operations:

  CREATE TABLE part (
  part_number INTEGER NOT NULL PRIMARY KEY,
  description LONG VARCHAR NOT NULL,
  price DECIMAL ( 11, 2 ) NOT NULL,
  exceptions LONG VARCHAR NOT NULL DEFAULT ” );
  MERGE INTO part USING transaction ON PRIMARY KEY
  WHEN NOT MATCHED AND transaction.action = ‘Add’
  THEN INSERT ( part_number,
  description,
  price ) VALUES ( transaction.part_number, transaction.description,transaction.price ) WHEN MATCHED AND transaction.action = ‘Change’ THEN UPDATE SET part.description = COALESCE ( transaction.description, part.description ),
  part.price
  = COALESCE ( transaction.price,
  part.price )
  WHEN MATCHED AND transaction.action = ‘delete’
  THEN DELETE
  WHEN MATCHED AND transaction.action = ‘Add’
  THEN UPDATE SET part.exceptions
  = STRING ( part.exceptions,
  ‘duplicate Add ignored. ‘ )
  WHEN NOT MATCHED AND transaction.action = ‘Change’
  THEN INSERT VALUES ( transaction.part_number,
  transaction.description,
  transaction.price,
  ‘Change accepted, new part inserted. ‘ )
  WHEN NOT MATCHED AND transaction.action = ‘delete’
  THEN SKIP;
   

  The MERGE statement works as follows: • The INTO clause specifies the output or target table.

  • The USING clause names the input or source table.

  • The ON clause specifies how row matching is done, in this case using the primary keys for both tables.

  • The firstWHEN clause says what to do when an ‘Add’ transaction doesn’t match any row in the part table: INSERT a new row.

  • The second WHEN says what to do with a matching ‘Change’: UPDATE the description and/or price columns.

  • The third WHEN turns a matching ‘delete’ transaction into a DELETE operation.

  • The fourth WHEN records an error:‘duplicate Add ignored.’

  • The fifth WHEN turns a mismatching ‘Change’ into an INSERT.

  • The lastWHEN simply ignores an attempt to ‘delete’ a part that isn’t there.

  In the first run of the MERGE, all it does is execute the firstWHEN clause four times to fill the part table:
  part_number description price exceptions =========== =========== ===== ========== 1           widget      10.00
  2           gizmo       20.00
  3           thingie     30.00
  4           bouncer     40.00

  Here’s where it gets interesting; six new transactions that exercise all the remaining WHEN clauses:

  TRUNCATE TABLE transaction; INSERT transaction VALUES ( 1, ‘Change’, NULL, 15.00 ); INSERT transaction VALUES ( 2, ‘Change’, ‘gizmoid’, NULL ); INSERT transaction VALUES ( 3, ‘delete’, NULL, NULL ); INSERT transaction VALUES ( 4, ‘Add’, ‘bouncer’, 45.00 ); INSERT transaction VALUES ( 5, ‘Change’, ‘gong’, 55.00 ); INSERT transaction VALUES ( 6, ‘delete’, NULL, NULL );

  Here’s what the part table looks like after running the MERGE again:

  part_number description price exceptions =========== =========== ===== ========== 1           widget 15.00 2           gizmoid 20.00 4           bouncer 40.00 Duplicate Add ignored. 5           gong 55.00 Change accepted, new part inserted.

   

  There is a dark side to MERGE, however: it won’t let you have multiple input rows updating one output row. That’s not SQL Anywhere’s fault, it’s a Pointy-Haired Committee decision, and I predict it’s going to be the Number One complaint about MERGE:“The ANSI SQL/2003 standard does not allow rows in target-object to be updated by more than one row in source-object during a merge operation.”The Number Two request? Folks will ask for the ability to call a procedure from a WHEN clause.

• CREATE EVENT … TYPE DEADLOCK

  A new EVENT type has been introduced for catching and reporting on cyclical deadlocks. Deadlocks sometimes occur on busy systems when two different connections each lock a row and then immediately ask for a lock on the row the other guy’s got. Both connections get blocked, neither one can get what it wants, and neither one will ever proceed until some third party takes action to break the deadlock.

  SQL Anywhere itself is that third party; it has always been able to detect deadlocks, and it instantly “resolves” them by picking one of the connections as the victim, issuing a ROLLBACK and returning an error message to that application.

  So far, so good; one user wins, the other can try again, and the maintenance programmer can start looking for the error… and it almost always is a programming error, some flaw in the order in which SQL statements are executed. The problem is that the standard deadlock error message gives absolutely no clue about what tables or rows were involved in the deadlock, or what either connection was doing at the time. The new CREATE EVENT … TYPE DEADLO K statement fixes that by giving you the ability to record and report on all the pertinent details: the connection numbers and user ids for both connections, and the actual SQL statements that were executing at the time the deadlock occurred.

  Here is an example of a deadlock event handler that captures the details and stores them in a table:

  CREATE EVENT deadlock TYPE DEADLOCK HANDLER
  BEGIN
  — Copy fresh entries from sa_report_deadlocks()
  INSERT deadlock (
  snapshotId,
  snapshotAt,
  waiter,
  who,
  what,

   

  object_id,
  record_id,
  owner,
  is_victim,
  rollback_operation_count )

   

  SELECT *
  FROM sa_report_deadlocks()
  WHERE NOT EXISTS (
  SELECT * FROM deadlock
  WHERE deadlock.snapshotId = sa_report_deadlocks.snapshotId
  AND deadlock.snapshotAt = sa_report_deadlocks.snapshotAt );
  COMMIT;
  MESSAGE STRING ( ‘dIAG ‘, CURRENT TIMESTAMP,
  ‘ deadlock’ ) TO CONSOLE;
  END;

   

  The built-in sa_report_deadlocks() procedure isn’t new, but it has been greatly improved for SQL Anywhere 11. It returns a result set containing at least two rows for each deadlock that has occurred since the server started: one for the victim, one for the other connection. The INSERT copies that data into a permanent user-defined table called “deadlock”. The WHERE NOT EXISTS is needed to prevent old rows from being copied again when another deadlock happens.

  Here’s what the table looks like; it has one extra column “row_number” to act as an artificial primary key, plus columns for everything returned by sa_report_deadlocks():

  CREATE TABLE deadlock (
  row_number BIGINT NOT NULL
  DEFAULT AUTOINCREMENT,
  snapshotId BIGINT NOT NULL,
  snapshotAt TIMESTAMP NOT NULL,
  waiter INTEGER NOT NULL,
  who VARCHAR ( 128 ) NOT NULL,
  what LONG VARCHAR NOT NULL,
  object_id UNSIGNED BIGINT NOT NULL,
  record_id BIGINT NOT NULL,
  owner INTEGER NOT NULL,
  is_victim BIT NOT NULL,
  rollback_operation_count UNSIGNED INTEGER NOT NULL,
  PRIMARY KEY ( row_number ) );

   

  In order to make this work, you have to tell the server to gather the report data:

  SET OPTION PUBLIC.LOG_DEADLOCKS = ‘ON’;

  It also helps a lot if you enable the ‘lastStatement’ connection property so the report data will show the actual SQL statements that were blocked. The easiest way to do that is to specify the dbsrv11 -zl option when starting the server.

  Figure 5 shows what the following query displayed after a deadlock;“waiter” is the connection number,“who” is the user name,“what” is the CONNECTION_PROPERTY ( ‘lastStatement’ ) and “is_victim” tells you which connection got the

  ROLLBACK:
  SELECT waiter,
  who,
  what,
  is_victim
  FROM deadlock
  ORDER BY row_number;

  FIGURE 5:

  Displaying Deadlock
  Details

• Le piccole cose che contano…

  In every release of SQL Anywhere, many features are introduced with little or no fanfare because nobody thinks they are significant. Sometimes, one of these features becomes hugely popular and important, like the magic DEFAULT TIMESTAMP which works with UPDATE as well as INSERT. I call these features The Little Things That Count, and somewhere in following list is lurking at least one that deserves to be in the Top 10 Cool list, only we don’t know it yet:

  • The function call PROPERTY ( ‘tCPIPAddresses’ ) answers the question “have I connected to the right server?” by telling you what address the server’s listening on; e.g.: 192.168.1.51:49270.

  • You can press Ctrl- to comment and uncomment blocks of code in dbisql.

  • You can use the DEFAULT keyword in an UPDATE as well as INSERT; e.g., UPDATE t SET c = DEFAULT.

  • ENDIF and END IF can be used interchangeably; no longer do you need to remember which one goes with the IF statement and which one with the IF expression.

  • Line numbers are displayed next to your code in the dbisql “SQL Statements” frame.

  • You can use the ampersand “&” as a line continuation character in @configuration files, which really helps when you’re setting up long parameter strings for High Availability and the built-in HTTP server.

  • JSON or JavaScript Object Notation is now available for web service result sets, in addition to HTML, SOAP, XML and raw formats… maybe JSON will save us all from XML.

  • The new PRIORITY option can be used by an application to increase or decrease the priority level at which its SQL requests are executed. For example, a high-speed OLTP connection can set its priority to ‘Critical’ while a long-running OLAP query can use ‘Background’… or any one of the five other levels between those two extremes.

  • Even if you don’t care about UNLOAD into a variable, one of the other enhancements to LOAD and UNLOAD is sure to make you happy: encryption, compression, using LOAD and UNLOAD in dbisql… how about optional transaction logging so you can use LOAD and UNLOAD in a mirrored database environment?

  • Speaking of mirrored databases: now you can run read-only queries against the secondary server in a High Availability  setup; now you can move your heavy OLAP sessions off your primary OLTP server and use the secondary server for something other than just failover.

  • The NewPassword connection parameter lets the user specify a new password even if the current one’s expired. Oh, and that business about expired passwords? There’s a whole mess of new features under the topic of “Login Policies”. It’s not in the Top 10 list because, let’s face it, security is not cool… but logging in without having to call the DBA, that’s  priceless.

• Full Text Search (ricerca testuale)

  If this was a “Top 1 List” then full text search would still be on it: it’s the number one coolest feature in SQL Anywhere 11.With full text search you don’t have to use a separate piece of software to implement fast searches across multiple LONG VARCHAR columns, and you don’t have to store the data outside the database to do it. If you’ve ever run SELECT LIKE ‘%word%’ queries against a large table, you know what “slow” means, and you’re going to love full text search.

  Here is an example of how it works, using a table that contains all 6.5 million entries downloaded from the English version of Wikipedia:
  CREATE TABLE enwiki_entry ( — 6,552,490 rows, 17.2G total
  page_number BIGINT NOT NULL,
  from_line_number BIGINT NOT NULL,
  to_line_number BIGINT NOT NULL,
  page_title VARCHAR ( 1000 ) NOT NULL,
  page_id VARCHAR ( 100 ) NOT NULL,
  page_text LONG VARCHAR NOT NULL,
  PRIMARY KEY CLUSTERED ( page_number ) );

  The first step is to define a text index on the columns to be searched:

  CREATE TEXT INDEX tx_page_text
  ON enwiki_entry ( page_text )
  MANUAL REFRESH;

  The second step is to build the text index, a process that can take quite a long time if the table is very large:

  REFRESH TEXT INDEX tx_page_text
  ON enwiki_entry
  WITH EXCLUSIVE MODE
  FORCE BUILD;

  Once the index is built, however, queries that use the index are very fast. The following SELECT uses the new CONTAINS clause to find all the Wikipedia entries containing the exact phrase “Ayn Rand”:

  SELECT score,
  enwiki_entry.page_title,
  LEFT ( enwiki_entry.page_text, 500 ) AS excerpt
  FROM enwiki_entry
  CONTAINS ( enwiki_entry.page_text, ‘”Ayn Rand”‘ )
  ORDER BY score DESC;

  The CONTAINS clause applies the query string ‘“Ayn Rand”’ to the enwiki_entry.page_text column using the full text index previously defined on that column, thus limiting the result set to rows that match. The CONTAINS clause also returns an implicit “score” column which measures how closely each row matches the query string. The ORDER BY clause uses that column to sort the best matches to the top, and Figure 6 shows the results in a browser display produced by a SQL Anywhere 11 web service. Here’s a tip: Don’t do like I did and leave out an important column from the CREATE TEXT INDEX statement. In Figure 6 the main Wikipedia entry entitled “Ayn Rand” doesn’t appear on the first page, but it should, and it would have if I hadn’t forgotten to include the enwiki_entry.page_title column in the index. When a full text index specifies multiple columns the CONTAINS clause calculates a score that counts both columns, and in this case the row with “Ayn Rand” in the title would have received a very high score for the query string ‘“Ayn Rand”’.

  There are many, many options available with full text searching, I’ve only touched on the basics. Here’s one of the extras: If you build an index on two columns, you can refer to one column in the CONTAINS clause and the search will only look at that column… another reason not to make the mistake I did, better to index on more columns, not fewer, you’ll have more freedom when designing your queries.

  Oh, and by the way: The default boolean operator is AND, just like in Google. In other words, the query strings ‘Ayn Rand’ and ‘Ayn AND Rand’ are the same, and they’re different from ‘Ayn OR Rand’.

  Breck Carter is principal consultant at RisingRoad Professional Services, providing consulting and support for SQL Anywhere databases and MobiLink synchronization with Oracle, SQL Server and SQL Anywhere. He is also author of the SQL Anywhere blog.