Re: Two interesting features of Sybase that Informix may want

Mike Segel wrote: > to consider... > > Sybase allows the programmer to *suggest* an index in a query. > This is handy because of some quirky behavior in their optimizer. > > Whats nice, is that you can ask for a specific index to be used > so that you don't have to do a table scan due to *other* issues. available since 7.3: SELECT {+ USE_INDEX(table,idxf2) } * FROM table WHERE indexfield1 = 3 AND indexfield2 = 9; These directives are also available, if you use wrong syntax the directive is ignored: { +AVOID_INDEX( tabellenname, indexname ) } ignore the index { +USE_INDEX( tabellenname, indexname ) } use this index { +USE_HASH( tabellenname ) } do a hash join { +AVOID_HASH( tabellenname ) } don't use hash join { +AVOID_NL( tabellenname ) } don' t use nested loop join { +USE_NL( tabellenname ) } use a nested loop join { +FULL( tabellenname ) } do a full table scan and there are more, these are only a selected few. > > The other nice issue is that you can specify a "noholdlock" keyword > within the query. This would set the query to act at an isolation level > of 0, > which in informix lingo, allows for dirty reads. It really comes in > handy. set isolation to committed read;begin work; blabla select update ... set isolation to dirty read; select * from table; set isolation to committed read; select more from table for update;..... commit work; > > > The only drawback is that if you are using Rogue Wave C++, you may have > some > bizarre side effects unless you are writing dynamic SQL. > > -Mikey Greetings Andreas Zeugswetter