Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: From 1second to 2 minutes in a single column...

Re: From 1second to 2 minutes in a single column...

From: Tore \(PX\) <tbostrup_at_telocity-nospam.com>
Date: Mon, 28 May 2001 00:43:16 -0400
Message-ID: <ZFkQ6.15214$ce.10160424@newsrump.sjc.telocity.net>

Thanks - I posted the Query Plan and more info to Sybrand's response.

The basic layout of the database is (not exact and from memory, but shows structure):

CREATE TABLE INSTRUMENTS (
    InstrumentNumber NUMERIC PRIMARY KEY /*always 10 digits*/,     UniqueDateTime char(22) NOT NULL /*Format MM/DD/YYYY HH24:MI:SS.nn where nn is a sequence number*/,

    InstrumentType int NOT NULL,
    <Other data>)

CREATE TABLE PARTYNAMES (
    ID NUMERIC PRIMARY KEY,
    InstrumentNumber NUMERIC NOT NULL,
    PartyIndex int NOT NULL /* 1 or 2 */,     LastName varchar2(255),
    <other data>)

CREATE TABLE PROPERTIES(
    ID NUMERIC PRIMARY KEY,
    InstrumentNumber NUMERIC NOT NULL,
    <Other Data>)

CREATE TABLE INSTRUMENTTYPES(
    InstrumentType Int PRIMARY KEY,
    Code char(4),
    <Other Data>)

I want to generate a report listing all name-combinations (PartyIndex = 2 for each PartyIndex = 1 for the same Instrument) within a name range (for instance D-F) for instruments within a UniqueDateTime date range (for instance 4/1/1995 - to 6/30/1997) with data from the tables.

Something logically like:

Select <list>
From PartyNames P1, PartyNames P2, Instruments I, Properties D, InstrumentTypes T

Where P1.PartyIndex = 1
  And P1.LastName BETWEEN :FromName AND (:ToName || 'zzzzzzzzzzzz')
  And P2.InstrumentNumber = P1.InstrumentNumber
  And P2.PartyIndex = 2

  And I.InstrumentNumber = P1.InstrumentNumber   And TO_DATE(Substr(I.UniqueDateTime, 1, 10), 'MM/DD/YYYY') BETWEEN :FromDate AND :ToDate
  And T.InstrumentType = I.InstrumentType   And T.Code Is Not Null

Thanks again,
Tore.

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:otg0ht8ciig6vr6laej0qkf81oa5tccpqh_at_4ax.com...
> A copy of this was sent to "tore" <TBostrup_at_teamia.com>
> (if that email address didn't require changing)
> On Sat, 26 May 2001 03:07:32 -0400, you wrote:
>
> >I have a very simple query:
> >
> >SELECT P1.InstrumentNumber,
> > P1.FixedLastName
> >FROM
> > Instruments I,
> > PartyNames P1
> >WHERE I.InstrumentNumber = P1.InstrumentNumber
> > AND P1.PartyIndex = 2
> > AND P1.LastName >= 'A'
> > AND P1.LastName <= 'ACzzzzzzzzzzz'
> >
> >InstrumentNumber is the primary key of Instruments. Instruments has just
> >over 600,000 entries, while PartyNames has over 1.8 million entries. I
 have
> >indexes for all the columns referenced in this query.
> >
> >This query executes in 1 second and returns less than 30 rows.
> >
> >If I change the select list to
> >
> >SELECT Count(*)
> >
> >the time explodes to 2 minutes and 17 seconds!
> >
> >If I add a column from Instrument to the select list (also indexed), I
 get a
> >similar jump in execution time.
> >
> >How on earth is Oracle spending 2 minutes in order to obtain information
> >about 30 rows???????
> >
> >Help !!
> >Tore.
> >
>
> Check out the query plans to see whats up. Use autotrace in sqlplus (see
> http://osi.oracle.com/~tkyte/article1/autotrace.html if you don't know of
 that).
> Post the results and perhaps we can diagnose whats going on.
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://asktom.oracle.com/
 http://asktom.oracle.com/~tkyte/
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Sun May 27 2001 - 23:43:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US