Bejeweler Pro Example
When a query uses an index, but not the best possible rate Many times I asked that a query does not respond in a timely manner when the execution plan shows that you are using an index. The answer in some cases is very simple and because the index is not as selective as possible, no filters that could filter everything. To say that a query uses an access plan for an index is not sufficient to ensure that you have found the most efficient way. To show a bit of that I'm talking about putting together a case, but somewhat trivial therefore less instructive, so that they understand the idea.
I will create a T-chart with 3 columns. COL1 column will have 1000 distinct values \u200b\u200band column COL2 will have 10 possible values. Also add filler column COL3
create table t as
select mod (rownum, 1000) col1,
mod (rownum, 100000) col2,
dbms_random.string ('a', 50)
col3 from dual connect by rownum
Once you create the table I create an index for COL1 and harvest statistics for table and index:
t_idx create index on t (col1) begin
dbms_stats.gather_table_stats (ownname => user, tabname => 'T', cascade => true);
end;
I run the following query and extract the plan using DBMS_XPLAN.DISPLAY_CURSOR so that the plan be more detailed (in a future I will use the same method to show how to see how "confused" the optimizer when there are no adequate statistics):
select / * + * gather_plan_statistics / * from t where col1 = 9
and
col2 = 9 select * from table (dbms_xplan.display_cursor (null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 25p2md7bszhj6, child number 0
------------------------------------- select /*+ gather_plan_statistics */ * from t where col1 = 9 and col2
= 9
Plan hash value: 1020776977
----------------------------------------------------------------------------------------------- filtering 10, as shown in step 1 (A-Rows)
(Note: A-Rows is the number of actual rows and E-Rows is the number of rows estimated)
<= 1000000
Now I will delete the index and T_IDX I will create another with the same name but indexed by COL1 and COL2. Let the new plan:
drop index create index t_idx
t_idx on t (col1, col2)
select / * + gather_plan_statistics 2 * / * from t where col1 = 9
and col2 = 9 select
* from table (dbms_xplan.display_cursor (null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------- -------------------------------------------------- -----------------------
SQL_ID 25p2md7bszhj6, child number 0 -------------------------------------
select /*+ gather_plan_statistics */ * from t where col1 = 9 and col2
= 9
Plan hash value: 1020776977
-----------------------------------------------------------------------------------------------
10 fewer resources and therefore less processing time to obtain the same output.
The moral is that you should never settle for just verify that the decision reached by an index and in-depth analysis on the current checking indexing is the best index possible or if you can find some other combination more efficient.
0 comments:
Post a Comment