| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Composite index and data distribution
Hi,
I've observed a behaviour that I cannot explain by myself and I would be glad for help. Here it goes (10gXE but tested on 9.2.0.5 EE too):
SIMPLE TABLE WITH TWO COLUMNS: SQL> create table foobar(foo number not null, bar number not null);
Table created.
DATA DISTRIBUTION: IN 1% OF THE ROWS FOO=1, IN 99% FOO=99:
SQL> begin
2 for i in 1..1000000 loop
3 insert into foobar(foo, bar) values (case when i <= 10000 then 1 else
99 end, i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
COMPOSITE INDEX (BEST I CAN THINK OF FOR THE QUERY TO BE EXECUTED): SQL> create index foobar_idx on foobar(foo, bar);
Index created.
DETAILED STATISTICS (SO IT'S NOT STATISTICS-RELATED ISSUE): SQL> exec dbms_stats.gather_table_stats(user, 'FOOBAR', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto');
PL/SQL procedure successfully completed.
JUST TO BE SURE - DATA DISTRIBUTION: SQL> select foo, count(*) from foobar group by foo;
FOO COUNT(*)
---------- ----------
1 10000
99 990000
TOOLS:
SQL> set timing on SQL> set autotrace traceonly SQL> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.08
AND HERE COMES THE QUERY FOR FOO=1 (1% OF THE ROWS): SQL> select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn 2 from foobar where foo=1 order by bar) where rn <= 25;
25 rows selected.
Elapsed: 00:00:00.03
Execution Plan
| 0 | SELECT STATEMENT | | 25 | 650 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 25 | 650 | 3 (0)| 00:00:01 | | 2 | COUNT | | | | | | |* 3 | INDEX RANGE SCAN| FOOBAR_IDX | 25 | 175 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter("RN"<=25)
3 - access("FOO"=1)
Statistics
1 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
811 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
AND HERE IS THE SAME QUERY WITH FOO=99 (99% OF THE ROWS): SQL> select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn 2 from foobar where foo=99 order by bar) where rn <= 25;
25 rows selected.
Elapsed: 00:00:00.78
Execution Plan
| 0 | SELECT STATEMENT | | 25 | 650 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 25 | 650 | 3 (0)| 00:00:01 | | 2 | COUNT | | | | | | |* 3 | INDEX RANGE SCAN| FOOBAR_IDX | 25 | 175 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter("RN"<=25)
3 - access("FOO"=99)
Statistics
1 recursive calls
0 db block gets
2624 consistent gets
0 physical reads
0 redo size
811 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
In the first case there were 30 consistent gets and in the second - 2624! Why?! Plan is the same. The index contains exactly the answer to the query (both columns, sorted as requested), so I would expect it to just grab the first 25 rows from the index and return them. I would ignore a few blocks difference but it's not the case - Oracle is obviously doing something different with those two queries.
Here is the output from tkprof:
select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn from foobar where foo=1 order by bar) where rn <= 25
call count cpu elapsed disk query current rows ------- ------ ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 30 0 25 ------- ------ ------ -------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 30 0 25
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 36
Rows Row Source Operation
------- ---------------------------------------------------
25 VIEW (cr=30 pr=0 pw=0 time=70 us)
10000 COUNT (cr=30 pr=0 pw=0 time=60049 us)
10000 INDEX RANGE SCAN FOOBAR_IDX (cr=30 pr=0 pw=0 time=30038 us
(object id 14207)
select /*+ FIRST_ROWS(25) */ * from (select foo, rownum rn from foobar where foo=99 order by bar) where rn <= 25
call count cpu elapsed disk query current rows ------- ------ ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.75 0.75 0 2624 0 25 ------- ------ ------ -------- ---------- ---------- ---------- ---------- total 5 0.75 0.75 0 2624 0 25
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 36
Rows Row Source Operation
------- ---------------------------------------------------
25 VIEW (cr=2624 pr=0 pw=0 time=73 us)
990000 COUNT (cr=2624 pr=0 pw=0 time=5940050 us)
990000 INDEX RANGE SCAN FOOBAR_IDX (cr=2624 pr=0 pw=0 time=1980043 us
(object id 14207)
Seems like Oracle cannot optimize the query and really reads all those rows instead of stopping after the first 25. Is it true or am I missing something? I can't believe that with all those tricks that CBO is capable of, it can't do such an obvious one. Any suggestions?
Thanks!
-- Michal KuratczykReceived on Fri Aug 11 2006 - 04:27:51 CDT
 |
 |