Re: Too bad performance of nested table insert operation

Min-Koo Seo wrote:

> Hello.
>
> I am running oracle 8i (8.1.7) in linux kernel version 2.4.25
> (redhat).
>
> What I want to do is migrate existing tables into nested table form to
> save storage. Example of existing table is as follows:
>
> Table K0
> --------
> ID VARCHAR2(11)
> LOC NUMBER
> TYPESTR CHAR(2)
> TYPELEN NUMBER
> LOOKAHEAD CHAR(7)
>
> In this table, I've made B+ tree multicolumn index on
> TYPESTR+TYPELEN+LOOKAHEAD column. However, there are too many
> duplicates in TYPESTR+TYPELEN+LOOKAHEAD, so I want to make those
> columns as parent table, and let other columns exist in nested table.
> (Actually, I want this migration as an alternative implementation of
> inverted index.)
>
> So I proceeded as follows:

<snip>

First, what other indexes are on this table? What are the definitions? What is the original query that is "slow" causing you to think that adding this index would improve anything?

And... this begs the question "WHY!!!????" Is your application/query looking up data by these columns? Just looking at the column names, I would suspect not, but that is because I have no idea about your data or application. Normally, wouldn't you look up data by the ID+LOC and maybe TYPESTR, but length????

It looks like you are making it a lot harder than it needs to be.. While Oracle will let you do the nested tables, it is quite obvious that it will not perform as you had anticipated.

Sometime (most times???) it is better to use the KISS principle (Keep It Simple Stupid)

Michael Austin. Received on Wed May 26 2004 - 14:51:41 CDT