Re: Newbie: LOGICAL Logs

In article <31BCD043.7A5C@PacBell.COM>, "Dave K. Lahoti" <D4kLaho@PacBell.COM> writes >Jeff Cerrafon wrote: >> >> We are running INFORMIX 5.04 ONLINE on HPUX 9.04(HP9000). >> >> Do select statements with several table joins or ORDER BY clauses CREATE >logical >> log entries? If they do, is there a quick workaround to avoid LONG >TRANSACTION? >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > >Per the SQL Reference manual, it appears that if the following condition >is true, you need not concern: > >(Current open database has status of no logging) > >or > >((Current open database has logging status) > and > (Explicitly created temp tables are created using "with no log" > keywords) >) Correct. > > >Couple things to note: > >1. I'm not certain if the engine will choose to create implicit temp > tables when doing a complex or multi-table select. If so, you won't >have use of the "with no log" keyword feature. > It will create implict temporary tables if required. > It may be the case that only temporary indexes are created in which > case then there would be no logging problem. This answers your > first question. > Temporary indicies may also be logged - I am not sure. However if they are being created than an index is missing somewhere or your query needs rewriting. >2. If you can afford to break up your query, you may be able to > explitly force no logging if you use the "with no log" keyword > feature. This answers your second question. > Correct break up the query into several sections and use temporary tables created with no logging to avoid logs filling up. As I said I am not sure if implict tables created within queries are logged - could someone from Informix confirm/deny this please? >Can anyone else please confirm/deny this points? > -- David Williams