Performance degrades with big blocksize tablespace
Hi bangalibor here,
I have a huge table which sizes near about 50GB and have five columns and no primary key exists.Because the performance is going down( it should as most of the queries are going for full table scan), we need to tune it.
The considerations are:
a. We will not go into partition table.(why?..dont ask plz)
b. There is no joins in the query with other table so, that i can tune.Most of the queries are simple like
select * from ... where a1=...;
I have tried with index, but the cost of using index is high, so, oracle surpresses ......
c. So, only way out for me , was to make table scan faster..
That was my point of working.
The test machine says, I took half of the data in a test machine, where i had two tables with different tablespace blocksize to compare.
I agree a 50 Gig in size table is a large one but I wouldn't say "huge".
In short, I think poster is wasting fine tune effort in the wrong direction.
Solution is pretty simple, fix the indexing strategy -there are just two reasons why Oracle wouldn't like the indexes: a) indexes have no o wrong statistics or, b) indexes are not in line with queries predicates rendering queries non-sargable.
I also wouldn't take partitioning out of the table.
Last but not least I wish query example "select * from ... where a1=...;" is not a real query -no "select *" query should be run against a production environment.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.