No, there are no histograms on any of the columns, just the min (LO) and
max (HI) values for each column. However, in the Oracle 8i database,
max(external_accession.SEQTABLEID) (=1770465) is equal to
max(IDENTIFIER.SEQTABID) (=1770463) whereas in the Oracle 9i database it is
not (1101043 < 1770463).
That could explain the different cardinality estimates and subsequently the
conclusion that a merge join would be cheaper than a hash join.
I would import the 8i statistics for external_accession into the 9i
database and see what that does to the plan and execution times.
At 11:44 AM 3/1/2004, you wrote:
external_accession.SEQTABLEID does seem to have different histogram data (1101043 vs 1770465). I am not familair with histograms, should I be doing something like
analyze table external_accession compute statistics for columns SEQTABLEID size 20;
in 9i?
Guang
select lpad(TABLE_NAME,10), lpad(COLUMN_NAME, 10), ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where table_name= upper('identifier');
-- from 9i:
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- IDENTIFIER ID 0 -399575 IDENTIFIER GENEID 0 1 IDENTIFIER SEQTABID 0 62 IDENTIFIER TYPE 0 3.3750E+35 IDENTIFIER SPECIESID 0 1 IDENTIFIER IDSTR 0 2.3465E+35 IDENTIFIER CLASSIFICA 0 1.6681E+35 IDENTIFIER ID 1 1770463 IDENTIFIER GENEID 1 10333859 IDENTIFIER SEQTABID 1 1770463 IDENTIFIER TYPE 1 4.1538E+35
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- IDENTIFIER SPECIESID 1 102 IDENTIFIER IDSTR 1 2.9712E+35 IDENTIFIER CLASSIFICA 1 1.6681E+35
14 rows selected.
-- from 8i:
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- IDENTIFIER ID 0 -399733 IDENTIFIER GENEID 0 1 IDENTIFIER SEQTABID 0 62 IDENTIFIER TYPE 0 3.3750E+35 IDENTIFIER SPECIESID 0 1 IDENTIFIER IDSTR 0 2.3465E+35 IDENTIFIER CLASSIFICA 0 1.6681E+35 IDENTIFIER ID 1 1770463 IDENTIFIER GENEID 1 10333859 IDENTIFIER SEQTABID 1 1770463 IDENTIFIER TYPE 1 4.1538E+35
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- IDENTIFIER SPECIESID 1 102 IDENTIFIER IDSTR 1 2.9712E+35 IDENTIFIER CLASSIFICA 1 1.6681E+35
14 rows selected.
select lpad(TABLE_NAME,10), lpad(COLUMN_NAME, 10), ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where table_name= upper('external_accession');
-- from 9i:
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- EXTERNAL_A SEQTABLEID 0 298261 EXTERNAL_A ACCESSION 0 3.3882E+35 EXTERNAL_A ACCESSION2 0 2.5540E+35 EXTERNAL_A DATABASE 0 5.3680E+35 EXTERNAL_A PCT_IDENT 0 98 EXTERNAL_A LENGTH 0 100 EXTERNAL_A QUERYLEN 0 100 EXTERNAL_A SUBJLEN 0 100 EXTERNAL_A SEQTABLEID 1 1101043 EXTERNAL_A ACCESSION 1 4.6847E+35 EXTERNAL_A ACCESSION2 1 2.9712E+35
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- EXTERNAL_A DATABASE 1 5.9398E+35 EXTERNAL_A PCT_IDENT 1 100 EXTERNAL_A LENGTH 1 94605 EXTERNAL_A QUERYLEN 1 99999 EXTERNAL_A SUBJLEN 1 270752
16 rows selected.
-- from 8i:
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- EXTERNAL_A SEQTABLEID 0 298261 EXTERNAL_A ACCESSION 0 3.3882E+35 EXTERNAL_A ACCESSION2 0 2.5540E+35 EXTERNAL_A DATABASE 0 5.3680E+35 EXTERNAL_A PCT_IDENT 0 98 EXTERNAL_A LENGTH 0 100 EXTERNAL_A QUERYLEN 0 100 EXTERNAL_A SUBJLEN 0 100 EXTERNAL_A SEQTABLEID 1 1770465 EXTERNAL_A ACCESSION 1 4.6847E+35 EXTERNAL_A ACCESSION2 1 2.9712E+35
LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- ---------- --------------- -------------- EXTERNAL_A DATABASE 1 5.9398E+35 EXTERNAL_A PCT_IDENT 1 100 EXTERNAL_A LENGTH 1 94605 EXTERNAL_A QUERYLEN 1 99999 EXTERNAL_A SUBJLEN 1 270752
16 rows selected.
-----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling Sent: Monday, March 01, 2004 1:22 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: query slow in 9i, but not slow in 8i
Given those counts, is there a histogram on external_accession.SEQTABLEID ?
At 10:44 AM 3/1/2004, you wrote: >The rowcount are not exact the same, but close. The 9i dataset is loaded >using the dump that was a few weeks old. And I ran >DBMS_STATS.GATHER_TABLE_STATS after the loading. > >Guang > > >-- from 8173: >MT@atlas-SQL> select count(*) from mt.External_accession; > > COUNT(*) >---------- > 38102138 > >MT@atlas-SQL> select count(*) from mt.identifier; > > COUNT(*) >---------- > 127836 > > >-- from 9204: > >SQL> select count(*) from mt.External_accession; > > COUNT(*) >---------- > 36907691 > >SQL> select count(*) from mt.identifier; > > COUNT(*) >---------- > 127612
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
