.
.
.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
24 February, 2011
20 February, 2011
Index Block Splits --- with REVERSE KEY Index
An earlier post "Index Block Splits : 90-10" demonstrated 90-10 block splits for an index on a monotonously increasing sequence -- as is defined in a Unique Index.
A question on what would be the case if the Index were a REVERSE KEY index was raised. (I will not cover "why REVERSE KEY" here). When I thought about it, I realised that it would cause 50-50 block splits. The initial entries (as the table begins) would of course, be in a single block. But as the index grew, values would be switching between the two "ends" of the index and would also be extending the stored minimum and maximum values (that are reversed from the user-entered values).
So, here's a simple proof of Index Block Splits for a an index on a monotonously increasing sequence -- the splits are 50-50 and the index leaf blocks are not as tightly packed as in a "normal" index :
NOTE 1 : I had initially published this post with the table name as "DEMO_IBS_90_10_RK" but have now rerun the scripts with the more meaningful name "DEMO_IBS_RK".
NOTE 2 : Both these Test Runs (and the test runs for the previous blog posts on Index Block Splits) are on 10.2.0.4, 64bit Linux, ASSM Tablespaces @8KB block size.
There are 2 Test Runs. The first is with the same index definition as in the previous blog post. The second test run is with the index on only the numeric column EMPLOYEE_ID.
TEST RUN 1 with a multi-column Unique Index.
I increment the key by 100,000 and 200,000 for the next two rounds of inserts and create a "gap". However, in my opinion, this does not significantly alter the results. Even if I did a "gapless" insert (as in the first insert), the index block splits would be 50-50.
TEST RUN 2 with a single, numeric column Unique Index.
Since the index is on a single NUMBER column, it is much smaller than the previous test run. The first set of 58thousand values creates only 128 leaf blocks and the next two sets of 1000 values each are able to find space within the existing leaf blocks because they are not "right-most" values occurring too frequently. So, the next two round do not create any block splits (I would get block splits if I insert more rows). This index is better packed as well at 76% after three rounds.
.
.
.
A question on what would be the case if the Index were a REVERSE KEY index was raised. (I will not cover "why REVERSE KEY" here). When I thought about it, I realised that it would cause 50-50 block splits. The initial entries (as the table begins) would of course, be in a single block. But as the index grew, values would be switching between the two "ends" of the index and would also be extending the stored minimum and maximum values (that are reversed from the user-entered values).
So, here's a simple proof of Index Block Splits for a an index on a monotonously increasing sequence -- the splits are 50-50 and the index leaf blocks are not as tightly packed as in a "normal" index :
NOTE 1 : I had initially published this post with the table name as "DEMO_IBS_90_10_RK" but have now rerun the scripts with the more meaningful name "DEMO_IBS_RK".
NOTE 2 : Both these Test Runs (and the test runs for the previous blog posts on Index Block Splits) are on 10.2.0.4, 64bit Linux, ASSM Tablespaces @8KB block size.
There are 2 Test Runs. The first is with the same index definition as in the previous blog post. The second test run is with the index on only the numeric column EMPLOYEE_ID.
TEST RUN 1 with a multi-column Unique Index.
SQL> drop table demo_ibs_rk purge;
Table dropped.
SQL>
SQL> -- REM create the demo table
SQL> create table demo_ibs_rk (
2 employee_id number not null,
3 country_name varchar2(10) not null,
4 dept_name varchar2(18) not null,
5 employee_name varchar2(128) not null,
6 join_date date)
7 /
Table created.
SQL>
SQL> -- create the index with a PCTFREE of 1 to pack it tightly
SQL> -- BUT CREATE IT AS A REVERSE KEY INDEX
SQL> create unique index demo_ibs_rk_u1 on
2 demo_ibs_rk (employee_id,country_name,employee_name)
3 reverse pctfree 1;
Index created.
SQL>
SQL> drop table source_table;
Table dropped.
SQL> create table source_table
2 as select * from dba_objects
3 where 1=2;
Table created.
SQL> insert /*+ APPEND */ into source_table
2 select * from dba_objects
3 where object_id is not null;
50833 rows created.
SQL> commit;
Commit complete.
SQL> select max(object_id) from source_table;
MAX(OBJECT_ID)
--------------
58316
SQL>
SQL>
SQL> -- REM create a new session and run an insert
SQL> -- then check the statistics for the insert
SQL> connect hemant/hemant
Connected.
SQL> insert into demo_ibs_rk
2 select object_id, substr(owner,1,10),
3 substr(object_type,1,18),
4 rpad(object_name,20,dbms_random.string('X',3)),
5 created
6 from source_table
7 where object_id is not null
8 order by object_id
9 /
50833 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 386
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
50833 50833 1 387 131.351421
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
50833 387 1 0 68
SQL>
SQL> REM #################
SQL> REM OBSERVATION !
SQL> REM The first set of block creations are all from Block Splits.
SQL> REM And all of these are now 50-50 Splits !
SQL> REM #################
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now simulate a Row-By-Row Insert that would happen
SQL> -- REM for creating new Employees
SQL> connect hemant/hemant
Connected.
SQL>
SQL> declare
2 i number;
3
4 begin
5 for i in 1..1000
6 loop
7 insert into demo_ibs_rk
8 select object_id+100000+i,
9 substr(owner,1,10),
10 substr(object_type,1,18),
11 rpad(object_name,20,dbms_random.string('X',3)),
12 created+vsize(object_name)
13 from source_table
14 where object_id is not null
15 and object_id = 1000+i;
16 commit;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 17
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
51833 51833 1 404 128.299505
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
51833 404 1 0 67
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now run a bulk insert again !
SQL> connect hemant/hemant
Connected.
SQL>
SQL> insert into demo_ibs_rk
2 select object_id+200000,
3 substr(owner,1,10),
4 substr(object_type,1,18),
5 rpad(object_name,20,dbms_random.string('X',3)),
6 created+vsize(object_name)
7 from source_table
8 where object_id is not null
9 and object_id between 1000 and 2000
10 /
1001 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 25
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
52834 52834 1 429 123.156177
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
52834 429 1 0 64
SQL>
I increment the key by 100,000 and 200,000 for the next two rounds of inserts and create a "gap". However, in my opinion, this does not significantly alter the results. Even if I did a "gapless" insert (as in the first insert), the index block splits would be 50-50.
TEST RUN 2 with a single, numeric column Unique Index.
SQL> drop table demo_ibs_rk purge;
Table dropped.
SQL>
SQL> -- REM create the demo table
SQL> create table demo_ibs_rk (
2 employee_id number not null,
3 country_name varchar2(10) not null,
4 dept_name varchar2(18) not null,
5 employee_name varchar2(128) not null,
6 join_date date)
7 /
Table created.
SQL>
SQL> -- create the index with a PCTFREE of 1 to pack it tightly
SQL> -- BUT CREATE IT AS A REVERSE KEY INDEX
SQL> create unique index demo_ibs_rk_u1 on
2 demo_ibs_rk (employee_id)
3 reverse pctfree 1;
Index created.
SQL>
SQL> drop table source_table;
Table dropped.
SQL> create table source_table
2 as select * from dba_objects
3 where 1=2;
Table created.
SQL> insert /*+ APPEND */ into source_table
2 select * from dba_objects
3 where object_id is not null;
50833 rows created.
SQL> commit;
Commit complete.
SQL> select max(object_id) from source_table;
MAX(OBJECT_ID)
--------------
58322
SQL>
SQL>
SQL> -- REM create a new session and run an insert
SQL> -- then check the statistics for the insert
SQL> connect hemant/hemant
Connected.
SQL> insert into demo_ibs_rk
2 select object_id, substr(owner,1,10),
3 substr(object_type,1,18),
4 rpad(object_name,20,dbms_random.string('X',3)),
5 created
6 from source_table
7 where object_id is not null
8 order by object_id
9 /
50833 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 127
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
50833 50833 1 128 397.132813
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
50833 128 1 0 74
SQL>
SQL> REM #################
SQL> REM OBSERVATION !
SQL> REM The first set of block creations are all from Block Splits.
SQL> REM And all of these are now 50-50 Splits !
SQL> REM #################
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now simulate a Row-By-Row Insert that would happen
SQL> -- REM for creating new Employees
SQL> connect hemant/hemant
Connected.
SQL>
SQL> declare
2 i number;
3
4 begin
5 for i in 1..1000
6 loop
7 insert into demo_ibs_rk
8 select object_id+100000+i,
9 substr(owner,1,10),
10 substr(object_type,1,18),
11 rpad(object_name,20,dbms_random.string('X',3)),
12 created+vsize(object_name)
13 from source_table
14 where object_id is not null
15 and object_id = 1000+i;
16 commit;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 0
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
51833 51833 1 128 404.945313
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
51833 128 1 0 75
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now run a bulk insert again !
SQL> connect hemant/hemant
Connected.
SQL>
SQL> insert into demo_ibs_rk
2 select object_id+200000,
3 substr(owner,1,10),
4 substr(object_type,1,18),
5 rpad(object_name,20,dbms_random.string('X',3)),
6 created+vsize(object_name)
7 from source_table
8 where object_id is not null
9 and object_id between 1000 and 2000
10 /
1001 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 0
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
52834 52834 1 128 412.765625
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
52834 128 1 0 76
SQL>
Since the index is on a single NUMBER column, it is much smaller than the previous test run. The first set of 58thousand values creates only 128 leaf blocks and the next two sets of 1000 values each are able to find space within the existing leaf blocks because they are not "right-most" values occurring too frequently. So, the next two round do not create any block splits (I would get block splits if I insert more rows). This index is better packed as well at 76% after three rounds.
.
.
.
17 February, 2011
Qualifying Column/Object names to set the right scope
Earlier today, I had a curious query that I knew was returning the wrong results.
(Fortunately, I could look at the output and say outright "this is wrong". Had I not been observant enough and not known the underlying data, I would have allowed that "wrong" query to return the wrong results and accepted them as "correct").
More than possible Oracle code and optimization bugs, the biggest reason for "wrong results" is improper referencing of objects. My query today was similar -- and it had been written by me !
Here is an example where two tables have a similar column name yet the wrong column is being referenced in the subquery. The subquery attempts "DEPTID" in the "EMP" table but there is no such column (the column name is really "E_DEPTID"). However, Oracle's name resolution is (successfully) able to resolve the "DEPTID" in that subquery to be that in the "DEPT" table. The query runs successfully but the results are wrong.
What would be good practice ? To properly qualify all column names, particularly when using
a. SubQueries
b. Joins
c. Table Aliases
Similar name resolution issues arise when objects and synonyms, private and public, exist with the same name. Thus, if "EMP" is not found as a table in the current schema, Oracle attempts to look for a private synonym called "EMP". If it can't find a private synonym, it looks for a public synonym.
In a development environment with multiple schemas present, when you are querying "EMP" make sure that you are querying "EMP" as a table in the current schema and not as a synonym to a table in some other schema !
.
.
.
(Fortunately, I could look at the output and say outright "this is wrong". Had I not been observant enough and not known the underlying data, I would have allowed that "wrong" query to return the wrong results and accepted them as "correct").
More than possible Oracle code and optimization bugs, the biggest reason for "wrong results" is improper referencing of objects. My query today was similar -- and it had been written by me !
Here is an example where two tables have a similar column name yet the wrong column is being referenced in the subquery. The subquery attempts "DEPTID" in the "EMP" table but there is no such column (the column name is really "E_DEPTID"). However, Oracle's name resolution is (successfully) able to resolve the "DEPTID" in that subquery to be that in the "DEPT" table. The query runs successfully but the results are wrong.
SQL>
SQL> create table DEPT (deptid number not null primary key, deptname varchar2(30));
Table created.
SQL> create table EMP (empid number not null primary key, e_deptid number, ename varchar2(30));
Table created.
SQL>
SQL> insert into DEPT select rownum,
2 decode(rownum,1,'SALES',2,'ACCOUNTING',3,'MANUFACTURING',0,'NON-EXISTENT')
3 from dual connect by level less_than 4;
3 rows created.
SQL> insert into EMP select rownum, mod(rownum,4)+1, dbms_random.string('X',25)
2 from dual connect by level less_than 41;
40 rows created.
SQL> commit;
Commit complete.
SQL> -- identify all the departments
SQL> select deptid, deptname from DEPT;
DEPTID DEPTNAME
---------- ------------------------------
1 SALES
2 ACCOUNTING
3 MANUFACTURING
SQL> -- now deliberately ensure that there are no employees for the ACCOUNTING department
SQL> delete emp where e_deptid = 2;
10 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> -- this query falsely returns rows !
SQL> -- there is no column called "deptid" in EMP
SQL> -- yet the query succeeds because Oracle resolved deptid to be the column in DEPT !
SQL> select distinct deptname from DEPT
2 where deptid in (select deptid from EMP);
DEPTNAME
------------------------------
ACCOUNTING
MANUFACTURING
SALES
SQL>
SQL> -- this is how the query would be best framed
SQL> -- we can now see that it fails
SQL> select distinct d.deptname from DEPT d
2 where d.deptid in (select e.deptid from EMP e);
where d.deptid in (select e.deptid from EMP e)
*
ERROR at line 2:
ORA-00904: "E"."DEPTID": invalid identifier
SQL>
SQL> -- this is the correct query
SQL> select distinct d.deptname from DEPT d
2 where d.deptid in (select e.e_deptid from EMP e);
DEPTNAME
------------------------------
MANUFACTURING
SALES
SQL>
What would be good practice ? To properly qualify all column names, particularly when using
a. SubQueries
b. Joins
c. Table Aliases
Similar name resolution issues arise when objects and synonyms, private and public, exist with the same name. Thus, if "EMP" is not found as a table in the current schema, Oracle attempts to look for a private synonym called "EMP". If it can't find a private synonym, it looks for a public synonym.
In a development environment with multiple schemas present, when you are querying "EMP" make sure that you are querying "EMP" as a table in the current schema and not as a synonym to a table in some other schema !
.
.
.
14 February, 2011
Cardinality Feedback in 11.2
The (undocumented) CARDINALITY Hint has been available since --10g-- 9i (correction by Timur). This allowed the Developer / DBA to "hint" to Oracle the expected number of rows that will be returned from a table that is part of a query.
UPDATE 20-Feb-11 : As Timur supected, I believe, I am unable to create a consistently reproducible test case to demonstrate the "cardinality feedback used for this statement" comment from DBMS_XPLAN.DISPLAY.
I next run a query on two predicates :
I find that Oracle actually read 13,000 ROWIDs from 36 Blocks of the SALES_DATE_NDX. However, on passing these 13,000 ROWIDs to the table, it read 113 Blocks but returned only 117 rows after filtering for CUST_ID=4. Evidently, only 117 of the 13,000 ROWIDs were relevant -- all of the rest were "throwaway".
Since I need to read only the SALE_DATE and CUST_ID columns, I create an appropriate index. (Let's ignore the possibility of a BitMap index -- maybe this is an OLTP table, maybe the Standard Edition is in use).
Note : Remember that Index statistics are automatically gathered with the CREATE INDEX.
Testing the same query, I expect the Index to be used. But I find something else as well :
Oracle estimated a Cardinality of 129 rows but also tells me that Cardinality Feedback was used.
Note that this SQL is exactly the same as the first one (has the same SQL_ID) but has been re-parsed and created as a new Child Cursor (CURSOR 1) with a new Plan (new PLAN_HASH_VALUE).
I then run a DDL statement to invalidate SQLs :
Retesting the query :
This time, the "cardinality feedback was used for this statement" message does not appear.
11g's Adaptive Cursor Sharing for queries that use Binds uses "feedback" from previous executions to validate if an Execution Plan has been good enough (e.g. if the Execution Plan estimated 100 rows but the actual execution returned 4500 rows, then the plan was not good !) and "decide" if it should "re-peek" the bind and generate a new Execution Plan.
However, Cardinality Feedback may be used by 11g even when binds don't appear.
UPDATE 20-Feb-11 : As Timur supected, I believe, I am unable to create a consistently reproducible test case to demonstrate the "cardinality feedback used for this statement" comment from DBMS_XPLAN.DISPLAY.
Here is a simple demonstration. However, I will not explain the Cardinality Feedback and the new Execution Plan -- I might get some comments that attempt to explain these, I hope !
[Note : I have converted the "<=" and ">=" signs to "less_than_equal_to" and "greater_than_equal_to"].
In an 11.2.0.1 database, I start with a table with 100,000 rows and two single column indexes :
SQL> desc sales
Name Null? Type
----------------------------------------- -------- ----------------------------
SALE_ID NOT NULL NUMBER
SALE_DATE DATE
CUST_ID NUMBER
REMARKS VARCHAR2(50)
SQL> select num_rows from user_tables where table_name = 'SALES';
NUM_ROWS
----------
100000
SQL> select num_rows from user_tables where table_name = 'SALES';
NUM_ROWS
----------
100000
SQL> @$HOME/Scripts/which_indexes
Enter value for table_name: SALES
Enter value for table_owner: HEMANT
Index Ind Type Unique? Column Pos
--------------------------- ------------ --------- ---------------- ----
SALES_DATES_NDX NORMAL NONUNIQUE SALE_DATE 1
SALES_UK NORMAL UNIQUE SALE_ID 1
SQL>
I next run a query on two predicates :
SQL> select /*+ gather_plan_statistics */
2 cust_id, count(*)
3 from sales
4 where sale_date between
5 to_date('01-DEC-10','DD-MON-RR') and to_date('14-DEC-10','DD-MON-RR')
6 and cust_id = 4
7 group by cust_id;
CUST_ID COUNT(*)
---------- ----------
4 117
SQL>
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 65hm33unu3mcm, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ cust_id, count(*) from sales where
sale_date between to_date('01-DEC-10','DD-MON-RR') and
to_date('14-DEC-10','DD-MON-RR') and cust_id = 4 group by cust_id
Plan hash value: 1184718566
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 149 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 1 |00:00:00.04 | 149 |
|* 2 | FILTER | | 1 | | 117 |00:00:00.01 | 149 |
|* 3 | TABLE ACCESS BY INDEX ROWID| SALES | 1 | 129 | 117 |00:00:00.01 | 149 |
|* 4 | INDEX RANGE SCAN | SALES_DATES_NDX | 1 | 13000 | 13000 |00:00:00.59 | 36 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-DEC-10','DD-MON-RR') less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
3 - filter("CUST_ID"=4)
4 - access("SALE_DATE" greater_than_equal_to TO_DATE('01-DEC-10','DD-MON-RR') AND
"SALE_DATE" less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
26 rows selected.
SQL>
I find that Oracle actually read 13,000 ROWIDs from 36 Blocks of the SALES_DATE_NDX. However, on passing these 13,000 ROWIDs to the table, it read 113 Blocks but returned only 117 rows after filtering for CUST_ID=4. Evidently, only 117 of the 13,000 ROWIDs were relevant -- all of the rest were "throwaway".
Since I need to read only the SALE_DATE and CUST_ID columns, I create an appropriate index. (Let's ignore the possibility of a BitMap index -- maybe this is an OLTP table, maybe the Standard Edition is in use).
SQL> create index sales_cust_date_ndx on sales(cust_id, sale_date);
Index created.
SQL>
Note : Remember that Index statistics are automatically gathered with the CREATE INDEX.
Testing the same query, I expect the Index to be used. But I find something else as well :
SQL> select /*+ gather_plan_statistics */
2 cust_id, count(*)
3 from sales
4 where sale_date between
5 to_date('01-DEC-10','DD-MON-RR') and to_date('14-DEC-10','DD-MON-RR')
6 and cust_id = 4
7 group by cust_id;
CUST_ID COUNT(*)
---------- ----------
4 117
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 65hm33unu3mcm, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ cust_id, count(*) from sales where
sale_date between to_date('01-DEC-10','DD-MON-RR') and
to_date('14-DEC-10','DD-MON-RR') and cust_id = 4 group by cust_id
Plan hash value: 1839877207
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT GROUP BY NOSORT| | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | FILTER | | 1 | | 117 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | SALES_CUST_DATE_NDX | 1 | 129 | 117 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-DEC-10','DD-MON-RR')less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
3 - access("CUST_ID"=4 AND "SALE_DATE" greater_than_equal_to TO_DATE('01-DEC-10','DD-MON-RR') AND
"SALE_DATE" less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
Note
-----
- cardinality feedback used for this statement
28 rows selected.
SQL>
Oracle estimated a Cardinality of 129 rows but also tells me that Cardinality Feedback was used.
Note that this SQL is exactly the same as the first one (has the same SQL_ID) but has been re-parsed and created as a new Child Cursor (CURSOR 1) with a new Plan (new PLAN_HASH_VALUE).
I then run a DDL statement to invalidate SQLs :
SQL> comment on table sales is 'Sales Data';
Comment created.
SQL>
Retesting the query :
SQL> select /*+ gather_plan_statistics */
2 cust_id, count(*)
3 from sales
4 where sale_date between
5 to_date('01-DEC-10','DD-MON-RR') and to_date('14-DEC-10','DD-MON-RR')
6 and cust_id = 4
7 group by cust_id;
CUST_ID COUNT(*)
---------- ----------
4 117
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 65hm33unu3mcm, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ cust_id, count(*) from sales where
sale_date between to_date('01-DEC-10','DD-MON-RR') and
to_date('14-DEC-10','DD-MON-RR') and cust_id = 4 group by cust_id
Plan hash value: 1839877207
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT GROUP BY NOSORT| | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | FILTER | | 1 | | 117 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | SALES_CUST_DATE_NDX | 1 | 129 | 117 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-DEC-10','DD-MON-RR') less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
3 - access("CUST_ID"=4 AND "SALE_DATE" greater_than_equal_to TO_DATE('01-DEC-10','DD-MON-RR') AND
"SALE_DATE" less_than_equal_to TO_DATE('14-DEC-10','DD-MON-RR'))
24 rows selected.
SQL>
This time, the "cardinality feedback was used for this statement" message does not appear.
(Comments on what you think are the "what" and "why" are welcome)
.
.
.
13 February, 2011
06 February, 2011
01 February, 2011
Most Popular Posts - Jan 11
Blogger's "Stats" feature shows that the 3 most popular posts in the past 30 days have been :
1. OPEN RESETLOGS without really doing a Recovery : 299 pageviews
2. Materialized View on Prebuilt Table : 233 pageviews
3. AUTOEXTEND ON Next Size : 201 pageviews
.
.
.
Subscribe to:
Posts (Atom)