Re: slow DSS large dataset query, help

In article <37D7E2D8.1A0D86BE@bloomberg.net>, kagel@bloomberg.net wrote: > I will suggest adding an index on Table_A as: > > create index new_one on Table_A( c1, create_timestamp );> > which will allow filtering at the index level and save some I/Os on the > largest table. Don't forget to update statistics low on (c1, create_timestamp) > and HIGH on create_timestamp. Assuming you already have > good distributions this is all you need to add to the stats. If your > distributions are not up-to-date or not at least as good as the > recommended UPDATE STATS suite then run the full suite on the table > (manually or using dostats.ec or one of the scripts that do this for you). > > Art S. Kagel > Art, 1, Eventhough there are highly duplicated values (6 different values for 30000000 rows), I still create index on create_timestamp? 2, How to find out if I have the "good distributions"? If not, what UPDATE STATS I should put? MEDIUM for table? HIGH for key? LOW for composite index? SJ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

sjsyau@my-deja.com wrote: > > In article <37D7E2D8.1A0D86BE@bloomberg.net>, > kagel@bloomberg.net wrote: > > I will suggest adding an index on Table_A as: > > > > create index new_one on Table_A( c1, create_timestamp );> > > > which will allow filtering at the index level and save some I/Os on > the > > largest table. Don't forget to update statistics low on (c1, > create_timestamp) > > and HIGH on create_timestamp. Assuming you already have > > good distributions this is all you need to add to the stats. If your > > distributions are not up-to-date or not at least as good as the > > recommended UPDATE STATS suite then run the full suite on the table > > (manually or using dostats.ec or one of the scripts that do this for > you). > > > > Art S. Kagel > > > > Art, > > 1, Eventhough there are highly duplicated values (6 different > values for 30000000 rows), I still create index on create_timestamp? By prepending the key (c1) to the create_timestamp you create a non-duplicate index (since this composite key is really unique you can make the index a UNIQUE index and the engine will generate a more efficient index structure with no inversion list). > 2, How to find out if I have the "good distributions"? One way would be to SELECT COUNT(*)....WHERE c1 = <some active value> and compare the count to the estimate that the distribution (dbschema...-hd <tablename>) holds. Choose one overflow key (from the dbschema distribution report) and one bucketed key (estimate this count as bucket_count/#keys_in_bucket). If the counts are close or at least the ratios of the counts of the two keys are close then the stats are good. If the engine is doing seqential scan where there are indexes available you may want exact counts even when the count ratios are OK though since the optimizer makes that decision based on estimating the number of data and index pages it may have to read with and without the index. > If not, what UPDATE STATS I should put? > MEDIUM for table? > HIGH for key? Actually: HIGH for lead column of each index key and first different key column if multiple indexes begin with the same column subset. > LOW for composite index? That's close. See the recommendations in the 7.3x versions of the Performance Guide or get my dostats.ec utility or one of the scripts at the IIUG Repository which implements those recommendations. Art S. Kagel