Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Unanswered: Reg Performance Issues

    Hi ,

    I have a general question regarding performance based on the table size..specifically the number of columns...

    This is my question...Will number of columns in a table affect the performance of a query...I have two tables...The one with around 20 columns
    of which only 7 or 8 of them are used in my query...The second table is a duplicate of the first table but essentially having only those 7 or 8 columns that are used in the query..

    Both these tables use the same indexes...but the first one seem to take a lot of time ...may be say arnd 10 mins while the second one is faster say 45 secs...

    Both these tables have millions of records..

    Any suggestions?

    Thanks..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post the EXPLAIN_PLAN for both queries.

    Are statistics current for the table taking a long time to run?

    IMO, the additional number of columns will result in a slight performance penalty;
    but nothing near the magnitude you are experiencing.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    Sorry I am not a DBA and I dont have access to setup a plan ...
    Let me explain it this way...

    I join three tables..

    1st table .. Millions of records (20 to 25 coulmns)
    2nd table .. 5000 records
    3rd table .. 3500 records

    I use composite indexs in all the three tables..
    When the first table is duplicated to have the columns that is needed in the query it runs faster...

    Was that information sufficient?
    Thanks for the support..

  4. #4
    Join Date
    Apr 2008
    Posts
    5
    Analysis for the first index of table 1
    Blevel 2
    Leaf Blocks 3811
    Distinct keys 1021
    avg lf blks/key 3
    avg dat blks/key 655
    clust factr 1869379
    Num rows 2369072
    Sample size 847918

    analysis of 2nd index of table 1
    Blevel 2
    Leaf Blocks 4488
    Distinct keys 2711
    avg lf blks/key 1
    avg dat blks/key 27
    clust factr 215242
    Num rows 2329033
    Sample size 819936

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    As was suggested, you need to get an explain plan. If you can't do it, find your friendly neighborhood DBA to help you. We love doing that kind of thing.

    If the explain plandoesn't help, then you should look at doing some advanced tracing and seeing where the slowdowns are. Oracle has an advanced wait interface and using it can shave hours, or even days, off the guess and try method.

Posting Permissions

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