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

NCC1701BOB@gmail.com wrote: > 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. > When and how have you run update statistics on either database? -- Clive

On Jun 15, 8:50 am, Clive Eisen <c...@serendipita.com> wrote: > NCC1701...@gmail.com wrote: > > 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. > > When and how have you run update statistics on either database? > > -- > Clive I'm not sure what you mean by "how", it was run yesterday. I'm a developer and our DBA is assisting in this and he ran the update statistics through SQL Editor.

NCC1701BOB@gmail.com said: > On Jun 15, 8:50 am, Clive Eisen <c...@serendipita.com> wrote: >> NCC1701...@gmail.com wrote: >> > 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. >> >> When and how have you run update statistics on either database? >> > > I'm not sure what you mean by "how", it was run yesterday. I'm a > developer and our DBA is assisting in this and he ran the update > statistics through SQL Editor. I think he means "which type of statistics did he get: high, medium or low? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

On Jun 15, 9:00 am, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > NCC1701...@gmail.com said: > > > On Jun 15, 8:50 am, Clive Eisen <c...@serendipita.com> wrote: > >> NCC1701...@gmail.com wrote: > >> > 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. > > >> When and how have you run update statistics on either database? > > > I'm not sure what you mean by "how", it was run yesterday. I'm a > > developer and our DBA is assisting in this and he ran the update > > statistics through SQL Editor. > > I think he means "which type of statistics did he get: high, medium or low? > -- > Bye now, > Obnoxio > > "I'm astonished anyone pays real money for this crap." > -- Cosmo > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. "Do you have the same data in both tables ? Do both tables have good statistics in sysdistrib ?" Both tables have about the same number of rows, but not the same data exactly. I don't know about the sysdistrib. I'll try to look and have our DBA look when he gets in. It was update statistics on HIGH.