Would anyone be able to tell me what an aggregated index is? I am new to progress and have been told that I can create several indexes on each of two joining tables that use single fields and that progress will be able to distinguish which are the most efficient ones to use. Have I dreamt this? It seems almost too simple.
Aggregated indexes
- Thread starter BTom
- Start date
Lee Curzon
Member
I have no idea what an aggregated index is, but I don't think it's got anything to do with Progress.
No, you haven't dreamt anything. Progress picks the best index, but whenever you find your query is slow, you can check your program with xref to see which index Progress is using.
For general index and querying rules, see
http://www.peg.com/techpapers/monographs/query/query.html
Lee
No, you haven't dreamt anything. Progress picks the best index, but whenever you find your query is slow, you can check your program with xref to see which index Progress is using.
For general index and querying rules, see
http://www.peg.com/techpapers/monographs/query/query.html
Lee
drunkahol
New Member
By Aggregated indexes, do you mean the use of multiple indexes?
If so, Progress can select more than one index to use on a search if there are indexes that usefully match the search criterea.
Can't remember where the rules for these are unfortunately, but the use of multiple indexes IS possible on Progress queries.
Duncan
If so, Progress can select more than one index to use on a search if there are indexes that usefully match the search criterea.
Can't remember where the rules for these are unfortunately, but the use of multiple indexes IS possible on Progress queries.
Duncan
almost every database engine can use
more then one index to search a table
progress supports this feature since v7
roughly a decade
the process basically scans all the indexes
which are much smaller and faster then reading records
merges the rowid lists
and retreives the records
as for finding the the best indexes
the progress 4gl engine uses a rules-based optimizer
this particular optimizer, geek term
is a syntactical optimizer
from syntax, because the join order i.e. the syntax
stays the same only the indexes are changed
the progress sql engine like any other standard sql engine
supports a cost-based optimizer
only thru odbc/jdbc, not from the 4gl
cost-based optimizer estimate the best access plan
based on various collected statistics
a cost-based optimizer is much more precise
bottom line it's faster
but it also requires maintenance and tunning, generally a dba
which might not be suitable for embedded databases
the obvious problem with rules-based optimizers
is that they're very general
and alot of the time it's pure luck
if they do get it right
all and all, a syntactical optimizer is consideral a solid optimizer
but it's usually suitable for specific searches
and would be a problem with very dynamic criteria
some of the ways around it would be
to use some sort of prefixed ranking of access plans
i.e. if order is entered search by order
else if item is entered search by item
else if group is entered search by group
else don't bother with anything worse then that
just run on the entire query in the most effecient way
i'd also guess that in following releases the 4gl engine
would support the option of using a cost based optimizer
theres no real reason why they shoudn't
a query statement is very similar to a select statement
and they already wrote a working product
some of it could even be used as-is like the stats mechanisms
maybe, after the guys in the engine crew
would free up from all the sql stuff ? please gus !!!
if you're interested
there are tons of material about query optimizers on the web
more then one index to search a table
progress supports this feature since v7
roughly a decade
the process basically scans all the indexes
which are much smaller and faster then reading records
merges the rowid lists
and retreives the records
as for finding the the best indexes
the progress 4gl engine uses a rules-based optimizer
this particular optimizer, geek term
is a syntactical optimizer
from syntax, because the join order i.e. the syntax
stays the same only the indexes are changed
the progress sql engine like any other standard sql engine
supports a cost-based optimizer
only thru odbc/jdbc, not from the 4gl
cost-based optimizer estimate the best access plan
based on various collected statistics
a cost-based optimizer is much more precise
bottom line it's faster
but it also requires maintenance and tunning, generally a dba
which might not be suitable for embedded databases
the obvious problem with rules-based optimizers
is that they're very general
and alot of the time it's pure luck
if they do get it right
all and all, a syntactical optimizer is consideral a solid optimizer
but it's usually suitable for specific searches
and would be a problem with very dynamic criteria
some of the ways around it would be
to use some sort of prefixed ranking of access plans
i.e. if order is entered search by order
else if item is entered search by item
else if group is entered search by group
else don't bother with anything worse then that
just run on the entire query in the most effecient way
i'd also guess that in following releases the 4gl engine
would support the option of using a cost based optimizer
theres no real reason why they shoudn't
a query statement is very similar to a select statement
and they already wrote a working product
some of it could even be used as-is like the stats mechanisms
maybe, after the guys in the engine crew
would free up from all the sql stuff ? please gus !!!
if you're interested
there are tons of material about query optimizers on the web