Re: Creating multiple indexes
- From: Yong Huang <yong321@xxxxxxxxx>
- To: mail@xxxxxxxxxxxxx
- Date: Fri, 19 Nov 2010 07:47:48 -0800 (PST)
> why scan the same table (or partition) multiple times to create
> multiple indexes? Would it not be more performant to scan the table
> once and build multiple indexes on the fly?
What you describe is as follows, if I were to add this functionality to
CREATE INDEX statement:
for i in (select rowid rid, col1, col2 from tab) loop
insert into tempseg1 values (i.rid, i.col1);
insert into tempseg2 values (i.rid, i.col2);
end loop;
sort tempseg1 on col1 and rebrand it as index1;
sort tempseg2 on col2 and rebrand it as index2;
I agree. It would be a great performance enhancement and may not need too
much effort to achieve that. But Oracle, and perhaps other RDBMS's, have
no plan to do this because creating multiple indexes by scanning the table
once doesn't happen often. It doesn't hurt to file an enhancement request
though.
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
