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.
Printable View
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.
could you populate the table sequentially on the value to be searched, then do a manual binary search?
No, there is no such thing for PL/SQL tables. You have to search them sequentialy from the first record toward the last one.Quote:
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.
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,
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.