Need small guidance here to run my queries little faster. My table 200 Million records with 20 fields in it with x,y,z fields as Unique Primary Index. We collect statistics every day on this table COLLECT STATS ON Table INDEX (x,y,z); and still the performance on this table is very bad. The queries which we use on this table includes a where clause on y and z. So does collecting statistics only on y and z helps or is it better to create a secondary index on y and z or is there anything else which we can do on it to improve the performance? Any suggestion will be helpful.
Yes, we have parttion on the table and it is on a date field.
did you consider to use a PI of (y,z)?
Can you run
select top 100 hashrow(y,z), count(*)
group by 1
order by 2 desc
and share the result?
y,z are not unique and i want to make sure the table is unique. So i have column x which makes it Unique. Does it help if we do a secondary index on it? or does it help doing statistics only on those two fields?
not unique is not an issue as long as skeq is not too high.
Don't forget a PI has two purposes! 1. Access and 2. distribution.
if skeq is not too high and you need a unique constrain you could also consider to use y,z as PI and define a USI on x,y,z
NUSI on y,z will mainly help if you have selective conditions on y,z. If you join the whole table on y,z to a different table the NUSI will not used.
Currently the skew factor is only 0.12 which i think is not bad. The problem we have is, when we run select * from mytable, then it takes long time to return the resultset. I think select * of any table should return the resultset very quickly. How does select * from Mytable works? does it 1st store the data in spool and then returns the result set after reading everything?
Do you do a plain
Select top 10 *
Or do you apply filter, joins?
If it is a plain select * from table
you might face TASM delays or the table is not a table but a view.
Show select * from table;
What would be the difference between selecting data from a view and selecting directly from it?
BTW..i am just doing SELECT * FROM MyTable
What are TASM delays?