Database Software

Hi friends,

performing with Oracle Text,
Context indexes in table 'fotos' for 'pie' and 'autor' fields,
Oracle indexes in fcreat and revisado fields.

As I'm carrying out this query:

1 select count(*) from fotos where
2 (contains (pie, 'xxxxx', 0) > 0) and
3 (fcreat between '1/1/2009' and '1/1/2010') and
4 (contains (autor, 'xxxxx', 1) > 0) and
5* (revisado <> '30/12/1899')

I can notice differences between execution plan in Oracle 9i (former database) and new Oracle 11g. These are the results:

ORACLE 11g:

13:58:10 SQL> r
1 select count(*) from fotos where
2 (contains (pie, 'xxxxx', 0) > 0) and
3 (fcreat between '1/1/2009' and '1/1/2010') and
4 (contains (autor, 'xxxxx', 1) > 0) and
5* (revisado <> '30/12/1899')

COUNT(*)
----------
2181

Transcurrido: *00:00:29.05*

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1313 Card=1

Bytes=
428)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FOTOS' (TABLE)

(Cost=1
313 Card=105 Bytes=44940)

3 2 DOMAIN INDEX OF 'FOTOS_AUTOR' (INDEX (DOMAIN))

(Cost=1
313 Card=110135)





Statistics
----------------------------------------------------------
289 recursive calls
0 db block gets
36557 consistent gets
10586 physical reads
1028 redo size
431 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed



ORACLE 9i:

14:02:09 SQL> r
1 select count(*) from fotos where
2 (contains (pie, 'xxxxx', 0) > 0) and
3 (fcreat between '1/1/2009' and '1/1/2010') and
4 (contains (autor, 'xxxxx', 1) > 0) and
5* (revisado <> '30/12/1899')

COUNT(*)
----------
2181

Transcurrido: *00:00:16.00*

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2545 Card=1

Bytes=42
3)

1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 BITMAP CONVERSION (COUNT)
4 3 BITMAP AND
5 4 BITMAP MINUS
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 INDEX (RANGE SCAN) OF 'IDX_FOTOS_FCREAT'

(NO
N-UNIQUE) (Cost=5 Card=773)

9 5 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'IDX_FOTOS_REV'

(NON-UNI
QUE) (Cost=5 Card=773)

11 4 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 DOMAIN INDEX OF 'FOTOS_AUTOR' (Cost=235

Card=7
73)

14 4 BITMAP CONVERSION (FROM ROWIDS)
15 14 SORT (ORDER BY)
16 15 DOMAIN INDEX OF 'FOTOS_PIE' (Cost=2111

Card=77
3)





Statistics
----------------------------------------------------------
21784 recursive calls
48 db block gets
137870 consistent gets
8572 physical reads
0 redo size
393 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
2 sorts (disk)
1 rows processed





Oracle 9i could use the four indexes (Context: FOTOS_AUTOR, FOTOS_PIE; Oracle: IDX_FOTOS_FCREAT, IDX_FOTOS_REV) and I was getting the results in a shorter time. Oracle 11g only uses Context FOTOS_AUTOR and the results take a longer time (having Oracle 11g server quite more resources than Oracle 9i server).

Is all this possible owing to Optimizer (choose vs all_rows) type?
How could I get the use of all involved indexes as in Oracle 9i?

Thanks for your attention.

Regards,

Angel.