CBO stupid !!!

        Hi,

	We use Oracle 7.2.2.4 on SCO UNIX 3.2.4.2. I must say we are very
	happy Oracle users. Initially we started with optimizer mode = RULE
	so we could migrate smoothly from Oracle 6.0.37. Then we switched
	on the COST mode. Though generaly CBO works ok, at times it
	makes stupid decisions!

	Consider this example:

	Very large table XX with about 500 000 rows. There are 5 indexes,
	2 of which are 

		IX1 on column A, B, C all VARCHAR2 and there is
			a good selectivity on column A
		IX2 on columns D, E where D is a date and
			the selectivity on D is twice better that of A

	now consider this select

		Select ...
		from XX
		where A = '...'
		  and D >= ... (or D between ... and ...)

	CBO uses the index IX1 and is very slow ....

	we cannot use hints because this select is called from FORMS 3.0

	so we rephrase the Where part to

		Where A || '' = '...'
		     and D >= ... (or D between ... and ...)

	CBO chooses FULL TABLE SCAN ! and now we wait an eternity !!

	in now way CBO uses IX2!!

	Don' t tell me about efficiency calculations from CBO ... 

	now we do nother TRICK!! watch this

		Select ...
		From XX, YY (YY is a very small table that has only values
				for columns D and E)
		Where YY.D >= ...
		    and YY.D = XX.D and YY.E = XX.E
		    and XX.A || '' = ....

	what do you think CBO does !!!

	it uses the index on YY correctly and for the join chooses
	SORT-MERGE join with full table scan of XX !!!

	somehow I think CBO prefers indexes by order of creation,
	IX1 was created before IX2,

	IX2 really exists because a select only by column D
	does use the index ( which also makes the claim that
	CBO has calculated the costs !! wrong) !!!


	Does anyone know what is happening!!

	Is Oracle listening ???

	Thanks, in advance

	E.I.Sarmas