Index in Progress

Hi Everybody,

Without an Index can we make a search faster, i read in a post that 20% of oracle queries does not use index... but they r faster...

Can anyone state a clear reason for that??? :confused:
 

TomBascom

Curmudgeon
In the 4GL every record access (except those using ROWID or RECID) requires at least 2 reads -- one for the index block and one for the data block. If you are going to read a significant portion of a table then the index block reads can be eliminated to your benefit in many cases (especially if ordering of the records is not important). Progress' SQL-92 query engine does this. The 4GL query engine does not.

There have been many requests made for many years of Progress product management to permit the 4GL engine to take advantage of this feature. The routine response has been "That's the first time I ever heard that request!" or "There's nobody else who wants this, why do you need it?". Talk to your Progress sales people and make it clear to them that this is a feature that you want and need and make sure that they relay your concern to product management and development.
 
Thanks a lot...

So based upon the record fetch we should decide whether index is required or not... If we are going to fetch more number of records from a table it doesnt require index...

Am i right???
 

TomBascom

Curmudgeon
No.

Progress 4GL will always use an index. Unless you specify exactly one record that you already know the RECID of.

You do not have the option of not using an index when you program 4GL.

There is a USE-INDEX keyword that allows you to over-ride the Progress compiler's choice of index but not using USE-INDEX does not result in no index being used. If you compile your code with the XREF option you can see what indexes where chosen.

Note: I do not recommend the use of USE-INDEX unless you have very detailed knowledge of the data access that your query will result in at runtime. This usually means that you have tested your beliefs about that access against a copy of production data. IMHO the compiler almost always make a better decisions about index usage than most programmers.

On the other hand if you would like your customers to pay me lots of money for consulting services feel free to add USE-INDEX liberally in your code. :awink:
 
Oh, fine...
Thnks a lot, now i could understand the concepts to an extent. I wil read more on this and understand all concepts, thanks a lot for sharing the valuable information with me.....
 
Top