• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
I'm wondering if there's a way to do basic catalog searches in SQL that's more efficient than "LIKE." For instance, to search for authors with last name = "Lasch"
I'm doing this: select z13_author,z13_title from z13 where upper(z13_author) like 'LASCH%' It works ok, but it seems like I might be missing an index or something.
The catalog searches I'm trying to do in SQL are: All Fields, Title Words, Exact Title, Periodical Title, Words, Exact Periodical Title, Author Words, Author - Corporate, Keywords, Subject, System number
Is there any easier way?

Resolution:
You can search as you describe on the fields in the Z13: Author, Title, call number, and imprint.

You could also search fields indexed in the Z11 (standard numbers) in the same way. (The Z01 Headings could be searched and then joined with the Z02 via the z01_acc_sequence number to get the z02 doc number; that's a bit more complicated. Searching the Words with SQL is not possible.)

In 15.2-up you could search the Z00R fields. Searching "words" in Z13 fields in SQL could be done ( select z13_rec_key from z13 where z13_title like '%bird%' and z13_title like '%cat%';). But this would be very inefficient.

Currently (as seen in UTIL-A-17-14) the z13 table has just a single Oracle index (the z13_id, built on the z13_rec_key -- the doc number). One could define additional Oracle indexes (following the examples in the ./alephm/sql_tab directory) on the z13_title, z13_author, etc. This would greatly improve the performance of SQL LIKE searches such as LIKE 'LASCH%', where you are starting with the first word in the fields. (Searches such as '%bird%' would not be improved.)