Oninit cdi / Archive
Talk to an expert →

a sunquery sql increase logical log...why?

Posted in 2012

Topics: Logging & Checkpoints, Platform-Specific Issues

MARS CHEN — — source: IIUG Forums & Mailing Lists

Greeting !! I've a IDS 11.5 running in CentOS Linux , The following sql will increase logical log usage ... select b.codex,a.currx ,b.optionx,b.bidx,b.askx from idximply a , riskmng b where a.codex = b.codex and b.codex in (select codex from tradingissuecode) ; If I use select b.codex,a.currx ,b.optionx,b.bidx,b.askx from idximply a , riskmng b where a.codex = b.codex and b.codex in ("XXX1","XXX2","XXX3","XXX4") ; then logical log won't increase ...I'd like to know why ? BTW , idximply and riskmng are raw tables , tradingissuecode is not !!

↪ replying to MARS CHEN
Art Kagel — — source: IIUG Forums & Mailing Lists

Do you have temp dbspaces listed in the DBSPACETEMP environment variable? If not, then temp tables will be created in the rootdb dbspace which is a logged dbspace so they will cause writes to the logical logs. The first query may be producing a temp table. What does the SET EXPLAIN output say? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 21, 2012 at 3:44 AM, MARS CHEN <hedgezzz@yahoo.com.tw> wrote: > Greeting !! > > I've a IDS 11.5 running in CentOS Linux , > The following sql will increase logical log usage ... > > select b.codex,a.currx ,b.optionx,b.bidx,b.askx > from idximply a , riskmng b > where a.codex = b.codex > and b.codex in > (select codex from tradingissuecode) ;> > If I use > > select b.codex,a.currx ,b.optionx,b.bidx,b.askx > from idximply a , riskmng b > where a.codex = b.codex > and b.codex in > ("XXX1","XXX2","XXX3","XXX4") ;> > then logical log won't increase ...I'd like to know why ? > > BTW , idximply and riskmng are raw tables , tradingissuecode is not !! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b33d5cc09b1dc04bbbe3700

↪ replying to Art Kagel
MARS CHEN — — source: IIUG Forums & Mailing Lists

Thanks !!! No...I don't have DBSPACETEMP ...I don't have temp dbspace , thanks for your information !! The following is the set explain on .... Estimated Cost: 35 Estimated # of Rows Returned: 10 1) informix.b: SEQUENTIAL SCAN Filters: informix.b.codex = ANY <subquery> 2) informix.a: INDEX PATH (1) Index Name: informix.idximply_1 Index Keys: codex (Serial, fragments: ALL) Lower Index Filter: informix.a.codex = informix.b.codex NESTED LOOP JOIN Subquery: --------- Estimated Cost: 2 Estimated # of Rows Returned: 8 1) informix.tradingissuecode: SEQUENTIAL SCAN Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 b t2 a type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 10 0 00:00.00 12 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 0 171 0 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 10 00:00.00 36

Glossary