Problem with Index

[DaPi]

Originally posted by kiewer
For me it just still a question whether "FTS+sort" always more expensive then "IFS+access by rowid"

I seems to me that, for reading the WHOLE table, unless you have a VAST amount of memory (i.e. can sort the whole table in RAM, in which case "FTS+sort" should win):

- The amount of disk i/o in "FTS+sort" will increase as N*log(N), BUT these are sequential i/o (faster than random access).

- The amount of disk i/o in "IFS+rowid" will increase as N, BUT these are random access i/o (slower than sequential).

The question is "where is the cross-over?" The log(N) increase will, in practice, be determined by the ratio of the file size to the sort-area. So the more memory you have (with good tuning) the better "FTS+sort" will work, until you get to a critical table size.
(I am assuming that the IFS time is negligible compared with the rest.)

The only answer is to try it and see.

[DaPi]

Addendum:
In the general case, unless the whole table fits in the buffer, "IFS+rowid" would also have to read a lot of the physical blocks several times (worst case, once per row in the block for every block!)

In the case that started this discussion (sorting by timestamp) it is very likely that the data is "almost" sorted by physical location and sucessive reads by rowid would hit the same block.

[kiewer]

Originally posted by slimdave
Could you run timings for your queries -- I see similar patterns in the gets for your example, but faster execution time on the index access.

You are right again :-) I did the dirty test for tables with size 10000, 100000 and 1000000 rows and found approximatly the same or better performance for "IFL+rowid".

The conditions were:
1.Everything is cached in SGA (table and index, so 0% physical reads for "IFL+rowid")
2.The sorts are going to the disk, because I can't dedicate more then 100M sort_area_size on my test box and this isn't enough for sorting 1000000 rows (the sorting column - integer from 1 to 1000000).

Nevertheless that "IFL+rowid" needs 10 times more block reads than "FTS+sort", disk sorts degrade performance significally.

[pando]

do you see sort improvement with 100mb sort_area_size? I have tested with 32mb and 64mb with not much differentce, seems that after passing some limit bigger sort_area_size doesnt imrpove performance

[DaPi]

Originally posted by kiewer
The conditions were:
1.Everything is cached in SGA (table and index, so 0% physical reads for "IFL+rowid")

I suspect that this mean the test is not applicable to the original question which had "millions of rows".

[kiewer]

Originally posted by pando
do you see sort improvement with 100mb sort_area_size? I have tested with 32mb and 64mb with not much differentce, seems that after passing some limit bigger sort_area_size doesnt imrpove performance

My concern was only to fit the sorts into memory (avoid disk sorts). I didn't compare memory sorts with different sort_area_size. IMHO Oracle has one stable algorithm for sorting and only checks whether it could be done in memory or must be splited into disk sorts. If it could be done in memory - so doesn't metter how much of it do you have. Just enough. But once again - this is just my IMHO.

The problem with big sort_area_size what I've expirienced is that optimizer prefers SORT JOIN rather than NESTED LOOPS, which are (SORT JOIN) much slow on my queries.

[kiewer]

Originally posted by DaPi
I suspect that this mean the test is not applicable to the original question which had "millions of rows".

Why not? I've tested with 1 million row table. Do you think it isn't enough?

[DaPi]

OOPS SORRY - need new glasses, miscounted the 0's.

[julian]

Very interesting thread...

[slimdave]

Originally posted by DaPi
In the case that started this discussion (sorting by timestamp) it is very likely that the data is "almost" sorted by physical location and sucessive reads by rowid would hit the same block.

Indeed, as long as there are not heavy deletes. In fact the data would not have to be sorted, there would just have to be a strong correlation between index blocks and table blocks. Oracle has access to that measurement through the clustering factor in dba_indexes or dba_ind_partitions.