Thread: Optimizing sql
12-10-04, 12:31 #1Registered User
- Join Date
- Oct 2004
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.
12-10-04, 22:17 #2Drunkard
- Join Date
- Nov 2002
- Desk, slightly south of keyboard
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
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).
BillPlease 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.