Parallel = the holy grail of speed! Sometimes, but in this case it does.
Why on Earth does IQ only allow you to create indexes with parallel option? Ask Sybase!
BEGIN PARALLEL
CREATE HG index idx_1_HG ON <table> (column_1);
CREATE LF index idx_2_LF ON <table> (column_2);
CREATE HG index idx_3_HG ON <table> (column_3);
...... on and on
END PARALLEL
I have a 300 million row table joined to 1.2 million row table and have some records I want to delete and was wondering why it’s taking longer than it should . How can I coax some more speed out of my deletes?
Auh, IQ Optimization options fills this need. Specifying predicates on columns that have HG indexes greatly improves costing. In order for the HG costing to pick an algorithm other than large delete, it must be able to determine the number of distinct values (groups) affected by deletions. 1st their has to be a HG index on the column in the predicate which is usually the case. IQ chooses three algorithms to process deletes.
set option HG_DELETE_METHOD = 1; <------------'Small' delete' for a few distinct rows or 1 row
set option HG_DELETE_METHOD = 2; <------------ 'Large delete' 100000+ rows
set option HG_DELETE_METHOD = 3; <------------' Mid delete' 100 to 10000 rows