HISTOGRAMS - 10.2.0.4
edited Jul 18, 2011 9:56AM in Database ORA-600 / ORA-7445 Analysis , Diagnostic Repository (ADR) & Packaging (MOSC) 1 commentAnswered
Hi,
Lets assume the following simple query. Its a 10.2.0.4 database
Select state,flag from master_table where flag=:1 and state=:2;
The query is using bind variables
master_table has 500,000 records
there is an index on column STATE
there is no index on column FLAG
FLAG column has highly skewed values (460,000 = 'N' and 36,000 = 'Y', 4,000 = 'U')
Couple of questions
1. If the query is executed first time with value of :1='N', the optimizer will generate an execution plan with FULL TABLE SCAN
as the query is supposed to go after 95% of data. Correct?
2. Now, if the same query is executed second time but with a passed on value of :1='U' , oracle still will execute the query
Lets assume the following simple query. Its a 10.2.0.4 database
Select state,flag from master_table where flag=:1 and state=:2;
The query is using bind variables
master_table has 500,000 records
there is an index on column STATE
there is no index on column FLAG
FLAG column has highly skewed values (460,000 = 'N' and 36,000 = 'Y', 4,000 = 'U')
Couple of questions
1. If the query is executed first time with value of :1='N', the optimizer will generate an execution plan with FULL TABLE SCAN
as the query is supposed to go after 95% of data. Correct?
2. Now, if the same query is executed second time but with a passed on value of :1='U' , oracle still will execute the query
0