Search within a PL/SQL Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Search within a PL/SQL Table

  1. #1
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    I have a procedure that populates a PL/SQL Table.
    I want to search the table for specific values and not use loop from table.FIRST to table.LAST.

    Is there a quick way to search the structure ??
    Rotem.

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    could you populate the table sequentially on the value to be searched, then do a manual binary search?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by rotem_fo
    Hi,
    I have a procedure that populates a PL/SQL Table.
    I want to search the table for specific values and not use loop from table.FIRST to table.LAST.

    Is there a quick way to search the structure ??
    Rotem.
    No, there is no such thing for PL/SQL tables. You have to search them sequentialy from the first record toward the last one.

    The only way I can think of to speed up such scans is to incorporate your table(i).value somehow into the index of the table (i). For example, suppose you are filling your PL/SQL table with some sample words from a dictionary, and you will later search for those words. Suppose you also know that there will less then 1000 records in your table. So instead of indexing those records by i from 1 to N (i=1,2,3,4...n) you might incorporate the initial character ASCII code into your table index, by multiplying it with 1000 and adding i, so instead by continuous index i you'll have a sparce index j=1000*(ASCII(first_character))+i


    word ...i j=ASCII(SUBSTR(word,1,1))*1000+i
    ---- ---- ---------------------------------------
    YES ...1 89001
    NOW ...2 78002
    ABC ...3 65003


    This way you'll end up with a very sparce table, but that is OK as PL/SQL tables support sparsity, and in fact the sparsity does not have any bad influence on performance. So now if you are looking for a word 'XYZ' you'll only have to scan through records with index between 88000 and 88999. If there is only 10 words in a table that begin with 'W', you'll have to go only through those 10 records instead of all 1000 records.

    But, as I said, PL/SQL tables are not wery suitable for searches, so you might better consider using global temporary tables instead of PL/SQL tables in such situations. Global temporary tables can be indexed and you can performe any SQL query on them.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    USe index by tables in PL/SQL which will allow u to create index on pl/sql tables , for large tables , i would prefer global temporary tables.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width