Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.SarmasReceived on Sat May 30 1998 - 05:35:32 CDT