Index Spool/Eager Spool

Hey Guys,
I have a good one!
What would cause the server to use a huge index but then perform a index
spool/eager spool action in the query plan and basically recreate the
whole index?
Thanks,
FrankThe query optimizer may have chosen a plan that requires the rows to be in a
different order for two different operations. For example, the optimizer
may choose to process the restrict condition (WHERE restrict_condition)
before processing the join condition (ON (join_condition)), where the index
used to process the restrict condition is in a different order than is
needed to process the join. If the execution plan contains an iteration
step, then the eager spool may be used to minimize the number of reads
required to satisfy the join. The SEEK:() predicate on the Index Spool
operator restricts the rows that are copied into tempdb, which minimizes the
number of reads required for lookups in each iteration.
"Frank W" <me@.frankwisniewski.net> wrote in message
news:8zhUe.245376$gL1.240016@.tornado.texas.rr.com...
> Hey Guys,
> I have a good one!
> What would cause the server to use a huge index but then perform a index
> spool/eager spool action in the query plan and basically recreate the
> whole index?
> Thanks,
> Frank

index creation

Hi, Folks

I have got a big table containing huge data, I am trying to create a composite index on three columns, the three columns are varchar2 datatype, but I notice(and confirm) that the data in these columns are numeric. in other words, I may change their datatype to be Numeric, hence, I have got two choices to create index either upon varchar, or Nuerice, can you tell me which way has the better performance to retrive data ?

Any advice will be highly appreciated.

xiongOriginally posted by xli
Hi, Folks

I have got a big table containing huge data, I am trying to create a composite index on three columns, the three columns are varchar2 datatype, but I notice(and confirm) that the data in these columns are numeric. in other words, I may change their datatype to be Numeric, hence, I have got two choices to create index either upon varchar, or Nuerice, can you tell me which way has the better performance to retrive data ?

Any advice will be highly appreciated.

xiong
If all the values in the column are menat to be numeric, then NUMBER is a better choice than VARCHAR2. It will use fewer bytes, and comparisons will be faster.