Re: IDS Optimizer Query Plan, declaring and naming indexes

Do you have the same data in both tables ? Do both tables have good statistics in sysdistrib ? -----Original Message----- From: NCC1701BOB@gmail.com [mailto:NCC1701BOB@gmail.com] Sent: Friday, June 15, 2007 09:44 AM To: informix-list@iiug.org Subject: 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 plan does a Sequenial Scan of that particular table instead of the Index Path. This is IDS 9.4. The differences in schema for the table in question are minimal: (1) In database1, there is no Index declared, but as I understand the primary key is an index implicitly. In database2, the index is declared and named on the primary key. Could those differences, explicitly declaring and naming the PK and Index, cause a differnce in the Optimizer's approach and query plan? (2) In database2, the Primary Key, and Index are declared outside of the Create Table block. Could declaring the PK, Index, etc OUTSIDE of the Create Table cause it? Below is the basic schema: --database1 create table tablename (... primary key (col1, col2, col3) constraint tablename_key ) lock mode row; --database2 create table tablename (... ) lock mode row; REVOKE ALL PRIVILEGES ON tablename FROM PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO PUBLIC; CREATE DISTINCT INDEX tablename_key_idx ON tablename (col1, col2, col3); ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY ( col1, col2, col3) CONSTRAINT tablename_key; Thanks for any help, Bob _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list