Doubt in Index Working

[ankit_jkt]

Hello,

Please tell me how progress uses the index and how index help to retrieve data from database?

As per my concern progress handles an index file seperatly with index field entries and an additionl field i.e "RowId" of the row.

Please clerify with this example:-

find first customer
where customer.name = 'XYZ':U
and customer.salesRep = 'ZKM':U
no-lock.

As find statement uses only one index and in this case if "Name" index used what flow will be processed in this case to fetch record from databse?

Thanks

 

[RealHeavyDude]

Your concern is how it is supposed to work. That's the concept of an index.

The compiler build an index bracket which will used to locate the matching records within the index. The ROWIDs will then be used to actually fetch the records. A ROWID represents the physical location of a record in the database.

Why does that concern you?

Heavy Regards, RealHeavyDude.

 

[ankit_jkt]

Thanks for your reply..

Can you tell me what type of contents this index bracket has?..is it index field values or ROWID of the record that satisfies the where clause of query?

 

[RealHeavyDude]

Based on your query criteria the compile will pick the most appropriate index following it's set of rules for the index selection. Depending on your query criteria it will even pick multiple indexes.

An index bracket will enclose the values satisfying the query criteria for the given index component ( = database field ). If the compiler can't find an appropriate index then one of the last rules will fire which will most likely be the primary index. But as it can't build an index bracket it will be a table scan running through the whole table on disk.

Heavy Regards, RealHeavyDude.

 

[ankit_jkt]

Can you clarify the index concept with the given example:-

Assume that databse table has the following entries:-

Name SalesRep
----- ---------
A L
B N
B J
C O

If table has two non unique index i.e., name and sales-rep, then one bracket will be on each index.

As per my concern index entries should be like this(first sort on the field value and then rowid(ascending order)):-

Name index SalesRep Index
----------- ---------------
Name RowId SalesRep RowId
A 7 J 8
B 5 L 7
B 8 N 5
C 4 O 4

For first customer
where customer.name = 'B':U
and customer.salesRep = 'N':U
no-lock:
/* display statement*/
end.

According to me two index bracket will be use. Am i right?

Now Please clarify the foolowing thing:-
1. What entries will be in bracket either field entry or rowid of record.
2. After Bracket how record will fetch from the database table.
3. How both bracket will communicate with each other to fetch the record?

Thanks in advance...

Regards
Ankit Rajpoot

 

[TomBascom]

You might find this helpful: Pick an Index, Any Index... it is Mike Lonski's famous presentation on the 4GL index selection process. Recently updated and presented at PUG Challenge Americas.

 

[ankit_jkt]

Doubt in Index Bracketing

Hi All,
Please tell me how index bracketing use in case of query uses multiple index to fetch the perticular record from the database.

If a query using two index for fetching the first record (i.e., For First table-name where "condition"), then how RowId will be selected from both of index bracket to fetch the record.

Regards
Ankit Kumar

 

[RealHeavyDude]

When a query is resolved via multiple indexes then the subsets are merged and from that on the ROWID is used.

What strikes me: Why do you bother so much how the ROWID is used by the database engine internally to fetch record? Why focus on the ROWID so much?

Heavy Regards, RealHeavyDude.

 

[jdpjamesp]

I have to agree RHD. Initially it's enough to know indexes work. Then it's good to know the basics of how indexes work such as brackets etc so you can create efficient indexing strategies and efficient code. But beyond that there seems to be little point?

 

[ankit_jkt]

Thanks for your reply.
I am focusing on ROWID because I want to know that in what cases progress may return the different result to the FOR FIRST and FIND FIRST statement.

 

[jdpjamesp]

Why would you want to find the first record if there are going to be many? Even if you work out this scenario, what's to stop it changing in the future? You'd be stuck then with a bug that would be nigh on impossible to track down. How about if someone inadvertantly adds an index that changes which record is found?

 

[RealHeavyDude]

FOR FIRST and FIND FIRST are bad practice, especially FIND FIRST!

Why would you use FIRST on a unique FIND? Using FIRST implies that you use a technique to retrieve exactly one unique record where there are probably more than one records exist. Why would you do that?

Heavy Regards, RealHeavyDude.

 

[TomBascom]

FOR FIRST doesn't work the way anyone thinks it works. Much like RELEASE, even when it seems to work it probably isn't doing what you think it is doing. I strongly suggest that nobody should ever use it.

FIND FIRST is, as RHD says, bad practice. It is an especially bad practice when FIRST is automatically glued to every FIND.

You really ought to carefully read that presentation that I posted yesterday. It will explain many things about index bracketing to you.

 

[TomBascom]

If the ordering of a set of records matters to you then you MUST use a BY phrase. Which eliminates FIND statements (they do not support BY) and FOR FIRST (which ignores BY).