Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    SQL query against z103 table running very slow

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

    We have a request for a report which reguires a search of the cun50 z103 table to get bib system numbers. The plan is to do the following:

    1. Extract from abc50 z30 z30_rec_key into a table.
    2. Run on the substr of the z30_rec_key, (first 9 characters) an sql query against the z103 the bib key.
    3. Run a query with the bib key against the abc01 z00r table.

    The problem is with item 2. We have over 40,000 records from the abc50.z30 table. The query against the z103 runs for hours and doesn't complete. Do you have any suggestions to improve the query? What query is the util f 3/12 use? (which may be similar)

    Below is the query I am using for item 2;

    update bcmus_table
    set bibnum = (select SUBSTR(Z103.Z103_REC_KEY_1,6,9)
    from abc50.z103
    where SUBSTR(Z103.Z103_REC_KEY,6,9) = bibnum
    and SUBSTR(Z103.Z103_REC_KEY_1,1,5) = 'ABC01'

    [From site:]
    Turned on "autotrace" and ran this query. It indicated that a full scan was taking place. I copied the z103 table to my schema and did the following:
    1. Created the table with caching turned on.
    2. Analyzed the table
    3. Indexed the table for the z103_lkr_number
    Modified the query to run against the z103_lkr_number. It ran in minutes.

    See also KB 16384-25265.

    • Article last edited: 10/8/2013