I have an ACX File for an MS-SQL table:
SEGNAME=BOOKS, CARDINALITY=564,
TABLENAME=dbo.Books,
CONNECTION=AudioBooks,
KEYS=1, $
FIELD=BOOKID,
AUTOINCREMENT=YES,
START=1000, INCREMENT=1, $
The only definition I have found indicates it is to indicate 1:1, 1:M and M:M relationships. What is a 564 ?

This message has been edited. Last edited by: <Kathryn Henning>,

Number of rows in the table.

I suppose the syntax is there to carry hints that may help the SQL-generator optimize the generated SQL (although I would think the RDBMS is much better at that game).

564 is the number of rows in the table, at the time of the .acx creation.

If WebFOCUS needs to make a decsion, based on the size of the tables, used in a JOIN, this number (CARDINALITY=) will be referenced.

(As a CUBS fan, I've always had an issue with CARDINALITY.)

Jack and David,

Thank you.

David, I'm not a baseball fan, but I still got a great laugh from your CUBS vs CARDINALS reference.

Hi John,

If memory serves me correctly, I believe it is the number of rows in the table. Depending on the adapter, the optimizer can use that value to make decisions on how to join tables. I seem to remember it should only be used for pretty static tables as you wouldn't want that value in your ACX if the actual value varies -- that could cause some inaccurate decisions by the optimizer.

I've never seen this CARDINALITY attribute, neither have I seen the AUTOINCREMENT, START and INCREMENT attributes - what type of dbms is this acx for?

Cheers,

Francis - For us, CARDINALITY is an optional attribute, within our WF adapter for Oracle.

This message has been edited. Last edited by: David Briars,

quote:

Originally posted by Emily Duensing:
If memory serves me correctly, I believe it is the number of rows in the table. Depending on the adapter, the optimizer can use that value to make decisions on how to join tables.

I imagine it also gets used in cross-database joins to determine which table to fetch entirely and which to select only specific values from (based on the result set from the smaller table).
For example, if you have a table A and a table B with cardinalities of a billion rows and just 10 respectively, then it's much more efficient to select the 10 rows from table B and apply an IN() clause to table A with those values than it would be to fetch the entirety of table A.

I don't know if WebFOCUS is smart enough to do that. I hope it is.

Francis,
This is a MS-SQL datsabase table. Autoincrement is the result of the MS-SQL IDENTITY function - which automatically increments the key field when adding new records.