Plz Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Plz Help

  1. #1
    Join Date
    Jul 2003
    Posts
    53

    Plz Help

    Hi all,

    I have a table with composite primary key. The query I have has where clause which includes some columns of composite key . But still the query is very low. Is there any other means to improve the performance of the query

    Plzz do help me out

    Thanks
    Anu

    Sorry the table structure is
    table1
    KEY1 NUMBER NOT NULL,
    Key2 NUMBER NOT NULL,
    Key3 NUMBER NOT NULL,
    Ke4 NUMBER NOT NULL,
    Key5 NUMBER NOT NULL,
    Key6 NUMBER NOT NULL,
    Key8 NUMBER NOT NULL,
    Key9 NUMBER NOT NULL,
    Key10 DATE NOT NULL,
    Key11 NUMBER NOT NULL,
    PROD NUMBER NOT NULL,
    CON NUMBER
    OT NUMBER

    Query is: SELECT DISTINCT (Key1),Key2
    FROM Table1
    Last edited by anupamasuresh; 12-08-2003 at 12:18 AM.
    anu

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    No tables structure
    No SQL statement
    No plan
    No number of rows
    No OS
    No Oracle Version

    Please read "How to ask Questions" Then ask!
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I guess the plan would be Index FFS, isnt it?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Re: Plz Help

    Originally posted by anupamasuresh
    Hi all,

    I have a table with composite primary key. The query I have has where clause which includes some columns of composite key .

    Query is: SELECT DISTINCT (Key1),Key2
    FROM Table1
    Invisible WHERE clause! Definitely Index FFS.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi


    create table test
    as select object_id key1,object_name key2,object_id key3
    from all_objects


    create unique index testidx
    on test(key1,key2,key3)

    analyze table compute test compute statistics

    select distinct(key1),key2
    from test


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=306 Card=23004 Bytes
    =667116)

    1 0 SORT (UNIQUE) (Cost=306 Card=23004 Bytes=667116)
    2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=44 Card=23004 Bytes=
    667116)


    well the most expensive operation here obviously is

    SORT (UNIQUE) investigate wheather you really need that distinc(key1)

    regards
    Hrishy
    Last edited by hrishy; 12-08-2003 at 06:28 AM.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    I guess the plan would be Index FFS, isnt it?
    Originally posted by Thomasps
    Invisible WHERE clause! Definitely Index FFS.
    It could as well be ordinary full table scan....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    It could as well be ordinary full table scan....
    Could be if Tab is very small or index data is almost equal to Tab data (No Of Blocks wise)...So was the word Guess put in there...
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width