Hi everyone,
My problem is, that i want to test the index generator.
For that I’m using an sql with an bad full table scan on a table with 400000 rows.
I increased the level of the index generator to 5 and it still doesn’t give any new index sets…
Can anyone give me a hint why???
thx,
Cedric
You can try our new module Optimize Indexes and see if you can get new indexes for your SQL.
but it says that i should use the sql rewrite function when tuning only one statement plus the optimize indexes module always freezes when i try to optimize the single statement.
Optimize Index use different approach to find indexes, sometimes it can find indexes that our single index generator cannot find.
SQL cannot use indexes may due to at least two reasons, the syntax itself forbid an index usage or Oracle SQL optimizer think that not to use the index is the lowest cost choice. If you really want to try that table with 400000 rows only, you can create a physical index on that table base on your SQL access columns, and then paste your SQL to our SQL Rewrite to look for a SQL with that index used and check whether this alternative is faster. Have you try our rewrite on your SQL ?
Richard
OK, so now I’m sure that there must be a problem.
Just for testing the tool I chose a random, not indexed column of our biggest table with 37 million rows and selected one value.
The execution plan stated a full table scan and so i tested both the index generation and the optimize index, but nothing.
I also checked the Help-> Database Privileges, to be sure that this is not the problem and it seems to be not.
Is it possible that there is some problem because of the trial version?
thanks,
cedric
Since you have not list your SQL, please make sure you have a SQL like this, the WHERE clause is specified.
select emp_telephone
from employee
where emp_telephone = ‘123456’
where repertoire_code = **222
**
from bds_sb
Thats my SQL:
select *
I would like to know whether the “User Index” is still work for your SQL ? You can define a virtual index on repertoire_code to check whether the virtual index function is still working.
Are there any SQL you in your database can generate index recommendation from our product ? or it is no index recommendation for any SQL at all ?
ORA-01031: insufficient privileges …
what is kind of funny, because I have the privileges which I should have regarding your SQL Optimizer Help.
Ok, now I got it. When I’m trying to create an virtual index i get the Message:
Ok, Hope our index advisor can improve your SQL performance
Sometimes, underlying data prevent any proper indexing of table. This happened on bigger tables almost as correct pattern.
So small advice.
Create new table like:
create new table as select * from old_table ordered by col1, col2, col3
where col1, col2, col3 are columns on which you filtered the most.
Then create index on those columns (or at least some of them from the beginning of order by) and you will see that index is used.
If this not helps … you are dealing with huge data and partitioning should be the only answer.