Board index » delphi » sql query from secondary index (InterBase.6 / FireBird.1.5)

2005-11-22 08:10:29 PM
delphi172
Hello Group,
please excuse my ignorance,
having created a secondary index on a table
how can I read it (in Paradox I treated it as a table)
in run time (not thru Object Inspector)
... select * from table1 ...index??
Many thanks in advance & regards,
Gianni Filippini
 

Hi,
Regards,
Arno Brinkman
ABVisie
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
www.databasedevelopmentforum.com
Firebird open source database (based on IB-OE) with many SQL-99 features:
www.firebirdsql.org
www.firebirdsql.info
Support list for Interbase and Firebird users:
XXXX@XXXXX.COM
Nederlandse firebird nieuwsgroep:
 

By "read it" do you mean read the data in index order? If so, use an
ORDER BY clause in your SELECT statement.
--
Bill Todd (TeamB)
 

Bill, many thanks for your reply,
hence, a secondary index works only behind the scenes and
it is never used directly (except in Object Inspector, for Tables), am I
right ?
Best regards,
Gianni Filippini
 

When and if any index is used is determined by the optimizer. I am not
sure what you mean by "in Object Inspector, for Tables". If you are
referring to the IndexName property of TTable, do not every use a table
component with a SQL database server unless you want poor performance
with any by the smallest tables.
--
Bill Todd (TeamB)
 

Gianni Filippini writes:
Select * from Cities
Plan ( Cities ORDER Cities_1)
ORDER BY CITY
Optimization of query using an index for ordering ...
Adalberto Baldini
 

Hi,
Unless you are certain about what and why you are doing it.
Regards,
Arno Brinkman
ABVisie
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
www.databasedevelopmentforum.com
Firebird open source database (based on IB-OE) with many SQL-99 features:
www.firebirdsql.org
www.firebirdsql.info
Support list for Interbase and Firebird users:
XXXX@XXXXX.COM
Nederlandse firebird nieuwsgroep:
 

Arno Brinkman writes:
I have not read of any problem about it.
Adalberto
 

adalberto baldini writes:
Using Indexes causes out of order page fetching. This extra disk I/O is usually
much more expensive than reading whole pages in sequentially and sorting.
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Your friends will know you better in the first minute you meet than your
acquaintances will know you in a thousand years. (R Bach)
 

In addition to the specific issues with using an index for sorting, the
optimizer usually has a good reason for not using an index. Unless you
know exactly what you are doing writing plan statements is not a good
idea.
--
Bill Todd (TeamB)
 

adalberto baldini writes:
have a problem with an imbeded query plan.
hth
--
Rob Schieck
TeamB
 

Thanks a lot to Bill Todd
and to everyone for replying
best regards,
Gianni Filippini