Hello my tuning friends.
I am running Oracle 11.2 (on Solaris 10) for a very active OLTP database, but that is also frequently used to pull reports on the most volatile critical table in the database.
My database has anywhere from 100 to 400 concurrent users.
Our application (REMEDY) is designed to put "tickets" in a single main table (with approx. 560 fields that 40 are clobs).
The dilemma I need help with is whether to put a bit map index on some of the low cardinality columns or not.
And, for that matter, many columns in this table are very low cardinality.
My fear is that I am afraid that putting a bitmap index on a highly active OLTP table might impede performance of ticket creations (new rows in the table).
Or, if I did it, can I have a job run every night that recreates the bitmap indexes on this table?
But what about performance on the table between the time of rebuilding the BM indexes?
In this case, this is a typical query that is run against our main table (containing approx. 6m records and is 16G in size).
When I look in OEM, I see that it does a full table scan on this table, and in this case, only returns 262 records.
Obviously, you can infer the columns in the predicate clause are very low cardinality (by the names of the columns and the values searched).
SELECT "Main_Ticket"."SHORTTICKETNUMBER" ,
"Main_Ticket"."OUTAGESEVERITY" ,
"Main_Ticket"."OUTAGESEVERITYTEXT" ,
"Main_Ticket"."OUTAGESERVICESLIST" ,
"Main_Ticket"."OUTAGESTATUS" ,
"Main_Ticket"."OUTAGEOVERALLSTARTTIME",
"Main_Ticket"."OUTAGEOVERALLENDTIME" ,
"Main_Ticket"."CITY" ,
"Main_Ticket"."STATE" ,
"Main_Ticket"."MASTEROUTAGE" ,
"Main_Ticket"."GROUPCREATEDBY" ,
"Main_Ticket"."GROUPASSIGNEDTO"
FROM "ARADMIN"."MAIN_TICKET" "Main_Ticket"
WHERE "Main_Ticket"."OUTAGESEVERITY"<=2
AND NOT
(
"Main_Ticket"."GROUPASSIGNEDTO" LIKE 'BBSG%'
OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'CIA%'
OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'DESIGNED%'
OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'IHD%'
OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NABB%'
OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NCC%'
OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NFC%'
)
I'm assuming to put the bitmap index on "OUTAGESEVERITY".
Not sure about GROUPASSIGNEDTO, because it uses the LIKE function and may not use the BM index if it had one.
In this case, would a FBI be better?
Edited by: 974632 on Feb 27, 2013 6:29 AM