Index utilization is not as per rule?

[engiri]

Hi,

THrough a reporting tool I have joined
two large tables and there is no index
in that tables.

Half way through the query we found there is no
index in that tables and we created with out cancelling
the queries.

After the index creation it seems the query
running fast.. and I think it started using the index.

is it correct?

Please suggest.

Thanks and regards
Giri

[SANJAY_G]

I don't think a query can change its execution plan midway.. What makes you feel that the query started running faster through midway ?

[engiri]

The query retrived 500 records per second before index creation..
where as after that it retrieved 1500 per second.

Thanks

[davey23uk]

so you re-ran after you created the index? then there is a good chance it will use the index

[slimdave]

I expect that any speed-up after creating the index was due to the presence of the table blocks in the buffer cache. The index creation kind-of pre-loaded the sga with the blocks required by the query.

[TomazZ]

Good one, slimdave!
Engiri, how do you count the rows per second?

[jmodic]

Originally posted by slimdave
I expect that any speed-up after creating the index was due to the presence of the table blocks in the buffer cache. The index creation kind-of pre-loaded the sga with the blocks required by the query.

I wouldn't agree with this one, unless the table is rather small.

When creating the index, oracle actually performs full scan on the table. And FTS only loads a limited portion of blocks at the time into the buffer cache, recycling previously useed blocks (presumably) from the same table scan. Hence, at the end of the index creation, only a relatively small portion of the table blocks will be held in the buffer cache.

It is very likely that any speed-up after creating the index was due to the changed execution plan of the query as a direct result of the inew indexe's existance.

[TomazZ]

Really?
Oracle will change execution plan during execution of one statement?

Or the table could be defined as cached.

[jmodic]

Originally posted by TomazZ
Really?
Oracle will change execution plan during execution of one statement?

Or the table could be defined as cached.

But of course not! That can't happen. I was refering to spead-ups in any subsequent reruns of the query after the index creation.

I thought slimdave was refering to re-runs too, but now after your intervention I realised he perhaps was actually talking about the speed-up of the initial query, during which the index was created. So in this light his remark makes much more sence - however I still doubt table blocks buffered in cache because of index creation could realy have any significant impact on the initial query performance. Moreover, I sincirely doubt any query would suddenly became three times faster in the midle of the execution because of something like that. And very good remark from your side: "Engiri, how do you count the rows retrieved per second during execution of the query?" (although there are quite some techniques to get this during the very execution of the long running query).

[slimdave]

Originally posted by TomazZ
Or the table could be defined as cached.

Ah, precisely -- but even if the blocks get marked as least recently used, that doesn't always mean they will get aged out straight away.

And SELECT execution plans getting changed part way through? I think not.

Ask yourself how Oracle would identify the thousands of rows returned prior to the change in execution plan, so that it could eliminate those from the post-change query result. Not a feasible process.

Unless, of course, this is not a single query, but a cursor loop with a select inside.