Tuning Queries for Nested Tables

From: Praful Thakkar <thakkar_praful_at_yahoo.com>
Date: Wed, 29 Nov 2000 15:16:08 -0800 (PST)
Message-Id: <10695.123267@fatcity.com>

Hi All,

Anyone used nested tables? If so, any tips for tuning queries involving nested tables?

The scenario is - for a table with million rows in it, there is a field_1 described as a nested table and another field_2 with a tilde-delimited multi-value field. For testing purpose, values of nested table are transposed as a tilde-delimited field. So, for id=1, if I have nested values in field_1 as 1,2 and 3, field_2 contains '~1~2~3~'.

The search with where condition -
field_2 like '%~2~%'
returns resultset in less than 200ms.

The search with where condition appropriate for nested table returns resultset in more than 4 seconds.

I've indexed column with nested table, but no gain whatsoever. Personally, I do not like using multi-value fields in just one column as mentioned earlier. Is there a way in which I can enhance the performance of the queries using nested tables?

TIA.

• Praful

  ---

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. Received on Wed Nov 29 2000 - 17:16:08 CST