Wny do you say that a FTS of table Z is bad? How many blocks has the table
allocated (user_tables.blocks)? How many blocks will the query be returning?
Is an index read really better? Use hints to test the different access paths
and prove it.
FTS are not always bad. I have examples where a FTS is
col_1 and col_2 are primary key columns in all 3 tables.
There is no implicit data type conversion.
col_1 is varchar2 and col_2 is varchar2 in all 3 tables.
col width is also same in all 3 tables.
-Original Message-
Sent: Monday, January 06, 2003 11:52 PM
To: Gurnani, Manoj
If an
[EMAIL PROTECTED] wrote:
Hi All,
I've 3 tables table x,y,z
pk of x is (col_1,col_2),pk of y is (col_1,col_2,col_4,col_5),pk of z is
(col_1,col_2)
where clause is :
where x.col_1 = y.col_1
and x.col_2 = y.col_2
and x.col_1 = z.col_1
and x.col_2 = z.col_2
and y.col_1 =
What is the significance of parameter OPTIMIZER_INDEX_COST_ADJ ?
When I tried to change the value the explain plan has changed .
alter session set OPTIMIZER_INDEX_COST_ADJ = 10
table access for Z was by index rowid
alter session set OPTIMIZER_INDEX_COST_ADJ = 100
table access for Z was full
]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: Prevent FTS
Please check Jonathan Lewis book 'Practice Oracle 8i'. This book provides lot more
details about these two optimizer related parameters that could significantly affect
FTS situations.
optimizer_index_caching
optimizer_index_cost_adj
The general 'harmless' guidelines could be setting 10 to
manoj.gurnani,
hI, It seems that you do not need this clause:
and y.col_1 = z.col_1
and y.col_2 = z.col_2
right?
Is your table z rather small? If your table have different size or your
optimizer mode is different, oracle will show different plan. It is
