Re: New 8.1.6 bug of the day

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: 2000/05/17
Message-ID: <8fudcv$6gk$1@soap.pipex.net>#1/1

On 8.1.5 NT This bug exists - I'll be looking as per one of the other posts for a patch. However analyzing the stats did make a difference. For the moment I guess keep analyzing frequently. eg

SQL> set echo on
SQL> @m:\scripts\testsql
SQL> -- alter session set optimizer_goal=rule;
SQL> alter session set optimizer_goal=choose;

Session altered.

SQL>
SQL> DROP TABLE TABLE1; Table dropped.

SQL> CREATE TABLE TABLE1(

  2  f1 VARCHAR2(10) NOT NULL,
  3  f2 VARCHAR2(10) NOT NULL,
  4  f3 VARCHAR2(10),

  5 CONSTRAINT PK_TABLE1 PRIMARY KEY (f1));

Table created.

SQL>
SQL>
SQL> DROP TABLE TABLE2;

Table dropped.

SQL> CREATE TABLE TABLE2 (
  2 f1 NUMBER(10) NOT NULL,
  3 f2 VARCHAR2(10) NOT NULL,
  4 f3 VARCHAR2(10),
  5 CONSTRAINT PK_TABLE2 PRIMARY KEY (f2));

Table created.

SQL>
SQL> DROP TABLE TABLE3; Table dropped.

SQL> CREATE TABLE TABLE3 (
  2 f2 VARCHAR2(10) NOT NULL,
  3 f1 VARCHAR2(10) NOT NULL,
  4 CONSTRAINT PK_TABLE3 PRIMARY KEY (f2));

Table created.

SQL>
SQL> INSERT INTO TABLE1 VALUES ('test1','test2','test1');

1 row created.

SQL> INSERT INTO TABLE1 VALUES ('test3','test4','test3');

1 row created.

SQL>
SQL> INSERT INTO TABLE2 VALUES (1,'test2','test1');

1 row created.

SQL> INSERT INTO TABLE2 VALUES (999,'test4','test3');

1 row created.

SQL>
SQL> INSERT INTO TABLE3 VALUES ('test2', 'test1');

1 row created.

SQL> INSERT INTO TABLE3 VALUES ('test4', 'test3');

1 row created.

SQL>
SQL> COMMIT; Commit complete.

SQL>
SQL> analyze table table1 compute statistics;

Table analyzed.

SQL> analyze table table2 compute statistics;

Table analyzed.

SQL> analyze table table3 compute statistics;

Table analyzed.

SQL>
SQL> SELECT TABLE1.F1, TABLE1.F2, TABLE2.F1, TABLE2.F2, TABLE2.F3   2 FROM TABLE3, TABLE2, TABLE1
  3 WHERE TABLE1.F1='test1'
  4 AND TABLE1.F1=TABLE2.F3
  5 AND TABLE1.F3=TABLE3.F1;

F1         F2                F1 F2         F3



---------- ---------- --------- ---------- ----------

test1      test2              1 test2      test1

SQL>
SQL> spool off;

--

Niall Litchfield
Oracle DBA
Audit Commission UK Received on Wed May 17 2000 - 00:00:00 CDT