Oninit® Archive
Results for “IFX_FOLDVIEW”
Not enough room in ROOT DBspace
Reduce the size of your PHYSFILE. You probably moved it to another dbspace after setting up the first server. Start it smaller, then create the plog dbspace and move it there and resize. j. On 1/4/17 4:09 PM, STEVEN AB wrote: > Hi, > > I'm trying to setup a new server with the …
Re: sql server -> informix
"Obnoxio The Clown" <obnoxio@hotmail.com> wrote > No. > > > Well the answer is it is possible using views. But even that would mean a > > code change. > > Why? Rename the table and create a view with the original table name... And then what would happen to all SELECTS. They will…
varchar index on varchar(255) column
Pete: You could alter the table for that column from VARCHAR(255) to LVARCHAR(255) which would be indexable. If you do that and don't want to have to modify your code to cast the column back to a VARCHAR everywhere it is referenced, you could rename the altered table and then cre…
Help with Delete Trigger
The best solution may be to rename the table and replace it with a view that has an INSTEAD OF trigger to catch the delete and mark it deleted. This has the advantage the the view can prevent users from seeing deleted rows by including a filter on the delete flag in the view defi…
How to kill a session using SQL
Thank you. This is great. Question. Want to query the client port no. for a database session. Are you aware of what sysmaster table/view this would be in?…
302: No GRANT option or illegal option
I tested it in both 11.70.FC7W1 (didn't have W2 to hand) and 12.10.FC8W2 and both behave in the same way. The problem is the cast in the view: create view v_mytab (rec_key, message) as select rec_key, message::char(1500) from mytab; It will work if this becomes: create view …
Performance loss for a query after 11.50 >12.10 up
Thanks Cesar! unfortunately no functional index to remote queries involved here only 15 LEFT joins written the 'Oracle way' (just to help...) If I were the Informix optimizer, I would be lost in such a query *lol* Trying to find out where the engine is spending time...…
new features about dynamic parameter added in 12.1
Attention, this is not documented. For Informix 12.10, another way you can get the list is to use the cf_flags column on syscfgtab to determine which onconfig parameters are dynamic: SELECT cf_name FROM sysmaster:syscfgtab WHERE bitand(cf_flags,'0x1') = 0 --removes Unsupported p…
Performance issue after migrate to IDS from Online
Questions: 1. After the database was recreated and loaded did you run the recommended suite of Update Statistics commands as described in the Performance Guide manual (or as implemented by my dostats utility) or are you depending on Auto Update Statistics (AUS)? 2. So, you m…
Graphic tool
Hi, Do you know a tool to view the database in a graphic mode ? Thanks in advance. Enviado desde mi iPhone…
Dynamically ReCreate Views
Hi Dave, As you have already found the names of the dependent views from sysdepend, you should just be able to use "dbschema -t <viewname> -p all" to get the view text and the permissions. You'll also need to make the process recursive to handle views built on views. Mike ----…
View with left join.
Ok but before this, the view was as : select * from bkhis where ncp < "0005000"; whitch returns about 70GB and no pb. I guess the cause isn't the volume of result size but joins where IDS tries to store temporary data (in the case of executing the view and not when executing dire…
Root db space fills up
Not sure if it will affect you and whether the behaviour of using rootdbs as temporarily space is affected... But maybe you can try it out. See below. We had used IFX_FOLDVIEW when having views with union and it did improve performance. "IFX_FOLDVIEW configuration parameter Use …
users with dbsecadm privilege
Hi, Is there a way to list users with DBSECADM privilege ? Something like selecting from sysusers to view database level privileges. Thanks…
Poorly Optimized SQL
So here's an interesting little nugget that I just realized: The Explain Plan doesn't always print out the "Query Statistics" portion. The plan of tables and joins is printed, but not the stats breakdown. This section prints a long time after the query is started, so if I kill th…
IDS 12.10 - dbimport very slow
Hi Markus, Is your import logged ? Regards, Henri MOUKOURI Le 25 juin 2013 à 10:45, "MARKUS BRINKBäUMER" <markus.brinkbaeumer@gmail.com> a écrit : > Hello, > > we have a new server (Windows 2012, 8 GB RAM, 4 x 600 GB SAS 15k HDD). On the > server we have installed an IBM Inf…
Performance Tuning Discussion
Ok it seems you have a big hardware, what´s your Informix version? What´s your box architecture? For performance tips, there is a good article in Andrew Ford´s blog, that should clarify your thoughts.... http://www.informix-dba.com/p/informix-innovator-c-tuning-basics.html This…
Physical log size
Art, Thanks for the info. We do have enough buffer cache to effectively buffer most of what will be active at any point, so it really does come down to monitoring the various checkpoint-related stats. It was just really confusing that the message indicated that it would be fas…
Re: trigger instead of view
Cesar: This is a case of the old adage "when all you have is a hammer, everything starts to look like a nail." SPL is the wrong tool to solve the problem of being too lazy to hand code alters to multiple table schema as well as an INSTEAD OF trigger for a view over those tables…
trigger instead of view
Hi, ifx 11.70 fc6it , Linux Need a little help to solve a situation what I don't found a decent workaround. I'm was executing few tests over the situation bellow , trying "simulate" a fragmentation/partition using view + trigger... Just for fun and as possible solution for a d…
How to query Invalid Views
I want to know name of all views in my database which are actually invalid views ( base table's column is dropped ) and are going to throw -217 error when application try to access those. Any SQL to query system table to list such views..? Thanks, Pradeep…
Config Help
Your biggest performance problem is that you are running your server on a VM. IO performance under VMWare is VERY VERY POOR and, as I've said several times already this month, databases live and die on IO performance. Art Art S. Kagel Advanced DataTools (www.advancedatatools.co…
What are best onconfig parameters for a DSS app
Frank.... first question: Is your database installation being made for a production system???? Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional …
Index On A View Created With A Union
Because of the UNION when you qpply a filter to the view the optimizer is creating a temp table with the entire contents of the view in it and filtering the rows using a sequential scan. That is why it is slower. There is an ONCONFIG parameter IFX_FOLDVIEW that when set will tell…
Updating remote database from a trigger
May create views for each region in the remote db, and create a synonym (same name) for a view in other db, then should be able to do insert via the synonym in your function. Regards, Gary…
sysindexes table missing
Aren't you looking in the wrong place? sysindexes is a view to sysindicies which is a system catalog table. Not a sysmaster table. dan…
Re: IFX_FOLDVIEW
I have not noticed anything negative. But it has done wonders with speeding up our views here. Nick…
IFX_FOLDVIEW
anyone experience any negative effects turning on this config parameter? thanks tom You can significantly improve the performance of a query that involves a view by enabling view folding with the IFX_FOLDVIEW configuration parameter. When the IFX_FOLDVIEW configuration paramete…
why DBA can not GRANT a view?
Just for curious... Why Informix have the limitation what the DBA or Informix user cannot grant a select on a VIEW with AS "grantor" where he isn't the owner? Give the error 302: No GRANT option or illegal option on multi-table view.…
question about Query with joined view
Hi, IDS 10.0.fc5 on AIX 5.3. Trying to tune a slow query. I think I got it boiled down to this: The select is joining with a view, and that view is joining with another view in it's create statement. The inner view seems to cause a table scan. I created an index, dropped and re…
Performance Issue w/View
I was researching IFX_FOLDVIEW when I received this tip. I will definately try it ASAP.…
Physical Log Size
How did you "increase" the size? You can only do that song onparams. You cannot do that just by changing the Onconfig file Art On Jan 25, 2011 12:10 PM, "KENT GRUNDEN" <k.grunden@hoogwegtus.com> wrote: > IDS 11.50.FC4WE Windows Server 2008 64bit > > I keep getting the message wh…
Re: IFX_FOLDVIEW , vantages? disvantages?
On Fri, Oct 8, 2010 at 12:07 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi, > > By default the IFX_FOLDVIEW is disabled . > > I read in some places of the documentation about the vantages to active > this option. But no disvantages are mentioned... or…
Re: IFX_FOLDVIEW , vantages? disvantages?
On Fri, Oct 8, 2010 at 12:07 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi, > > By default the IFX_FOLDVIEW is disabled . > > I read in some places of the documentation about the vantages to active > this option. But no disvantages are mentioned... or…
IFX_FOLDVIEW , vantages? disvantages?
Hi, By default the IFX_FOLDVIEW is disabled . I read in some places of the documentation about the vantages to active this option. But no disvantages are mentioned... or risks of degrade the performance when enable it, or others consequences (problem with triggers? permission?)…
IFX_FOLDVIEW , vantages? disvantages?
Hi, By default the IFX_FOLDVIEW is disabled . I read in some places of the documentation about the vantages to active this option. But no disvantages are mentioned... or risks of degrade the performance when enable it, or others consequences (problem with triggers? permission?)…
limit on query length in Informix IDS 11.50
Is there a limit on query length in Informix IDS 11.50? If there is one, can it be increased? Also can anyone suggest some information on how to optimize IDS 11.50 for longer queries to make them run faster? We have some Cognos reports that send very long queries to IDS 11.50 …
Query works fine in IDS 10 but crashes 11.5 engine
Jonathon wrote: I'd post what the optimizer has to say, but sadly it never gets that far. It prints out the last query right before this one (which completes successfully) and then the oninit process hangs and the optimizer information for the query in question never gets printed…
Re: Query-Optimizer on HDR-Secondary
Hi folks, I talked to my informix-supporter: Rebuilding the hdr-mirror solved the problem. Regards Markus Bschorer "Markus Bschorer" <mb@worxbox.com> schrieb im Newsbeitrag news:4bd44874$0$6892$9b4e6d93@newsspool2.arcor-online.net... > Hi Art, > > thanks a lot for your answers…
Query-Optimizer on HDR-Secondary
IDS 11.5 FC4 Linux 64 Bit Hi everybody, it seems, that the IDS-Optimizer works different on primary and (hdr-)secondary servers, having identical IDS-Versions. Example: create table wbx_permission ( per_object_id char(20), per_client_id char(10), per_user_type i…
ids query optimizer.
As I understand it, most query optimizers are cost-based. Some can be influenced by hints like FIRST_ROWS(). Others are tailored for OLAP. Is it possible to know more detailed logic about how Informix IDS and SE's optimizers decide what's the best route for processing a query, ot…
Re: materialized view
No. Materialized views have two main advantages: - They can be updated automatically - The optimizer can take advantage of them to give quicker answers You don't need the second, so you could think about implementing the first one with triggers. Regards. On Sat, Mar 27, 2010 …
materialized view
Is there the equivalent of Oracle's materialized view available in Informix 11.5 ? We are migrating data that will require an initial bulk load from Informix to Oracle, and then delta loads. We were going to use HP Loader to unload files and then have an ETL process ( ODI ) to cl…
views and update statistics
hi all how can one see the last time statistics were update for a view? or does it just relate back to when the tables within the view were last updated? thanks much -…
Random Server Slowdown "Redone"
There are some usual things you could do for now: 1) calc your database ratios, I think there are several NETTYPE, BUFFERS and even BTSCANNER options that could be optimized, you could download Mr. Art script to automatically know these important numbers!!! It´s on the download …
Re: Union view limits
Art Kagel wrote: > The only limit that I am aware of is the limits on the length of a > single SQL statement which is 65535 characters, so if each table and > column name is two characters long that works out to about 3449 SELECTS > in an maximum length UNION less the characters …
Union view limits
Guys, Is anyone aware of a limit on the number of select statements that a union view can handle? I have tested a union view with 100 "select * " from 100 different tables, with no problem. Just wondering if there is a ceiling, or if the sky is the limit... ^_^ I'm working on 11.…
Seeing one oninit working hard while others are no
No the parameter is SQLTRACE which appears to be off, at least the config line you posted is commented out: #SQLTRACE level=low,ntraces=1000,size=2,mode=global Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that…
how to unload the view in informix DB
Hi All, Any tool to unload the view in informix DB? thanks,…
Directives
Can these be part of a view ... Peter Logan Senior Database Administrator Phone: 616/878-8309…
IFX_FOLDVIEW
Preparing to migrate from IDS 10 to 11.5. Is there any downside to setting IFX_FOLDVIEW to 1? Thank you. DG IDS 11.5 Solaris 10 Sun V480…
Insert into an indexed table is really SLOW!
MIKE DUNHAM-WILKIE wrote: > I am running into performance problems inserting into an indexed table, using > Informix 11.50. The following timings for loading into > compressed/uncompressed, indexed/non-indexed tables using dbload/HPL-Deluxe > more illustrate the problem. The numb…
"Materialized" Views
Hi All, My system: Solaris 10, IDS 10.FC6X7 I got a request to optimize the time it takes to select from a view in our system. I first thought of materialized views. However there seems to be different meanings to that terminology. Not knowing Oracle very well at all it seems …
dirty read isolation in views
Is it possible to create views with dirty read default isolation -or- run views in dirty read isolation? IDS 9.4UC8 Redhat Enterprise Linux 3 AS DISCLAIMER This transmission and the information contained herein and/or in any attachments hereto and/or in any attachments theret…
Re: On performance using views
I had tried setting OPTCOMPIND environment variable to zero and one (originaly it was at two), with no results. We perform a weekly "update statistics high" over all the database, without specifying tables or procedures. That means both kinds of objects are treated the sam…
Re: On performance using views
Hello Omar, Superboer is right. Just one additional thought, did you run your update statistics the same way for your stored procedures as you did for your real tables? -L.S.…
On performance using views
Hi. Some of my workmates are using views very often into stored procedures. We found out that many times performance of this SPs is very poor, but if we execute some of the most heavy queries in dbaccess, they behave ok - I mean, in a short time and without any strange thing…
Ownership for table, view and stored procedure
Hi All Is there any way to change the onwership for table, view and stored procedure without export and import them?…
Re: Please help optimize a query:
First cut: 1. Update statistics to the recommended levels (see the Performance Guide and John Miller III's white paper) 2. Save SET EXPLAIN output for this original query and all subsequent attempts to optimize it. 3. Why are you forcing the use of a particular index? Let th…
Please help optimize a query:
//QUERY IS AS FOLLOWS: select --+index (s ix_sr2) s.trandt ,sub_min as min ,s.s_class ,s.status ,s.active_dt ,s.exp_dt ,s.canc_dt from status_records s where s.canc_dt >= '08/04/2008' and s.canc_dt < '09/04/2008' and s.canc_dt = (select --+index(y ix_sr2) max(canc_dt)…
Informix external directives ?
Dirk Gunsthï¿œvel schrieb: > Hi, > > first you should be able to set the optimizer goal per session using > SET OPTIMIZATION FIRST_ROWS > > secondly how do you see that the optimizer hints (that is what you > mean, right?) are not used? Do you have any helpfull output? > > (I h…
Caching Data to Buffers
Regarding Update Stats - short answer It synchs meta data with your real data, providing the SQL optimizer with the best possible query plan(s). MM…
unused indexes
howdy does informix have system views/meta data that indicates the last time an index was used? thanks tom…
Re: unkillable sid's
On Feb 29, 4:58 pm, Darren_Jac...@carmax.com wrote: > Here is the stack from the onmode -X: > > 17:51:37 Stack for thread: 309 sqlexec > > base: 0xc000000249ffd000 > len: 69632 > pc: 0x0000000000000000 > tos: 0xc000000249ffecd0 > state: running > vp: 3 > > ( 0) 0x4…
Craziness with views
Ok, HPUX 11iv1 Informix 9.40.HC5 I came across some thing utterly nuts: create table data_a ( id integer, vali integer); create table data_b ( id integer, val char(1)); -- data insert into data_a (id,vali) values (1,1); insert into data_a (id,vali) values (2,1); insert into da…
schema for views
When i use 'dbschema' i only get the schema for tables, procedures and synonyms. how can i get the schema for views ( without querying the sys tables) i m using IDS 7.3 and 9.4 both.…
4gl/esql syntax
On Wed, 2007-08-08 at 13:35 -0400, sergio kapul wrote: > looking for correct syntax to create directives inside > queries to overwrite optimizer query execution plan, > to be used in 4gl/esql, I got something that works in straight sql, but it's > ignored when I put it inside of …
Re: alter view or something
On 15 Jun, 17:45, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Jun 15, 10:55 am, b <b...@bb.hr> wrote: > > > So, we have views and on top them we have private synonyms. Users get > > permissions on private synonyms which are in fact views. > > From time to time data structure…
alter view or something
So, we have views and on top them we have private synonyms. Users get permissions on private synonyms which are in fact views.…
IDS Optimizer Query Plan, declaring and naming indexes
I'm seeing differences in performance for the same SQL being executed on two slightly different databases. Using Set Explain, the difference is when database1 exectues part of the WHERE, it goes down the Index Path for that particular table and it's fast. On database2, the query …
query execution plan
As Jack Parker mentioned earlier, there may not be any problem, optimizer just choosing query path based on number of rows. If you are seeing performance problem with one plan versus other, may consider using optimizer directive to enforce the correct path. -Sanjit Chakraborty…
Re: RE: if else error in dbaccess
my subselect2 and subselect3 are create view statements means select case when (select count(*) from tab1)>9 then create view tab1_view1 bla bla... else create view tab1_view2 bla bla.... end; its throwing error at create view…
View contents of backup file
Hi All. I would appreciate all help to my query. I have a set of (presumably) binary files that I believe were generated following a backup of an informix database. I am told the "onunload" command was used. Each file corresponds to a table in the database. I would like to vi…
Slow query execution from a view
When I used Informix extension outer syntax the result was even worse. I seem, when I used view, the implicit temp table created regadless of syntax (join). Without view the output rows are ready without any delay. And with view I have rows with other ordering rather than without…
Software Testing Update
Tutorials for using WinRunner and LoadRunner are added in order to provide a broader view of using the specified terms. http://it-techexperts.com/tutorials/tutorials.asp…
Re: query optimize
Devaraj Takhellambam said: > Thanks for replying...The query has been working well and takes just about > 20-30 minutes in the past but recently it has been degraded and took about > 3-4 hours and sometimes 5 hour..so i thought it needs to be > optimized....Thanks.. OK, so how a…
query optimize
Hi, How can I optimize the informix query below- the query uses an OUTER join... select col1, col2, '' col3, col4, A.col5, A.col6, count(*) cout from table A, table B, table C, OUTER table D where A.col9 = B.col9 and A.col7 = D.col7 and A.col8 = 'Y' and …
VLDB Design - Books/Journals/References
Hi, Need help from the folks on list regarding VLDB design. Are there any books out there that can provide the design basics for VLDB application design? TIA, Asheesh ----------------------------------------------------------- The views expressed are my personal views and in n…
help with view
RUnning IDS 9.40.FC6 on HPUX 11.11 I create a view with the following select: CREATE VIEW v_cust (div, cust_num, ship_num, cust_name, address1, address2, attn, city, province, postal_code, phone, terr_code, sort_name, cust_type, cust_group, inv_date_last, hold_code, soft_a_i…
Fwd: Multi-table views
Reposting for Liviu ----- Original Message ----- From: Liviu Lintes <informix-forum@iiug.org> At: 9/13 13:38:50 How can you identify if a view can be updateable? The view cannot be updated if it's a multi-table view or if it's a sigle table view but contains calculated columns. …
Re: Permissions on a View
Chris S wrote: > Here's a snippet from the Informix 10 Manual: "When you create a view, > PUBLIC does not automatically receive any privileges for a view that > you create. Only you have access to table data through that view. Even > users who have privileges on the base table o…
Permissions on a View
Here's a snippet from the Informix 10 Manual: "When you create a view, PUBLIC does not automatically receive any privileges for a view that you create. Only you have access to table data through that view. Even users who have privileges on the base table of the view do not autom…
Re: VP memory cache (was IFX_FOLDVIEW)
Guy . wrote: > > What is this "Virtual Processor Memory Cache"?? > > This feature is fallout from Informix revisting the world of benchmarks, as is the other xC5 feature, Fastpoll. > Well IDS 10.00.xC5 is out now. And the release notes do not mention (even though this was menti…
Re: Poor view performance
Can you post the view definition and the output from 'set explain on'. Be sure to post the exact view definition - newlines and all. Ben ----- Original Message ----- From: "Paul Watson" <paul@iiug.org> To: <mosserp@wellsfargo.com>; <informix-list@iiug.org> Cc: <paul@oninit.com…
RE: Poor view performance
> -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] On Behalf Of Paul Watson > Sent: Wednesday, May 24, 2006 10:01 AM > To: informix-list@iiug.org > Subject: Poor view performance > > Solaris 2.8 IDS 10.0.FC4 > > Simple vi…
Poor view performance
Solaris 2.8 IDS 10.0.FC4 Simple view joining two tables (4M and 25M rows). One is the live table, the other the archive As a select/union the query is quick and uses all the 'proper' indexes. As a view it does a S-scan over the 25M table and I always got bored before it comple…
IFX_FOLDVIEW
This is what is in the release notes for 9.40.FC8: 1.5.2.1 Enabling View Folding to Improve Query Performance For certain situations where a view is involved in a query, view folding can significantly improve the performance of the query. In these cases, views are folded into…
IFX_FOLDVIEW
hello all, i am not finding much on this new configuration parameter - IFX_FOLDVIEW - maybe i am blind but i am not seeing it...? thanks for any help! Tom…
Cannot read system catalog (systables)
We have IDS 9.40. I created a view called SalesReport for a report that we are creating. It worked ok. After a while we found out that the view needed to be modified. Dropped the view and recreated it. Not I cannot query anymore that queue or delete or replace it. When I try t…
Materialized views ...
I know the answer to this is most likely in a manual somewhere ... hopefully this is faster though ... Does anyone know if IDS 10 supports materialized views ... or virtual tables that can be shared between sessions ... Peter…
help for extending material in sales view
hi there I am extending material to sales view . path SAP R/3 Easy Access: Logistics--> Sales and Distribution---> Master Data---> Products--> Material---> trading Goods----> create Question: create sales view for trading goods Difficulty: the Sap system does n…
changing datatype in view
In IDS 7.31 and higher, is it possible to create a view where the datatype of the view column is different than the datatype of the underlying table? For example, table a has column b integer, but the view based on table a would have column b char(10).…
Merry Christmas & Happy new year
Thank you Captain. XMAS is for all (my own views). -:)…
Re: View performance in IDS - suggestions?
Matt Penning wrote: > Hi all, > > I'm having performance problems with views in IDS 10 (RC3,Linux). > > Creating a simple view with one join and then querying against it > results in not only a temporary table being created, but a temporary > table with no indexes on it. The temp…
View performance in IDS - suggestions?
Hi all, I'm having performance problems with views in IDS 10 (RC3,Linux). Creating a simple view with one join and then querying against it results in not only a temporary table being created, but a temporary table with no indexes on it. The temp table creation followed by sequ…
Re: Weird Sysmaster behaviour
I would unload this view and stick it into a new table in a new database. Create an index on the table so you can query it quickly.…
Systables partnumber
They are views which aren't stored in the database. dbschema -d <database> -t <tablename> Will show you the SQL that created the view.…
Re: IFX_FLAT_UCSQ
>> I am sure flattening of uncorrolated subqueries (all, some types? don't know!) is off by default. This is not correct. I am sure that any query can be flattened if the cost says to do it that way. Also read the excellent link that Bill Dare included: http://www.iiug.org/waiug…
Invalidate View under IDS 10
Greeting All, Since IDS 10.0.UC3 is out with many excellent features and bug fixes, does any one know if IDS 10.0 supports invalidate views if parent object has been dropped. If yes, which catalogue/sysmaster table does have that info ? Also, upon recreating the parent object ca…
OPTCOMPIND
I set to to 0. I have seen loads of questions on here in the past about upgrading to IDS and finding everything running slowly. It appears that sometimes even though 2 should mean least cost the optimizer gets it wrong and will not use indexes. Setting OPTCOMPIND to 0 avoids th…
Cost Estimation for queries
Again optimizer considering BUFFERS/RA_PAGES while considering estimated no of I/Os is not a good idea since buffers are part of volatile memory where at a point of time we cannot be sure how many pages reside in memory satisfying the query for which optimizer is evaluating the b…
Re: purpose of optcompind and optgoal
calyanram@hotmail.com wrote: > Hi > > what is purpose of these two parameters ? > > regards > s.kalyan I'll answer since I was one of the users pressing for something like OPT_GOAL to be implemented back in the 7.24 days. OPT_GOAL tells the optimizer whether to optimize for the…
purpose of optcompind and optgoal
calyanram@hotmail.com wrote: > Hi > > what is purpose of these two parameters ? > > regards > s.kalyan I'll answer since I was one of the users pressing for something like OPT_GOAL to be implemented back in the 7.24 days. OPT_GOAL tells the optimizer whether to optimize for the…
Creating Views
I haven't worked with views before, but I have successfully created a view which looks at a subset of fields in a subset of rows in a table according to a criteria. My current view creation would be something like this: CREATE VIEW view1 (f1, f2, f3, f4) AS SELECT f1, f2, f3, f…
Re: SPL DATETIME bug ????
This performance fall off is only on datetimes, if the where clause uses integers then it's fine. If I drop the datetime select into the exec blade the performance is back to 0.3 Art S. Kagel wrote: > Paul Watson wrote: > > The difference is that effectively the second query/s…
RE: SPL DATETIME bug ????
check the explain for both query, I believe they are deferent. In the first query the optimizer know the range of date at optimization time, but in the second it do not. In each query he build deferent query plan. You should force the optimizer to work as the first query using di…
triggers and views not created by dbimport
The "database.sql" file created by running dbexport contains all schema information for creating views and triggers. But the views and triggers are all missing in the new database created by running dbimport. Any help is greately appreciated.…
Materialized Views
Hi, I heard somthing about materialized views for oracle and sql-Server. I think, this technique is very interesting, especially when you have to join certain "less-change"-tables very often. Is there any comparable technique for Informix (IDS 9.4)? Thanks in advance! Bye Mar…
Re: using Views vs Table
One specific con...views involving aggregate columns can be murder on performance. Any query referencing the aggregate column triggers a "temp table for view" to be created at the outset. Before it does anything else it will create a temp table to be the view. If the table beh…
using Views vs Table
If the view is a simple view, then it really won't cause any performance issue. However, if the view has additional complexity that the query doesn't need, then using a view will be more expensive. Basicaly a view is an sql macro. When the query is against a view, the query pl…
Create View with Union All Syntax error
Hi All , Can this syntax create view with union all work on SE? THanks FRancis sending to informix-list…
Report or View ?
This is a multi-part message in MIME format. ------_=_NextPart_001_01C42939.47F5A969 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Quick question. We currently have space problems. The MIS users = (when they run large reports)…
Re: Re: Query Permormance improvement: URGENT
>Nonsense. The order in which the optimiser decides >to do the processing is critical. I Agree, but as long as the optimizer give a plan wich has some _sense_ , which is certain almost all the the time... But in the cases when the optimizer plan is _nonsense_ (statistics et all…
Re: Query Optimization ... Is this all I can get??
The reason your original solution didn't work well is that You needed to have the correct indexes, which you have added and then the outer join query should be better and faster. > Although your first option would work in my case, it would be a mess, > as they would have to be O…
Re: Query Optimization ... Is this all I can get??
Anthony Presley wrote: > > Although your first option would work in my case, it would be a mess, > as they would have to be OUTER joins of the address and phone table, > and the joining of some 100K with 300K with another 150K takes some > time [although, this was my original sol…
Re: Query Optimization ... Is this all I can get??
Yes - (amendment to my own response) - it's usually best to head an index with the most selective column, unless you have lots of queries that only reference the other column(s) or have an ORDER BY or GROUP BY that requires the index to be in a particular sequence. As for UPDATE…
Re: Query Optimization ... Is this all I can get??
Thanks for everyone's advice. Unfortunately, I am using a combination of Java and I4GL (moving everything to Jasper Reports at the moment), and was just using the "FOREACH" as an example. In reality, it is a for loop. I first added the (type, type_id) index, and then did an up…
Query Optimization ... Is this all I can get??
Hello all .... Always with the query questions .... so I come to you once again. 1st -- my textbooks on this subject are rather sparse, and a bit ... un-useful. Anyone have any good links / articles / books on query optimization? 2nd -- the actual problem .... I have a SIMPL…
Problems with views?
Hi I have a problem with our system which is yielding error -243. Platform: Windows 2000 Informix 9.21 TC6R1 Single user development environment VB6 front end Scenario: Running a stored procedure which involves a view as part of a SELECT may sometimes cause the error "Could no…
Problems with views?
This is a multi-part message in MIME format. ------=_NextPart_000_003A_01C3C3E5.F75CFF60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi I have a problem with our system which is yielding error -243. Platform: Windows 2000 Inform…
Redbrick Query Optimizer behaving funny.
Hi All, Today I saw very funny behaviour of Redbrick query optimizer where the order in which tables are listed in the from clause is controlling the indexes being used. I am sending the results of explain for both the conditions: CONDITION 1: ============= RISQL> explain …
Order BY Cluase on View
YES - you can use order by in select clause on view. You cannot use ORDER BY clause in CREATE VIEW statement (prior to version 9.40). Gorazd "KchittiBhooma,NarsaReddy" <kchittin@ks2.stph.net> wrote in message news:bm5m4a$tdh$1@terabinaries.xmission.com... > > Hi > > I have ques…
Re: order by view
"rkusenet" <rkusenet@sympatico.ca> wrote: > It seems even in Informix 9.4 we can not use order by clause > in a view > create view viewname > as select a,b > from table1 > order by b ; > will result in syntax error. > SQl Server 2000 also does not allow this. However Oracle…
order by view
It seems even in Informix 9.4 we can not use order by clause in a view create view viewname as select a,b from table1 order by b ; will result in syntax error. SQl Server 2000 also does not allow this. However Oracle allows. We recenetly had to change the definition of …
view on a synonym
IDS 9.21.UC4 If a view is created on a synonym, then the view definition takes the base table and not the synonym table. For e.g. 'table_a' has a synonym 'table'. new if a view is created as follows create view table_view as select * from table ; the definition of the view …
OPT_GOAL setting at 4gl level
Hi, I have checked the manuals about where I can set the OPT_GOAL parameter. Below are the IBM docs, but they do not specify if I can set the OPT_GOAL in the 4gl code. Can anyone confirm if I can set this in the 4gl code? If I must do this at the session/user level, I assume this…
Re: Entity-relationship between tables
As request by you to post the ouput of script, pl. have a look at below: ----------------------------------------------------- Example: $depend.sh authority aunrmast Searching for tables that (aunrmast) has an FK to: Searching for tables that refer to (aunrmast) with an FK: Sea…
Indexing on views?
Hi I am working with 9.21 and Crystal Reports 7. I have created a view to base the report on but it takes quite a while for the report to appear. In general I have noticed that with views it does seem to take longer to access the data. The views are normally made up of a main ta…
Re: OPT_GOAL
On Tue, 20 May 2003 10:00:30 -0400, Greg Mitchell wrote: Setting OPT_GOAL to zero (or using FIRST_ROWS optimization in SET OPTIMIZATION or optimizer hints) is useful for internactive applications where records will be displayed a few at a time. It makes the app appear to be mor…
Help on SetNet32 with SDK 2.80
All the environment variable are seting correctly, however the error persists in occurring. Which would be the cause, in view of that all the configuration is correct? Thanks Denis…
Re: Views
Without the source that created it you are kinda screwed. Do a dbschema and edit that, then recreate it. I'm not sure if you can alter them with an alter table command, never tried. brett Martyn Shiner wrote: > > I have a db that has several views (virtual tables) set up. How d…
Views
I have a db that has several views (virtual tables) set up. How do I find out what the SQL statment is that created the view? Can I edit this or do I have to drop the table and set it up again? Informix version is 7.20.UE2 Regards Martyn…
create views
Can you create a view in one database that looks at a table(s) in another database? Thanks. - Jane ------------------------------------------------- Jane Vohden Database Administrator Fairbanks North Star Borough --------------------------------------------------…
index on a view in another database
short form of my question: can I create an index, off of a view that is created in another database than the one that has the underlying table for the view? longer explanation of my question: I have created a view in one database Y that does a select on a table in another databa…
Privileges on views
With database connect and table select privileges as a regular user, I can create a view. However, I haven't been able to grant privileges to other users on this view until I'm granted DBA privilege. Why is this? Have I missed something? -- it doesn't make sense that I can cre…
How do I set FIRST_ROWS for client session?
Set the environment variable OPT_GOAL=0 or add a SET OPTIMIZATION FIRST_ROWS in the apps. Art S. Kagel bafarwell@earthlink.net wrote: > > Does anyone know how to set the FIRST_ROWS optimization goal for a > client session (i.e. Windows client using native libraries, setnet) so …
create view
Please help me ! I want to create a view with an "union" statement. Ex : create wiew articles (a_id, a_name) as select a1_id, a1_name from article1 union select a2_id, a2_name from article2 It doesn't work ... SYNTAX ERROR on the UNION statement... What is the good syntax ??? …
Views
Hello, I want to know how Informix handles views. If I have a database with some views connected and I put a limit on the upper view. Dows Informix reach the limit to the lower view and execute it there? It would be great if somebody could help me. Best regards Stefan Blanken…
VIEW efficiency?
What is the efficiency loss on INFORMIX 7.3 when using a simple VIEW to SELECT and UPDATE the table it points to? No WHERE clause, we are just changing the tablename and fieldnames. Eric Loos Advanced Technologies Sent via Deja.com http://www.deja.com/ Before you buy.…
Indexes and Views
Greetings ! If I have a source table (T) and a view (V) of that table. When my application issues a SELECT against V, how does Informix use indexes ? Is it able to use an index against (T) for the underlying creation of (V) and *then* use another more appropriate index for the SE…
View permission!!!
I already create a view that take data from another database in the same server and the message is "272: No SELECT permission" when I run a simple select to that view. I test login with INFORMIX and the result is the same, with dba permission both database and is the same. Please…
Query Optimization: Wrong execution plan?
Hello All, I have a problem with a query: The query is the join of two tables select * from tab1, tab2 where tab1.pk=tab2.fk There're indexes on both fields (unique index on tab1.pk and indexes with duplicates on tab2.fk). The problem is that the query takes too much time. …
Performance comparison btn Informix and SQL Server
Thanks for all the suggestions, I will try them out, and give a summary about the results. P.S. One small question I have now is: optimizing one query brings another one down! :-( At least I have one query which takes double so long as it took before I run UPDATE STATISTICS.…
Data type conversion in query
Does anyone out there know how to force the optimizer/engine to convert data types in a query? I have a query that is joining a CHAR(20) column to an INTEGER column. Right now it is trying to convert the CHAR to an INTEGER which won't work. If I could make it convert the INTEGE…
RE: using UNION in view statment
IDS 7.24 does not support UNION in views, but IDS 7.3x does. -----Original Message----- From: musman@my-deja.com To: informix-list@iiug.org Sent: 6/24/00 8:48 AM Subject: using UNION in view statment Hi We are using Informix 7.24 on Solaris 2.6. Can we use UNION while creatin…
question on VIEWS
I'm using views (or trying to) for the first time. I want the table to have permissions set to select only for public. Then I want one view that allows select and insert on all columns and another view that allows 'all' on a subset of those columns. I created the table first an…
Using Views
Since I have created some views in my DataBase, all the SELECT statement s go more slow. The Select statements query tables that don�t have views. When I drop the views, the Select statements return the data faster. � Can anybody tell me why the views makes the database slow? Tha…
How to generate view schemas?
Hi everyone! Does anyone know of an Informix 7x utility/program that generates the schema(s) for all the view(s) which are derived from a given table/view? I'm thinking of something like the dbschema utility, maybe we can call it dbview, which would take the table or view name …
Using views
What have been people's experiences using views? What are the pitfalls in using them? Their worst limitations? Their best use? Or are they such a maintenance nightmare that they shouldn't even be considered? What guidelines would you advise in their use?…
first 'n' rows
sveiga@my-deja.com wrote: > > In article <0cefcfc2.92a34d23@usw-ex0106-048.remarq.com>, > Esther <estherNOesSPAM@ts.es.invalid> wrote: > > I would like to know how can I use the "FIRST_ROWS" optimizer > directive > > and the "SELECT FIRST 'n' ROWS" directive. I don't know the s…
Left join ignores optimizer directive
Hey, I'm wondering if anyone out there has had difficulties with left joins? Our query goes incredibly slow with optimize set high and fairly quickly with it set low. In order to improve performance, we brought in a performance expert who specified some optimizer directives. W…
OPT_GOAL
matur_suksema@my-deja.com wrote: > Has anyone experimented with changing the default for OPT_GOAL from all > row to first rows? Would anyone recommend using first rows for a 200+GB > database? Does anyone have any good/bad experiences that they can > share? You use FIRST_ROWS…
Error on GRANT SELECT in a View with Union
Hello All, Thank you in advance. I am trying to create a View, which contains a UNION statement, and I am receiving error "302: No GRANT option or illegal option on multi-table view. " It creates the view, does the REVOKE ALL then errors on the GRANT SELECT statement. Can you not…
Guidelines for using views
Hello there, I am looking for a set of guidelines for using views. Those guidelines are written down in order to make an easy management of them. For exemple, the number of view level to use in a view (a view on top of multiple views on top of other views). I am also looking …
GRANT on VIEW
Hi ! I have created view to existing table from the user tmpuser1 > info access for mytable; User Select Update Insert Delete Index Alter tmpuser1 All All Yes Yes No No Now, I want to add same acces…
RE: Multiple Drop and Create View Statements
By default, dbaccess will continue executing SQL commands. If you "export DBACCNOIGN=1" before invoking dbaccess, processing will stop after the first error. -----Original Message----- From: Gail [mailto:wormang@sutterhealth.org] Sent: Thursday, February 24, 2000 9:50 AM To: inf…
Multiple Drop and Create View Statements
Hello, I'm new to writing Informix SQL and would appreciate any assistance you can offer. My problem is, I am writing a script, which will DROP and CREATE multiple views (see example below). The DROP view does not always exist but I want to include the DROP so that if it does exi…
Query Optimizer Weirdness in Stored Procedure
(Environment: Sun Enterprise/450, SunOS 5.6, Informix 7.30UC5) Greetings All! Has anyone out there encountered this same problem, and figured out a solution? I have a single table with over 10 million records in it. I am trying to retrieve a small number of records from it ba…
OPTIMIZER DIRECTIVES IN 4GL
Hello. I can use optimizer directives from dbaccess / Query Language, but they don�t work in a 4gl-program. The engine is IDS 7.30.UC2-1 and RDS-4GL is 7.20.UD7 on a SCO OpenServer 5.0.5. Example: select {+INDEX (tablename indexname)} * …
index on view
Hi, ALL, Is there any way for Informix 7.3 on UNIX, an index can be created on an view? I am performing a query on the join of two table A and B, the search criteria AC for A table returns a lot of records, and the search criteria BC on B table also returns a lot of records, but…
query optimizer (set explain) question
i've run set explain on three different systems (all hpux 10.20 with informix ds 7.3 uc2) the databases in question are the same schema (tables and indexes, same onconfigs, etc.) on each machine (however number of rows in each table varies. I ran set explain on each machine with…
Re: URGENT! How many rows will be returned?
I smell a troll here... Different databases use different technologies for query optimization and some are far better than others. Some 'so-called' optimizers need subtle hints to steer queries while others produce cost estimates based on statistics. Each database on your cros…
View or Table
Hi, I am new in Informix , and want tu ask : I have a table and I want to know if a table is table or view. :) I do not know the script that was a table created. Is there any method to find out this ? thanx Roman…
RE: Prepared statement vs Exec sql insert/update/select
> Is there any benefit to preparing and executing sql statements > rather than just having them in an exec sql insert/update/select.... [Bill Raper] In the Informix world, there is a very significant benefit. This is in the fact that the optimizer processes are run only once …
One HUGE Query - any optimizers?
This is a multi-part message in MIME format. ------=_NextPart_000_0082_01BEF4B3.58C19EC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've developed a program which constructs the following query and then = executes it. INSERT INT…
syschkio view
Hi all, I am trying to understand the syschkio view. What is the relationship between read/pagesread. I read in an article that read means # of read operations and pagesread means # of pages read.Therefore, I identified situations where the number of reads is less than the numbe…
Re: Help optimizing query (newbie)
Just a quick thought. If you are using transation logging it will be worth will creating the temp table with the no log option so it does not to any transaction logging. Nayan Jain wrote in message <7lapvi$3im$1@news.xmission.com>... > >Hi ! > >Just thought of correcting one thi…
Optimizer question
Hi all, Informix Dynamic Server Version 7.30.UC6 Can someone please tell me if the optimizer takes into account the number of extents for tables when deciding on a query plan? Thanks Steve _______________________________________________________________ Get Free Email and Do …
VARCHAR in table, CHAR in view
So, I'm working on a system right now that has a requirement that the character columns in our tables be of CHAR data type. This is due to an interface with some legacy application and is not negotiable. My question is this- If I created a view against the table where the only d…
Online index usage in one table query
In article <37861A36.3B9B6FAB@bloomberg.net>, kagel@bloomberg.net wrote: ..snipped > To the first > question, if your query is prepared with replaceable parameters the > optimizer does not know at prepare time when it is deciding on a query > plan what the actual values will be…
Re: Help optimizing query (newbie)
Hi ! Just thought of correcting one things here On Mon, 28 Jun 1999, Peter Sloboda wrote: > Frank T. Ramb=F8l wrote: >=20 > > Hi. > > > > I am trying to run the following statements on a rather huge database. > > > > =3D=3D > > begin work; > > set lock mode to wait; > > > > up…
Help optimizing query (newbie)
Hi. I am trying to run the following statements on a rather huge database. == begin work; set lock mode to wait; update tracked_ids set chk_count=-1 where image_id in (select image_id[16,26] from image_ids); delete from image_ids where image_id[16,26] in (select image_id fr…
Re: Indexing
John: The optimizer is a cost based optimizer and the order of items in the query will not matter unless you use optimizer directives. Now if the filter created by "a=? and b=? and c=? is not very selective, and the optimizer estimates that a large number of rows will be returne…
SQL performance
Shaun Campbell wrote: > > I have a query regarding something we have noticed using New Era and > wondered whether it a bug in New Era or is related to the database. > > In New Era we have been preparing SQL Select statements with ? > placeholders so that we could use the same pre…
7.30UC2 - Subquery uses Autodindex instead of Index
Richard Peterson wrote: > > Hello, > > I have this problem after upgrading a server from 7.20 to 7.30UC2. The > optimizer appears to process subquerys diffrently when the in clause is > used. Rather than using the index on the table used in the subquery it uses > an autoindex ins…
Statement level statistics
Does any body know how to enable the statement level statistics colums in the sysmaster:syssqexplain view/syssdblock table?…
Re: Severe Performance Problem - Infomix 7.3 WGS & HPUX 10.20
eric wrote: > > We're in the process of upgrading from Informix 5 to Informix 7.3 Workgroup > Server but are experiencing severe performance problems. Some programs > which used to take 5 hours to complete are now taking 19 hours, on identical > hardware with identical indexing …
Re: anyway to optimize this query?
>query only. Well, then, why not create all possible index combinations? :-) >Obnoxio The Clown wrote: > >> Sometimes it helps to treat a 2 CPU box as a single CPU box. Have you >> tried this? >> >> >PHYSBUFF 32 # Physical log buffer size (Kbytes) >> >LOGBUF…
Re: anyway to optimize this query?
query only. Obnoxio The Clown wrote: > >let's see... > > > >there are 61772773 rows in the table. > > > >The table is indexed on three fields, let's call it f1,f2,f3, it is a > >composite index, the reason I do that is because > >there is also a where clause in the query which…
Re: anyway to optimize this query?
>let's see... > >there are 61772773 rows in the table. > >The table is indexed on three fields, let's call it f1,f2,f3, it is a >composite index, the reason I do that is because >there is also a where clause in the query which uses all three fields as >criterias. > >update satis…
Re: anyway to optimize this query?
let's see... there are 61772773 rows in the table. The table is indexed on three fields, let's call it f1,f2,f3, it is a composite index, the reason I do that is because there is also a where clause in the query which uses all three fields as criterias. update satistics was p…
Re: anyway to optimize this query?
>hi all: > I have a 16 gig database, each record is contains 50 fields. > I am running a query like this: > > select f1,f2....f15, sum(f40),sum(f41),sum(f42), sumf(43) > group by f1,f2...f15 > > as you see, i have to group about 15 fields and sum 4 other fields >ba…
anyway to optimize this query?
hi all: I have a 16 gig database, each record is contains 50 fields. I am running a query like this: select f1,f2....f15, sum(f40),sum(f41),sum(f42), sumf(43) group by f1,f2...f15 as you see, i have to group about 15 fields and sum 4 other fields based on th…
simple question on view limits
Is their a limit on the number of columns in a view, or tables in a join using IDS 7.30.TC7? Thank You, Nicole Guffey DBA Nguffey@jdriscoll.com…
What Y2K problems do people expect with Informix?
Any views appreciated. (Let's take it that upgrading to a tools version that supports DBCENTURY is assumed). -Andy Kent- Bristol, England…
composite index question
Yan Zhu wrote: > > hi all: > If I put a composite index on three fields, a,b,c, what kind of > query would this benefit? > > I made a few queries with differenct criterias, and set explain on, > this is what I discovered. > 1. if the criteria is only on a or b or c, i…
How to avoid to have to write the owner in ANSI databases
> Change the logging mode of the database to BUFFERED or UNBUFFERED. > ANSI mode databases require the use of a table's owner in order to > reference the table, sorry. > > Art S. Kagel What about using separate views for each user ? Each view would contain a whole table. This …
i4gl - programs are limited.
Hi there, Has anybody a workaround for the following problem. With the i4gl or r4gl you can make programs. The number of programs that can be viewed is limited. More or less about 92. Is there a sollution for viewing more programs? Because me application has more than 92 progra…
Re: Flushing Cache
Bryan Hughes wrote: > > Does anyone know a way to flush cache in Online 7.2x short of a) writing a > huge query, and b) cycling the engine? I am trying doing to query > optimization and need to do raw timing of the same query written in > different ways. Shut the engine down an…
Views and Informix
I was wondering if someone might know how Informix implements VIEWS. In a discussion with our Informix Systems Engineer, he indicated that when a query is executed through a view, the engine first creates and populates a temporary table with a subquery constructed from the viewd…
Re: Problem with Informix 7.3 I need a good Idea very soon
Bundesarchiv wrote: > > E posibile > to mark a table (a priori) as "AVOID_FULL"(scan) ?? > > or (i.e. with UPDATE STATS) "near fullscan disabled" ?? > > (in Informix 7.3) > > I need a good Idea very soon See other posts. Also have you tried OPT_GOAL=0 which can be set in the ap…
Re: Problem with Informix 7.3 I need a good Idea very soon
Hi Klaus, in this case you will have to deal with the different levels of update-statistics, until you get what you want. Try to do "typical" queries after 'set explain on;' in dbaccess and try to figure out, why the optimizer takes a full-table-scan. hth martin Bundesarchiv sch…
problem with views
These are our products $isql -V INFORMIX-SQL Version 4.12.UE1 $ i4gl -V INFORMIX-4GL Version 4.10.UC2 and RSAM Version 5.02.UC7 ----------------------------------- We have two databases, called historica and sucesion, respectively. We have in sucesion a table called pagano, …
Re: Distributions
Stefan Weideneder wrote: > > Jay Aymond wrote: [SNIP other stuff] > Do you think it's a good idea to give the optimizer detailled > informations for columns, if the information will not cause > a better query plan ? Don't you believe that it will slow down > those queries that d…
Re: SET EXPLAIN / SQL
Stefan Weideneder wrote: > > Hi David, > > as Sujit mentioned already, the results of SET EXPLAIN are > available as soon as you prepared your query. > This is not always true, but in most cases. > > If the query contains no question-mark "?", your query will > be optimized at th…
Re: 7.23 UC4 vs. 7.30 UC3
snip... > >> Also, Oyvind, we have experienced the wrong query path being selected by the > >> optimizer is 7.x for some time. We understand that 7.30 allows the user (us) > >> to control the query optimizer more directly. > > > >Yes, we have used this feature to verify that the …
Re: SET EXPLAIN / SQL
I am not sure but you can use Optimizer directives to select adhoc query plan. For more information about optimizer directives, you review the presentation slides about informix performance optimization by Kevin Fennimore on the following URL location (thank you Kevin). Good luck…
Re: How to list alle tables in database
SELECT tabname FROM informix.systables WHERE tabid >= 100 and tabtype = "T"; otherwise you get synonyms (tabtype = "S") and views ("V")…
Re: Infromix & Data Warehousing Solutions
Doug Johnson wrote: > > Tim, > > Thanks for the info but we're not on the same page. I'm not talking about > the Informix query optimizer (access plan shown by 'explain plan'). There's > tons of info on www.informix.com on that optimizer. I'm talking about the > new MetaCube Ware…
Re: Infromix & Data Warehousing Solutions
Tim, Thanks for the info but we're not on the same page. I'm not talking about the Informix query optimizer (access plan shown by 'explain plan'). There's tons of info on www.informix.com on that optimizer. I'm talking about the new MetaCube Warehouse Optimizer. My understanding…
Re: Views
SaTriGuy wrote: > Basically all that a view does is to expand the query to include the view. So > there is no more actual overhead than if you had expanded your query to include > the definition of the view. > > Actually the view's definition is reduced to only include the item…
Re: Views
>Does anyone know the performance hit (or not) I may take by putting a datbase >that consists soley of views in a dbspace with another database soley >consisting of views? > > I'm not sure that I understand this. Are you wanting to create a database consisting of nothing but vi…
Views
Does anyone know the performance hit (or not) I may take by putting a datbase that consists soley of views in a dbspace with another database soley consisting of views? -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp …
Creating a view with union clause
In Informix you cannot create a view that includes a union clause. Do you know how to solve this problem or how to emulate a view that uses union clauses in informix? ====================== Mario Garc�a Franco Cap de Projecte Centrisa - Val�ncia e-mail: mgarcia@vlc.centrisa.es…
Re: Creating Informix Views on Oracle tables/views....
Dear Sir/Madam: I am Kal Thakkar, recruiter for Software Tactics, Inc., which is a small to midsize consulting firm serving fortune 1000 clients in Chicagoland area. Currently there are several permanent/consulting positions available for a programmer/analyst with background such…
Creating Informix Views on Oracle tables/views....
Hi All, Does anyone knows as to how to go about creating views in Informix Database v7.2 for Oracle objects (Tables,Views) v7.3.3... All what we need is a SQL-based gateway that allows Informix tools, applications and databases to interoperate transparently with non-Informix da…
Re: Images in IUS
You should look into the Web DataBlade, it will let you upload and and download(view) images with a web browser.…
Re: query optimizator
>Something strange happened to me (is not the first time) with >Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. >A query that we use frequently, suddendly stopped working. >The query optimizer decided to change something, and the query now >do not use anymore the rig…
Re: query optimizator
Marco Traini wrote: > > Something strange happened to me (is not the first time) with > Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. > A query that we use frequently, suddendly stopped working. > The query optimizer decided to change something, and the query now >…
query optimizator
Something strange happened to me (is not the first time) with Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. A query that we use frequently, suddendly stopped working. The query optimizer decided to change something, and the query now do not use anymore the right in…
Re: I can't think of the subject name (have not words).
David Kosenko wrote: > "Art S. Kagel" <kagel@bloomberg.net> offerred: > +> > An annoyed Informix user wrote: > +> Do You see index3 on table1(field1, field2)? There isn't sorting > needed. > +WRONG WRONG WRONG! > +> There isn't table reading needed too. index3 contains all > …
Re: I can't think of the subject name (have not words).
"Art S. Kagel" <kagel@bloomberg.net> offerred: +> > An annoyed Informix user wrote: +> Do You see index3 on table1(field1, field2)? There isn't sorting needed. + +WRONG WRONG WRONG! + +> There isn't table reading needed too. index3 contains all information +> queried and in righ…
Re: Data binding
"Art S. Kagel" <kagel@bloomberg.com> offerred: +Yes. Versions before 7.3 resolved the query path at PREPARE time so, +except for an EXECUTE IMMEDIATE (which prepares and executes at the +same time), these two queries MUST be treated differently. Since the +actual value of the…
Re: Optimizing a select query
Sujata, Try creating the following index on tab2 (col2, col1). This way it will only need to do an index read. Also run update statistics high (if SQL/Server has the concept). Regards David Ashby ______________________________ Reply Separator _…
Optimizing a select query
Hi everyone, Here is a question for you guys. I am facing a problem with a simple select query running toooo slow. Before I proceed, let me explain that I am facing this problem in SQL Server not in Informix. I know the die-hard Informix fans will say "Well... there is your a…
Re: Optimizing..
In article <6gc0fc$qe1$1@news.xmission.com>, "ABEL ROJAS GARCIA" <abelrg@bital.com.mx> writes: > > Hi, > > What can I do for Optimizing queries? > > > Thanks How about giving an example of query you'd like optimized.…
Re: Optimizing..
Go to the Informix Advanced Development and OnLine Administration classes. They're a definite investment. Either that or do some SERIOUS reading. Vij. ABEL ROJAS GARCIA wrote in message <6gc0fc$qe1$1@news.xmission.com>... > >Hi, > >What can I do for Optimizing queries? > > >Th…
Optimizing..
Hi, What can I do for Optimizing queries? Thanks…
Rowids in a view?
Is there any way to include a rowid in a view of two tables? I have a C++ application that requires a unique identifier and a view that does not provide me with anything unique enough. Rowid of the constituent tables looks like the proper thing to send, but I cannot place it in…
view table
Dear Listers: We have a master employee table with thousands of records. Whenever a department updates its records it will lock other ppl records as well ( we are using the default page level locking). What's the best way to handle these updates, BTW, this kind of updates can be…
Re: Update statistics of the informix.xxx tables.
>Is the update statistics scenario of the informix.systables alike tables >of a database the same as for the users tables. ? For the most part the access to these tables use a "fixed plan". Since this information is mainly needed by the optimizer to generate the query plan, we …
Where is the work done?
I have 3 databases on Informix, let's call them A, B, and C. Databases A and B both have tables, but C is comprised completely of views. If I create a view on database C that joins tables from databases A and B, where will the work be done? In what database will the worktables…
Re: Prepare a Statement that uses a temporary table?
Georg Winkens wrote: > > Is there a way in ESQL/C to prepare a Statement that uses a temporary > table (e.g. "Select * from Temp_Table ...") if this temporary table is > not already created at prepare time? No there is not. The purpose of preparing a statement is to save the co…
Re: Optimizer Info
>Are there any ways to know how the optimizer is evaluating all the >possible query plans and how it decides which one it chooses. > >I know about 'set explain' and data distributions and such, but is there >a way to see the process >the optimizer went through. Basically, no. T…
Optimizer Info
I am running 7.1 on Solaris 2.5 (soon to be upgrading to 7.24). Are there any ways to know how the optimizer is evaluating all the possible query plans and how it decides which one it chooses. I know about 'set explain' and data distributions and such, but is there a way to see…
Re: Query Optimizer
>Help me please! >I need information about algorithm of Query Optimizer. >Informix Engine - OnLine Dynamic Server 7.10 One can write a book on this topic. Basically, the optimizer looks at all possible ways of scanning every table in a query. The cost for each path is calculated…
Query Optimizer
Help me please! I need information about algorithm of Query Optimizer. Informix Engine - OnLine Dynamic Server 7.10 .…
Re: Question: Performance of a view as opposed to a table
> What's the performance hit on selecting, deleting, inserting, etc. >on a view compared to working with the table directly? This question has >to do with views with no operation (e.g.: columnA - columnB) on columns, >just straight selection. In 7.23 + the query against a view …
Re: DELETE does not use index
>Sorry for my stupid question, but what does UPDATE STATISTICS do in detail? No such thing as a stupid question. UPDATE STATSTICS updates information in the database catalogue, such as systables, sysindexes, sysdistributions. Information from these catalogue tables is read by …
Re: Stored Procedures and querries.
From "Incorporating Stored Procedures and Triggers into Your Informix Databases Training Manual" Version 01-95 Page 6-128: When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist o…
Re: Running UPDATE STATISTICS on a table being used
sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote: > Since the update statistics was going on parallelly with the query, will the > query use the new plan once the update statistics is over or does it still go > with the old plan ?? The query is just taking for eve…
Re: Set explain use
>Not considering indexes, does it make any difference to the informix >optimizer > if we switch the order of conditions in a where clause. For example, which >one > is better: > >select * from A where p = q and r = s or >select * from A where r = s and p = q . > >None of p,q,r,…
optimizing - sqexplain
Hi, We are developing an application with New Era 2.20 using Online 7.10 (about to upgrade to 7.23). My goal is to optimize the response times and I'm new in that function. The first idea is to search through the access plan for high cost queries, the problem is that I don't kn…
Re: Query Optimizer
Dies ist eine mehrteilige Nachricht im MIME-Format. --------------7BAFAD1E691FD235D6CAD43F Content-Type: text/plain; charset=iso-8859-1 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Transfer-Encoding: 8bit Hello Mauro, but there still exis…
Re: Query Optimizer
Mauro G. Pisano wrote: > > HI all: I recently heard about the possibility of upgrade de query optimizer. > I 've got ON-LINE 5.0 and at Informix Seminary, It was listened taht by > upgradin only the query optimizer, somes exposed queries at the seminary, > had down its times fro…
Query Optimizer
HI all: I recently heard about the possibility of upgrade de query optimizer. I 've got ON-LINE 5.0 and at Informix Seminary, It was listened taht by upgradin only the query optimizer, somes exposed queries at the seminary, had down its times from 30 minutes to seconds. I want …
informix viewer
Does anyone know of a informix viewer that can be used to view tables in a informix database?…
Re: Way to make columns exist, but not take up any space?
Sorry, that I haven't read the original post, but I'll give it a try anyway. I'd do the following: Create a table and then create a view on that table, that incorporates a virtual column that is a constant. E.g. CREATE VIEW blahblah (virtcol,col1,col2,col3,col4,...) AS SELECT "…
How many tables in a View?
What is the limit (if any) of tables that can be used in a view? (I am simultaneously learning Informix and designing tables for an application and this is important to know for my table/view design approach.) Thanks in advance. - Tom Fredericks Cornell University…
Re: First N records in SELECT
Slavik Anosov wrote: > > Does anybody know something in Informix ODS 7.1 that perform functions of > Oracle's ROWNUM pseudo-column? I need to retrieve only first N records in > select. > No, there is no such feature. Assuming that you are using ESQL-C or 4GL you can simply close…
Re: Query Optimization question
Ignatius In version 7.x (not in 5.x) you can do: UPDATE STATISTICS MEDIUM FOR table(column) DISTRIBUTIONS ONLY The MEDIUM will do data sampling whether or not you create an index on the column. The DISTRIBUTIONS ONLY update the sysdistrib table in your database. In case you …
Re: Query Optimization question
On Thu, 10 Jul 1997, Ignatius Cristina Michelle wrote: > Hi. > > What statistical information can Informix draw upon to determine how > many rows will be satisified by a query coondition of the form "column = > value", for example, "department = 'D'" where "department" may or ma…
Query Optimization question
Hi. What statistical information can Informix draw upon to determine how many rows will be satisified by a query coondition of the form "column = value", for example, "department = 'D'" where "department" may or may not be an "indexed" column. Where is this statistical informati…
Re: Set explain utility
>The sqexplain.out file exists immediately after the prepare. >Please, try it again. How can this be? At the PREPARE stage (for any real-life query) crucial information - values to use - are still missing. It is only at OPEN CURSOR when we tell the engine the values, and only t…
Re: adjacent key locking
> > Any views ? > > You tell me, its your database. 8-))))))))…
Re: DSA follies
> Correct - hold cursor declared with hold are not parallelized... > Several other types 'queries' are not parallelized - can't remember > which... Wow, what a coincidence! I just read a page on this in the Performance Tuning Training manual. Queries NOT parallelized: * Th…
Re: Problems with VIEWs
Your select can't possibly work fine alone without a "group by something" clause. marcus@balu.sch.bme.hu (Mohos Csaba Mark) wrote: :I've problems with VIEWs. I've created a VIEW: : :CREATE VIEW something (title, number) AS :SELECT somebody, COUNT(any) :FROM book, lending :WHERE…
Problems with VIEWs
I've problems with VIEWs. I've created a VIEW: CREATE VIEW something (title, number) AS SELECT somebody, COUNT(any) FROM book, lending WHERE book.isbn = lending.isbn The SELECT statement works fine alone. If I create the VIEW, DbAccess returns View Created. I make a query from …
Access to Optimizer results
We are using version 7.2 on HP-UX 10.10, and would like to know if there is any way to access to "cost" (from the optimizer) of a query while the query is executing without having the explain plan written out to a dataset. We are trying to develop a quey govenor that can be used…
Problem with a VIEW
Hi all, I'm with Florida's Agency for Health Care Administration. We run Informix 7.21 on Solaris 2.5.1 I've run into an issue with view that I created to give name, mailing address , street address, and bed count for hospitals on a regulatory and admin licensing system. The …
Use of unions in Views
Informix views do not support unions while Oracle does( I need to port some Oracle views). Anyone with suggestions for workarounds ?? TIA, -Lin…
Re: understanding On-Line's Optimizer
Steve Romankiw wrote: > > I am new the Informix On-Line engine. I am looking for information on > On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from > SQLBase and the optimizers work differently. In informix, we often get > sequential scans on queries that w…
Re: understanding On-Line's Optimizer
At 10:05 AM 19-12-96 -0500, you wrote: >I am new the Informix On-Line engine. I am looking for information on >On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from >SQLBase and the optimizers work differently. In informix, we often get >sequential scans on que…
Re: understanding On-Line's Optimizer
sromankiw@execrisk.com (Steve Romankiw) wrote: :I am new the Informix On-Line engine. I am looking for information on :On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from :SQLBase and the optimizers work differently. In informix, we often get :sequential sca…
Re: understanding On-Line's Optimizer
On 19 Dec 96 at 10:05, Steve Romankiw wrote: > I am new the Informix On-Line engine. I am looking for information on > On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from > SQLBase and the optimizers work differently. In informix, we often get > sequential s…
understanding On-Line's Optimizer
I am new the Informix On-Line engine. I am looking for information on On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from SQLBase and the optimizers work differently. In informix, we often get sequential scans on queries that we feel should be using an index.…
Re: update stats resolution
Manoj Shroff wrote: > > Hi All, > > Does anyone have any guide lines for the use of the > resolution clause in "update statistics" ? Hi All, data distribution is an additional overhead for the optimizer. Therefore you should try to avoid data distributions whereever pos…
Re: Optimize A Simple Query -- Help
Chao Y. Din wrote: > > I have a simple select count statement that takes three minutes to > execute, yet I only have about 300,0000 rows. Here is the SQL. > > SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 > > Is it possible to force the query to …
Re: Optimize A Simple Query -- Help
Chao Y. Din wrote: > > I have a simple select count statement that takes three minutes to > execute, yet I only have about 300,0000 rows. Here is the SQL. > > SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 > AND col2 = 10; > [SNIP] > Is it possible…
Re: Optimize A Simple Query -- Help
At 08:29 AM 9/16/96 -0400, you wrote: }I have a simple select count statement that takes three minutes to }execute, yet I only have about 300,0000 rows. Here is the SQL. } } SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 }AND col2 = 10; } }The "key" field …
Re: Optimize A Simple Query -- Help
tvarnas@compulink.gr (Apostolos Varnas) wrote: :Chao Y. Din wrote: :} :} I have a simple select count statement that takes three minutes to :} execute, yet I only have about 300,0000 rows. Here is the SQL. :} :} SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND …
RE: Optimize A Simple Query -- Help
Chao Try creating an index on table (col1, col2, key). This should make the = optimizer utilize the index path. Also since col1 and col2 are highly = duplicate values, this will give the index some degree of uniquenss so = the optimizer finds it worth giving the new index a try.…
Re: Optimize A Simple Query -- Help
Chao Y. Din wrote: } } I have a simple select count statement that takes three minutes to } execute, yet I only have about 300,0000 rows. Here is the SQL. } } SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 } AND col2 = 10; } } The "key" field is th…
Re: Optimize A Simple Query -- Help
Chao - have you tried "update statistics medium..." or high...? That might induce the optimizer to choose the more discriminating index. If you're running "low", the optimizer is flying pretty blind when it comes to data distributions. Greg Chao Y. Din wrote: > > I have a s…
Optimize A Simple Query -- Help
I have a simple select count statement that takes three minutes to execute, yet I only have about 300,0000 rows. Here is the SQL. SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 AND col2 = 10; The "key" field is the primary key with data type char(11). …
Re: Update Statistics Low, Medium & High
billy@west.co.za (Billy Wheeler) wrote: :> >In our system, we discover sometimes some tables in index searching is much :> >faster while we update their statistics low or medium instead of high. :> I heard a discussion the other day which indicated that when you update :> stati…
Re: Update Statistics Low, Medium & High
Clem Akins wrote: > > >In our system, we discover sometimes some tables in index searching is much > >faster while we update their statistics low or medium instead of high. > > > >We have an idea that update all tables high statistics with reference to the > >server admin. guide.…
Updating views
Does anyone know what the situation is regarding the case where a view is set up to prevent a particular user or users from accessing particular columns in a table? - Can a row be update, subject to the rules to enforce a one-to-one mapping between rows in the view and the paren…
Informix, PowerBuilder, Views
We would like to use views rather than table access with PowerBuilder. However . . . The views are not updateable, no matter what we try. They are updateable in ISQL under Unix. Just not under client/server via INET. We made certain to have a primary key. We made certain to …
Informix Bug? error -360
I am trying to run this statement with Informix 7.10: INSERT INTO a SELECT 1, b.* FROM b B is view that is a join between table A and another. When this statement is run, I get this error: -360: Cannot modify table or view used in subquery. Of course the text of the er…
Updating views
Can anyone tell me if views can be updated in informix, and if so to what extent? Any help would be appreciated. Thanks, Tom Gray thomas@kainos.com…
UNION in a view
Can anyone tell me why I cannot utilize the UNION SQL operator in a view definition? This would make my life a LOT easier!! I am using Online 5.03 TIA Allen Jantzen…
Re: How to view a view
Adrian Davis <adavis@lbdplc.demon.co.uk> wrote: >I'm using SQL ver 4.1 on AIX. >Having defined lots of views with fairly complex select statements, I >can't see an easy way of displaying the view definitions. I can pull >apart the sysviews table, but is there a simpler or more el…
How to view a view
I'm using SQL ver 4.1 on AIX. Having defined lots of views with fairly complex select statements, I can't see an easy way of displaying the view definitions. I can pull apart the sysviews table, but is there a simpler or more elegant solution? -- -----------------------…
Set Explain with SP's
In attempting to determine how the optimizer is processing queries in stored procedures, we've tried turning 'set explain on' within a procedure. However, when executing the procedure, I get an empty file. Is there anything special which needs to be done, to get set explain outp…
Views
Hi All We're using INFORMIX ONLINE Ver 5.01. Does anyone have any ideas on including a 'UNION' in a view. There have also been occasions when a conditional feature eg. 'IF ELSE' would have been extremely useful within the view. Is there any chance that future versions will s…
Re: EXPLAIN result diff. in TEST and REALITY
: In our TEST DB the result of a joined query and SET EXPLAIN is INDEX PATH : for the two tables. On our REAL enviroment this result is for one table : is SEQUENTIAL SCAN, and for the other one is INDEX PATH. Is this normal ?? : The query is awful slow, maybe thatsway ?? Hi, am…
VIEWS in sperform
Hi, Can anyone out there pl. put opinions on using views in sperform. Scenario goes like this. 1. I have 2 tables invoice Invoice Master ( Key = invoice_no ) consignee Consignee Details ( Key = invoice_no ) Schema For Table : invoice invoice_no …
Re: Distributed Join
>I remain very sceptical. To optimize a join across multiple vendor databases >requires that your 'joining' product must fully familiar with all >the individual optimizers in each database to understand how they will process >their portion of the query. Next it must be familiar…
about INDEX
Good day people, somebody could tell me which is the use of INDEX and one example. I think that with INDEX, I can to optimize my querys in 4gl, thanks in advanced. Atte. German Velazquez…
Re: Query Optimising Problem !!
Optimizing a query can be very quick once you type set explain on and then run your query. The output will tell you how the optimizer has decided to process your request: it will show you what indexes it uses, what filters and when it is doing a sequential read. It can be su…
Re: Query Optimizing Problem !!
In article <3i16po$c1s@raffles.technet.sg>, Alan Chen <sgs@technet.sg> wrote: >I have never done any query optimization practise in Informix Online >before, can anyone provide me some pointers ?? Any good reference book ?? >Any idea will be appreciated. > >Thanks in Advance. > >A…
Re: Optimizing Many-Table Joins
mmueller@calvin.bellahs.com (Michael Mueller RD) writes: >Can anyone tell me the tactics that the query optimizer uses for >optimizing joins, especially queries joining a large number (e.g. >4) >of tables? What is the max number of tables allowed in a query? >How does this comp…
Optimizing Many-Table Joins
Can anyone tell me the tactics that the query optimizer uses for optimizing joins, especially queries joining a large number (e.g. >4) of tables? What is the max number of tables allowed in a query? How does this compare to Sybase's handling? Thanks! --Michael mmueller@bella…
using VIEWS
Folks, Are there any reasons, such as pig overhead, not to use views? They seem handy for generating forms and then, in 4gl,defining structure variables "like" the views. (I'm the one who employed the NOT EXISTS, because it seemed like a good idea; now I'm leery of everything.)…
View Point
I have an Informix Database, whose main table has 5000 K rows. Managers want take Decision Support Information from it. Do you think View Point may be a good tool for this task? What kind of experience did you have with VP? Will it be useful with a so large table? Thanks in …
RE: Optimize Query for slow responce
> 26-Oct-1994 > >I can not understand the Informix Query Optimizer ! >--------------------------------------------------- > > >I have a fine large table and I create a query, where I select all fields from >the table. To lookup the records I have an unique index on a field…
Re: Optimize Query for slow responce
In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) says: > > 26-Oct-1994 > >I can not understand the Informix Query Optimizer ! >--------------------------------------------------- > > >I have a …
Re: Optimize Query for slow responce
In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) writes: |> Now look at the Optimizer Query Plan: |> |> |> QUERY: (LOW) |> ------ |> select * from ks_gdkonto |> where gd_kontonr = "0003573508" |> and gd_prod_kode < 90000 |> |> |> Estimated Cost: 2 |> Es…
RE: Optimize Query for slow responce
} 26-Oct-1994 } }I can not understand the Informix Query Optimizer ! }--------------------------------------------------- } } }I have a fine large table and I create a query, where I select all fields from }the table. To lookup the records I have an unique index on a field…
Optimize Query for slow responce
26-Oct-1994 I can not understand the Informix Query Optimizer ! --------------------------------------------------- I have a fine large table and I create a query, where I select all fields from the table. To lookup the records I have an unique index on a field called '…
Re: View Management Tools
Hi, We have a logical database tool, which will manage views although it only runs on the unix end at present and is possibly overkill for simply managing views on systems with less than 100 entities Graham Twaddle Technical Director Beta Computers (Europe) Ltd beta@cix.compuli…
View Management Tools
Are there any tools out there to manage database views? I have found that on every database I have designed, in order to meet the needs of users (who are only interested in querying logical views of the data), I end up creating large numbers of views which are nested in intricat…
Creating a view
Folks, I am running RDS 4.10.UC1 and would like to as the following question: I have two tables that have the same structure (one temp, one permanent) and I would like to create a view that is the union of these two tables. My problem is that the UNION operator is not valid bet…
Optimizer & Views
Hello Informixer who know about the optimizer! I have a problem with the performance when selecting through a view. The output of the SET EXPLAIN option shows that there is a temp table generated for the View I use. I assume that my performance problems come from this temp table…
Re: Query Optimizer
In article <dennisp.762623729@puma>, Dennis Pimple <dennisp@informix.com> wrote: >root@perk.jpr.com (Joseph A. Miele) writes: > > >>The following two queries QUERY 1 and QUERY 2 are the output of SET EXPLAIN >>ON. I do not understand why the query optimizer does a sequential sca…
Re: Query Optimizer
root@perk.jpr.com (Joseph A. Miele) writes: >The following two queries QUERY 1 and QUERY 2 are the output of SET EXPLAIN >ON. I do not understand why the query optimizer does a sequential scan on >root.alujobsh in QUERY 2. The only difference in the two queries are these >lines…
Query Optimizer
The following two queries QUERY 1 and QUERY 2 are the output of SET EXPLAIN ON. I do not understand why the query optimizer does a sequential scan on root.alujobsh in QUERY 2. The only difference in the two queries are these lines: QUERY 1: atkliquh.inv_num = '12316' and QUER…
INFORMIX views
Greetings! A co-worker and I were discussing how INFORMIX handles a view on a table. I was under the impression that when you select off a view, INFORMIX merges the view criteria (ie, the WHERE clause) with your criteria and then executes the result. Therefore, if your criteria …
Re: Optimizing queries info
Greg Sheppard (imop%uspfo.wa-ngnet.army.mil@wa-ngnet.army.mil) wrote: : We've recently written a couple of reports which take FOREVER (hours) : to run...and are getting very interested in streamlineing our queries : for speed and efficiency. Is there a book or useful info on ho…
Re: Optimizing queries info
}From: bt@irl3b2c.att.com }Date: Thu Dec 16 9:15:34 GMT 1993 }Subject: Optimizing queries info }X-Informix-List-Id: <list.3228> } }Greg Shepard wrote } }> We've recently written a couple of reports which take }>FOREVER (hours) to run...and are getting very interested in streamlin…
Re: Optimizing queries info
Greg, The best way to speed up queries is temp tables. If you use set explain on you might find informix is creating its own temp tables but not making good choices. You know you data better that the engine so you should be able to build better (smaller) temp tables. -- Greg …
Query Optimizer or Lack Thereof
Yet another example of a slooooooow query. This does get frustrating because there are too many times that a perfectly good query has to be split into parts (ie. temp tables) in order to get some decent access time. In the query described below, Does anybody have a clue as to …
Re: Optimizing queries info
> > > } > }I suggest that Jonathan Leffler's book - "Using Informix-Sql" [ISBN > }0-201-51240-8] is *required* reading if you want to do anything with isql. > > Thank you very much. > Well deserved I might add (the compliment - not the thanks). j. _____________________________…
Re: Optimizing queries info
}From: bt@irl3b2c.att.com }Date: Thu Dec 16 9:15:34 GMT 1993 }Subject: Optimizing queries info }X-Informix-List-Id: <list.3228> } }Greg Shepard wrote } }> We've recently written a couple of reports which take }>FOREVER (hours) to run...and are getting very interested in streamlin…
Re: Optimizing queries info
Greg Sheppard (imop%uspfo.wa-ngnet.army.mil@wa-ngnet.army.mil) wrote: : We've recently written a couple of reports which take FOREVER (hours) : to run...and are getting very interested in streamlineing our queries : for speed and efficiency. Is there a book or useful info on ho…
Optimizing queries info
Greg Shepard wrote > We've recently written a couple of reports which take >FOREVER (hours) to run...and are getting very interested in streamlineing >our queries for speed and efficiency. Is there a book or useful info on >how best to get the quickest results with queries using…
Optimizing queries info
We've recently written a couple of reports which take FOREVER (hours) to run...and are getting very interested in streamlineing our queries for speed and efficiency. Is there a book or useful info on how best to get the quickest results with queries using iSQL? Or is it just a…
Query on Views
A query on views : What is the performance overhead on using views ? I have heard of a couple of problems where using views changes the optimizer's approach to queries - is this a general problem or isolated bug(s) ? Thanks in advance, Richard Ridley DHL Asia Pacific…
VIEW - Problems
Well, my last INFORMIX-problem was solved by this group, but I have a new problem with Informix-VIEWs: There is a View defined over ( for simplicity ) 2 tables (Regard the OUTER! ): CREATE TABLE A ( A_ID SERIAL (1) , A_NAME CHARACTER(10), B_ID INTEGER ); CREATE TABLE B ( …
Re: Query Optimiser
John B writes: |Has anyone noticed the 5.X optimizer making bad choices when |choosing a query plan? choosing a query plan? In my case the |optimizer insists in performing a sequential read of a detail table |ignoring the unique index used to join to the master. Performance, …
Query optimizer in 5.01 Informix
Has anyone noticed the 5.X optimizer making bad choices when choosing a query plan? In my case the optimizer insists in performing a sequential read of a detail table ignoring the unique index used to join to the master. Performance, as you can imagine is very, very poor. Infor…
Informix query optimizer info please
I am looking for detailed information on how the Informix query optimizer operates (i.e. makes its decisions, what it looks for, good tips on how to formulate tricky queries, etc.) I have a reasonable amount of experience in the land of Sybase, but I'm new to Informix. After I …
Re: SQL query
Bob Baskett writes: |netters, |can someone explain why the optimizer doesnt short circuit on the |following query: | QUERY: | ------ | select * | from book_bill_back | where 1=2 | Estimated Cost: 370832 | Estimated # of Rows…
False queries and optimizer
Is there any way to slip fake queries past the online5 optimizer? E.g., select * from foo where field1=42 works fine, uses the index on field1, while select * from foo where 1=0 or field1=42 has the optimzer doing a sequential scan on all of foo... (As for "why do I care", I…
Informix Online 5.0 Query Optimizer
We have a problem concerning Informix Online 5.0 query optimization. Informix seems to be choosing extremely inefficient paths for no apparent reason. The part_ref table originally had only one index, a composite of partnumber and option. When a query was performed that joined …
SQL Views
Hello Informix Net: I have been working with ISQL v. 2.10 for MS-DOS for some time. I have successfully created a view using the RDSQL language format, everything in the informix system says the view is ok. Question: How do I now use the view to display data on the screen, qu…
Views and More views
REPLY-TO: lugnut@columbia.com ����������������������������� Message Of The Moment: A baby is an alimentary canal with a loud voice at one end and no responsibility at the other. X-Mailer: ELM [version 2.4 PL13] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII…
Re: Indexing strategy
1rk2ciINNrhr@emory.mathcs.emory.edu writes: >: Stuff deleted.. >: If you define the unique 3-part index before the >: non-unique 2-part index, the query optimizer may already be using the 3-part >: index (so I have heard), since the optimizer finds the 3-part index first, >: an…
Re: re:choosing idx to use
Ann Barnes@letterkenn-emh1.army.mil writes: |> |> When you create a composite index, the first field is also, effectively an |> index of its own. This was mentioned in the query optimization article here |> yesterday, but perhaps not specifically stated. Actually we have found i…
Re: Query Optimism
%UNIPLEX %TO informix-list@rmy.emory.edu %FROM rridley %SYSTEM DHLNET %SUBJECT Re: Query Optimism %VERIFY y %DATE 29/03/93 16:30 %REFERENCE 17053 Organization: Informix Software, Inc. Re: >In article <billd.733006990@cheetah…
Re: Help: SQL query optimizer
In article <billd.733006990@cheetah>, billd@informix.com (William Daul) writes: |> |> From: Spring 1987 TechNotes |> |> Query Optimization |> ================== |> Uh, Bill? This article is on the Rule-Based optimizer, which we have not used since 1989 (the 4.0 release of p…
Re: Help: SQL query optimizer
Anique Van der Vlugt (anique@sun-4.cs.uct.ac.za) wrote: : : Hi : : I am presently involved in a project for a SQL query optimizer for INFORMIX. : Has anyone done any research in this field or knows of anyone who does? I : am also interested in any articles related to the field o…
Re: Help: SQL query optimizer
In <anique.732967452@sun-4> anique@sun-4.cs.uct.ac.za (Anique Van der Vlugt) writes: >I am presently involved in a project for a SQL query optimizer for INFORMIX. >Has anyone done any research in this field or knows of anyone who does? I >am also interested in any articles rela…
Help: SQL query optimizer
Hi I am presently involved in a project for a SQL query optimizer for INFORMIX. Has anyone done any research in this field or knows of anyone who does? I am also interested in any articles related to the field of query optimization. If you can help me in any way please email m…
informix views
here is the question: how informix (ON-LINE) processes queries involving views? it seems to me that it first materializes the view and than executes selections... it would be much better to perform string substitution first and than materialize the view using only a subse…
I-Star and Views: Problem resolved
Thanks to all who responded to my earlier posting. To recap, the problem was: ----------------------------------------------------------------------------- Created a view of a remote database. Creator of the view attempted to grant SELECT permission on the view to other users -…
Re: I-Star and views
In article <1m0jt3INNbqn@emory.mathcs.emory.edu> jgordon@ssf-sys.DHL.COM (Jim Gordon) writes: >How about getting the view owner to GRANT permissions on the view >to the required users or to PUBLIC. > >Cheers - Jim The owner of the view has *tried* to grant permissions on the vi…
I-Star and views
We are trying to use I-star to make a useable view of a remote Informix database. The view has been created but only the view *owner* can use it. We have contacted Informix tech support to no avail. If you have successfully used I-star to make a view of a remote database, pleas…
Views on I4GLMODS
Apologies if you've already read this but I think my newsfeed/mailer's playing up. ------------------------------------------------------------------------ FWIW here are my comments: I4GL-R03: Part of the functionality for this would come from I4GL-I04 where windows are ope…
An insider's view
I recieved this message today from someone inside Informix. I've been asked not to identify the individual but am posting the message with permission. ----------------------------- Hello, I hear every day that we have the best 4GL on the market ! It seems that our 4GL has a ve…
Re: Two index query optimization problem
Devin Wala writes: > > >I'm having some problems with Informix over optimizing my query. >I have a table with > 500000 rows and I can't get Informix to >use what I deem to be the "correct" index. > >There are two indexes on the table: > > index1: unique index on A, B; > index…
Re: Two index query optimization problem
> I'm having some problems with Informix over optimizing my query. > I have a table with > 500000 rows and I can't get Informix to > use what I deem to be the "correct" index. > > There are two indexes on the table: > > index1: unique index on A, B; > index2: unique index o…
Two index query optimization problem (fwd)
} I'm having some problems with Informix over optimizing my query. } I have a table with > 500000 rows and I can't get Informix to } use what I deem to be the "correct" index. } } There are two indexes on the table: } } index1: unique index on A, B; } index2: unique index o…
Re: Two index query optimization problem
In article <1992Dec12.000653.7004@anasaz> devin@anasaz (Devin Wala) writes: > > >There are two indexes on the table: > > index1: unique index on A, B; > index2: unique index on C, D, A, B; > >The problem I'm having is that I can't run a query with A and B mentioned >in the WH…
Two index query optimization problem
I'm having some problems with Informix over optimizing my query. I have a table with > 500000 rows and I can't get Informix to use what I deem to be the "correct" index. There are two indexes on the table: index1: unique index on A, B; index2: unique index on C, D, A, B; …
View approach.
I am losing an argument with isql 4.10. I hope someone out with more experience with this system will be able to steer me in the right direction. I have a bucketload of time series data (1600+ channels of hourly building energy use data) currently modeled as one table per channe…
In view update
Our application needs to do an in view update of the screen whenever certain parts of a database are being updated. I.e. a user may be viewing the contents of a table simultaneusly with an ongoing update of the same table. The user requirement says that the screen must be immidia…
Re: QUERY Optimizer
> Hi netters > > I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is > very often guessing wrong, even if we run update statistics. > > The estimated number of rows returned does very much depend on the > sequence in which you enter the SELECT statement. In one …
QUERY Optimizer
Christian Hansen write: >I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is >very often guessing wrong, even if we run update statistics. > >The estimated number of rows returned does very much depend on the >sequence in which you enter the SELECT statement. …
QUERY Optimizer
Hi netters I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is very often guessing wrong, even if we run update statistics. The estimated number of rows returned does very much depend on the sequence in which you enter the SELECT statement. In one case we had …
creating a view
Path: emory!swrinde!cs.utexas.edu!qt.cs.utexas.edu!yale.edu!heifetz!math.fu-berlin.de!uniol!unido!sbsvax!ukh840!becker From: becker@ukh840.med-in.uni-sb.de Newsgroups: comp.databases.informix Message-ID: <15648@sbsvax.cs.uni-sb.de> Date: 17 Jan 92 10:58:13 GMT Sender: news@sbsvax…
query optimization
jmn@pdx.csd.mot.com (Jim Nielsen) write : >Can anyone tell me where I might find some definitive information on >how to optimize wildcard searches. > >I have a table which contains 300,000 records and I need to look up >records based on 6 possible fields. Some of the fields can…
Permissions on views
Nigel Brickenden <brick@bcr.bellcore.com> writes (quoting me) > >A couple of guys suggested using views for protection. This is > >okay provided that there's no means of going straight to the > >base table (i.e., the user can't call isql, etc), but the > >protection …
Query Optimizer
What follows are some excerts from my previous posting and an update: > > In running the query described below on my OLD version 2.10.03K performance > was completely acceptable. On version 4.0, ouch!!!! Queries that took seconds > were now taking lots........ of minutes like 15…
Re: Update Statistics
> > Does anyone have a recommendation on how often the update statistics > command should be run? 'update statistics' only updates the system catalogs with the correct number of rows and the 'next-to-highest' and 'next-to-lowest' values so that the optimizer can make an educated…
(no subject)
rhlab!kuhn@uunet.uu.net writes: > In running the query described below on my OLD version performance was > completely acceptable. On version 4.0, ouch!!!! Queries that took seconds > were now taking lots........ of minutes like 15,20,25 minutes. > > After sending tech support a s…