Performance on a table with 200 Million Records

Database
Enthusiast

Performance on a table with 200 Million Records

Hello Experts,

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.

10 REPLIES
Supporter

Re: Performance on a table with 200 Million Records

did you consider to use a PI of (y,z)?

Can you run

select top 100 hashrow(y,z), count(*)
from your_tab
group by 1
order by 2 desc

and share the result?

Enthusiast

Re: Performance on a table with 200 Million Records

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?

Supporter

Re: Performance on a table with 200 Million Records

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.

Enthusiast

Re: Performance on a table with 200 Million Records

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?

Supporter

Re: Performance on a table with 200 Million Records

yes - but what do you want with 200 mio rows in a select *?

Supporter

Re: Performance on a table with 200 Million Records

Did you try

select top 200 * from table?

Enthusiast

Re: Performance on a table with 200 Million Records

Yes, even that takes long time.  5-10 min.

Supporter

Re: Performance on a table with 200 Million Records

Do you do a plain

Select top 10 *

from table;

?

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;

Enthusiast

Re: Performance on a table with 200 Million Records

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?