search records by index

[cruel]

I have a very large table and it has quite a few indices. I need to locate a record that comes right after another record with certain event in terms of time. The nature order of the records in the table is not in time order. So normally one would use order by statement to have it sorted first. However, since the table is huge and there is a time limit for the data processing, I am wondering what I could do to speed up the process. The field that contains the time data is indexed. Can I use this index info to locate the record without sorting the the data? Thanks

 

[sem]

Actually if index is in use, then you need no extra-sorting.

Regards, Dima

http://seminihin.narod.ru

 

[cruel]

Thanks. But I am not sure if index could replace sorting. It is evident that if I have multiple indices in a table, the data can not be ordered in all the sequences defined by those indices.

Suppose the data look like this as a result of query:
field: A B C D other fields
x 1 8 0
x 2 7 1
y 3 9 0

All A, B, C are indexed. Since field D indicating an event at second record, I need data from field C that has a value right after the second record's value (7), os it is 8. How could I get '8' without sorting by field C?

 

[Thargy]

cruel,

you appear to be missing the point of indexes.

Indexes do not replace sorting, but they do make accessing data faster. Whether or not a query sorts the data is irrelevant to the index. If you want data and it's indexed appropriately, it will be used.

http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96521/indexes.htm#4953

is a handy link to chapter and verse on this subject. I would draw your attention to the statement in section 16 "Guidelines for Managing Indexes" which categorically states

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

and

Indexes are logically and physically independent of the data in the associated table.

I heartily recommend you take some time to look through this part of the documentation, particularly the section about multiple column indexes and speeding retrieval.

Regards

Tharg

Grinding away at things Oracular

 

[cruel]

Thanks.

I think my point is being missed. I understand well from the beginning when this idea came to me before new year's eve that, trying to exploit index that way, it is a bit wacky. But sorting becomes prohibitive, server-wise, OS-wise and time resources-wise. Indeed, index is not the focal point. Order is. I want the value of C that is JUST greater than 7 at column C, which is identified by 1 at columne D. Now, using:

min(C) ... where C > (select C from ... where D = 1)

won't do it because I have many cases of 1 at column D, along with conbinations of many other columns.

Is there a way I can cheat it out without sorting or any other expensive processing? Index or not? Index comes to me for an intuitive reason, not for technical grounds. I am sure we all use unorthodox ideas here or there in programming to make things work.

 

[cruel]

couple of additional points:

1) I meant multiple indexes, not multiple column index. So, A, B, C, D are all indexes of their own.

2) I am not a DBA. So I am not in a place to modify or create new index. I am sure also that the table serves for many other more important purposes.

Again, thanks