read index only

jongpau

Member
Hi,

Do you mean you want to know which fields are part of the primary index on a table?

If so, and depending on what Progress version you are using you can do this in one of the following ways:

1. Access the hidden tables of your database:
Code:
FIND _file WHERE 
	 _file-name EQ "customer":U 
	 NO-LOCK NO-ERROR.
IF AVAILABLE _file
THEN FIND _index WHERE 
		  RECID(_index) EQ _file._prime-index 
		  NO-LOCK NO-ERROR.
IF AVAILABLE _index
THEN FOR EACH _index-field OF _index NO-LOCK,
		 FIRST _field OF _index-field NO-LOCK:
  DISPLAY _field._field-name.
END. /* if available _index */
Note that if you use more than one connected database, you will have to either create an alias for the db you want to search in and qualify the "_" tables with the alias (preferred), or qualify the "_" tables with the logical database name.
2. Use a dynamic buffer (v9.x only):
Code:
DEF VAR lhBuffer AS HANDLE NO-UNDO.
DEF VAR lvCount  AS INT	NO-UNDO INIT 1.
DEF VAR lvField  AS INT	NO-UNDO.
DEF VAR lvIndex  AS CHAR   NO-UNDO.
CREATE BUFFER lhBuffer FOR TABLE "customer":U NO-ERROR.
IF VALID-HANDLE(lhBuffer)
THEN DO:
  GetIndices:
  REPEAT:
	lvIndex = lhBuffer:INDEX-INFORMATION(lvCount) NO-ERROR.
	IF lvIndex EQ ? 
	THEN LEAVE GetIndices.
	
	IF ENTRY(3,lvIndex) EQ "1":U 
	THEN DO:
	  MESSAGE "Index:":L ENTRY(1,lvIndex) "Unique:":L ENTRY(2,lvIndex) "Word:":L ENTRY(4,lvIndex).
	  
	  DO lvField = 5 TO NUM-ENTRIES(lvIndex) BY 2:
		MESSAGE "Index Field:":L ENTRY(lvField,lvIndex) "Descending:":L ENTRY(lvField + 1,lvIndex).
	  END.
	  LEAVE GetIndices.
	END.
	lvCount = lvCount + 1.
  END.
  DELETE OBJECT lhBuffer.
END.
HTH
 

Balwinder

New Member
I know which fields make up the index. There is a way to go through the file to know the number of index entries in a table without reading each record. For example, if a table has 10 records, you can count the primary index entries on that table and it would return 10. I want to know how to get the count for the index entries.

Thanks
 
SELECT COUNT(*) FROM <TableName> will do that.

This will still take a significant amouont of time on a medium to large table.

I have used a technique which uses an approximate value stored in a table but that is not wildly accurate and you seem to want accuracy.
 

jongpau

Member
Hello again,

Don't think Progress gives us that information anywhere (if you do find out if it is possible, let me know, because I would be very interested).

As said, counting the records would be the only thing you can do.
Unless (just an idea) you are willing to add a database table that holds the record counts (Two fields; TableName and RecordCount and an Index on TableName should do the trick). Add some database triggers that update the count table on write and delete (for those tables that you need to count regularly) and you should be set (after doing an initial count and filling the table with the current counts of course).
 
SELECT COUNT actually only counts the number of index entries (liitle know fact about Progress #143!)

There has long been an "enhancement request" in the works to have the numbers of records in each table stored in a "System Table" but this doesn't seem to be attracting much interest at PSC

And Jongpau - the sun is shining!
 
You could use the FIELDS option on your FOR EACH to limit the amount of data fetched. This can greatly improve performance when using a network db connection.
 

vinod_home

Member
I think you can get the count info from proutil faster. Its just that it puts in a text file and you would have to parse it.

HTH

Simon Sweetman said:
You could use the FIELDS option on your FOR EACH to limit the amount of data fetched. This can greatly improve performance when using a network db connection.
 
This debate is fast becoming religious!

Yes - Proutil is faster for a specified number of records in one table but proutil does the whole database! This can make it a little slower if you are doing a small table in a huge database!

This actually forms the bases of the technique I was discussing which used an script to run a proutil and update a table with the latest figures. It was also very useful for estimating growth since the figures could be stored on a daily basis and included record sizes etc!

select count *should* be faster than fields, because select count literally counts the index entries. for each will still retrieve the records.

I still wish PSC would add some of this stuff to the database engine and save us these debates!
 
Top