has to do with leading edge compression of the branch data. it only has to know where the leading edge of the key varies. If you put into the table
'1 '
'2 '
'3 '
vs
' 1'
' 2'
' 3'
you'll see big time differences:
ops$tkyte@ORA9IR2> create table t ( id1 char(1600), id2 char(1600) );
Table created.
ops$tkyte@ORA9IR2> insert /*+ append */ into t select rownum, rpad( ' ', 1600-length(rownum), ' ' ) || rownum
2 from all_objects where rownum <= 7955;
7955 rows created.
ops$tkyte@ORA9IR2> create index t_idx1 on t(id1);
Index created.
ops$tkyte@ORA9IR2> create index t_idx2 on t(id2);
Index created.
ops$tkyte@ORA9IR2> analyze index t_idx1 validate structure;
Index analyzed.
ops$tkyte@ORA9IR2> create table idx_stats as select * from index_stats;
Table created.
ops$tkyte@ORA9IR2> analyze index t_idx2 validate structure;
Index analyzed.
ops$tkyte@ORA9IR2> insert into idx_stats select * from index_stats;
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function is_number( p_str in varchar2 ) return number
2 as
3 l_number number;
4 begin
5 l_number := p_str;
6 return 1;
7 exception
8 when others then return 0;
9 end;
10 /
Function created.
ops$tkyte@ORA9IR2> column cname format a25
ops$tkyte@ORA9IR2> column t1 format a20
ops$tkyte@ORA9IR2> column t2 format a20
ops$tkyte@ORA9IR2> select a.cname,
2 decode( is_number(a.val),0,a.val,round(a.val,2)) t1,
3 decode( is_number(b.val),0,b.val,round(b.val,2)) t2,
4 case when is_number(a.val) = 1 and is_number(b.val) = 1
5 then to_char( decode(a.val,'0',null,round(b.val/a.val*100,2) ), '9,999.00' )
6 end pct
7 from table( cols_as_rows( 'select *
8 from idx_stats
9 where name = ''T_IDX1'' ' ) ) a,
10 table( cols_as_rows( 'select *
11 from idx_stats
12 where name = ''T_IDX2'' ' ) ) b
13 where a.cname = b.cname
14 /
CNAME T1 T2 PCT
------------------------- -------------------- -------------------- ---------
BLKS_GETS_PER_ACCESS 3 5 166.67
BLOCKS 896 1024 114.29
BR_BLKS 1 90 9,000.00
BR_BLK_LEN 16220 16220 100.00
BR_ROWS 883 883 100.00
BR_ROWS_LEN 10478 1420648 #########
BTREE_SPACE 14326412 15769992 110.08
DEL_LF_ROWS 0 0
DEL_LF_ROWS_LEN 0 0
DISTINCT_KEYS 7955 7955 100.00
HEIGHT 2 4 200.00
LF_BLKS 884 884 100.00
LF_BLK_LEN 16188 16188 100.00
LF_ROWS 7955 7955 100.00
LF_ROWS_LEN 12831415 12831415 100.00
MOST_REPEATED_KEY 1 1 100.00
NAME T_IDX1 T_IDX2
OPT_CMPR_COUNT 0 1
OPT_CMPR_PCTSAVE 0 8
PARTITION_NAME
PCT_USED 90 91 101.11
PRE_ROWS 0 0
PRE_ROWS_LEN 0 0
ROWS_PER_KEY 1 1 100.00
USED_SPACE 12841893 14252063 110.98
25 rows selected.
ops$tkyte@ORA9IR2> set pause off
<b>
Neat Stuff</b>
ops$tkyte@ORA9IR2> create or replace type myScalarType as object
2 ( rnum number, cname varchar2(30), val varchar2(4000) )
3 /
Type created.
ops$tkyte@ORA9IR2> create or replace type myTableType as table of myScalarType
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace
2 function cols_as_rows( p_query in varchar2 ) return myTableType
3 -- this function is designed to be installed ONCE per database, and
4 -- it is nice to have ROLES active for the dynamic sql, hence the
5 -- AUTHID CURRENT_USER
6 authid current_user
7 -- this function is a pipelined function -- meaning, it'll send
8 -- rows back to the client before getting the last row itself
9 -- in 8i, we cannot do this
10 PIPELINED
11 as
12 l_theCursor integer default dbms_sql.open_cursor;
13 l_columnValue varchar2(4000);
14 l_status integer;
15 l_colCnt number default 0;
16 l_descTbl dbms_sql.desc_tab;
17 l_rnum number := 1;
18 begin
19 -- parse, describe and define the query. Note, unlike print_table
20 -- i am not altering the session in this routine. the
21 -- caller would use TO_CHAR() on dates to format and if they
22 -- want, they would set cursor_sharing. This routine would
23 -- be called rather infrequently, I did not see the need
24 -- to set cursor sharing therefore.
25 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
26 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
27 for i in 1 .. l_colCnt loop
28 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
29 end loop;
30
31 -- Now, execute the query and fetch the rows. Iterate over
32 -- the columns and "pipe" each column out as a separate row
33 -- in the loop. increment the row counter after each
34 -- dbms_sql row
35 l_status := dbms_sql.execute(l_theCursor);
36 while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
37 loop
38 for i in 1 .. l_colCnt
39 loop
40 dbms_sql.column_value( l_theCursor, i, l_columnValue );
41 pipe row
42 (myScalarType( l_rnum, l_descTbl(i).col_name, l_columnValue ));
43 end loop;
44 l_rnum := l_rnum+1;
45 end loop;
46
47 -- clean up and return...
48 dbms_sql.close_cursor(l_theCursor);
49 return;
50 end cols_as_rows;
51 /
Function created.