Results 1 to 2 of 2

Thread: Optimizing sql

  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Smile Unanswered: Optimizing sql

    I'm trying to improve perfomance of a list of sql's. My sql are something like this:

    select /*+ PARALLEL(t,5) */ * from tabla1 where yyyymm in (200301, 200302, 200303, 200304, 200305, 200306) and field1 = '010102' and field2 = ..... fieldn =

    The fields yyyymm and field1 is in all my sql's but the rest of the fields (field2-fieldn) depends on the sql, (it varies from one sql to other).

    My table is partitioned by yyyymm and i have an index (normal, not bitmap) by field1. The rest of the fields don't have a lot of possible values (10-20 values more or less), one of them is in a lot of sql's but it has three posible values and I dont' have an index because I think it doesnt't worth, doesnt' it??

    I use parallel hint because server can make parallel executing. It's a very large table, tipical fact tables of a date warehouse, about 10 millions records.

    Any idea to impove perfomance, to make faster the sql's??

    Any advice or idea will be greatly apreciatted.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Just a suggestion.

    select * from t where c in (1,3,5,7,9)

    can be optimised differently to

    select * from t where c = 1
    union all
    select * from t where c = 3
    union all ....

    The "in" clause can cause enough of the index to be scanned for Oracle to think that a FTS is better. Without histograms on the column being [in'ed] the number of values in the clause could also affect it.

    You can force the optimiser to go the route you want (fine if your data demographics are stable), trust the optimiser, or re-form the query to help the optimiser (in which case you may as well force it through hints).

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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