Monday, August 25, 2014

Query Rewrite Explain

Learnt something new in Oracle today, it’s all about Query rewrite capabilities and Explain Rewrite functionality. Let’s begin with a demo.

rajesh@ORA10G> create table t1
  2  as
  3  select * from dba_users;

Table created.

rajesh@ORA10G>
rajesh@ORA10G> alter table t1
  2  add constraint t1_pk
  3  primary key(username);

Table altered.

rajesh@ORA10G> create table t2
  2  as
  3  select b.*
  4  from dba_objects b,
  5      dba_users a
  6  where a.username = b.owner;

Table created.

rajesh@ORA10G> alter table t2
  2  add constraint t2_fk
  3  foreign key(owner)
  4  references t1;

Table altered.

rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T2');

PL/SQL procedure successfully completed.

rajesh@ORA10G>

Table T1 and T2 got created along with necessary primary key and foreign keys, table T2 in this context is referred to as Key-Preserved table, since each row from T2 joins at most only once with T1, next we define our materialized views.

rajesh@ORA10G> create materialized view t1_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select owner,object_type,count(*),
  7     sum(object_id),count(object_id),
  8     min(object_id), max(object_id),
  9     avg(object_id), grouping_id(owner,object_type) grp
 10  from t1, t2
 11  where t1.username = t2.owner
 12  group by cube(owner,object_type) ;

Materialized view created.

rajesh@ORA10G>
rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T1_MV');

PL/SQL procedure successfully completed.

rajesh@ORA10G>

Now for the Validation part,

rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G> select owner,count(*)
  2  from t1, t2
  3  where t1.username = t2.owner
  4  and t1.username ='SCOTT'
  5  group by owner;

Execution Plan
----------------------------------------------------------
Plan hash value: 2390704265

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     3 |    39 |     3   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| T1_MV |     3 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1_MV"."OWNER"='SCOTT' AND "T1_MV"."GRP"=1)

rajesh@ORA10G> select object_type,sum(object_id)
  2  from t1, t2
  3  where t1.username = t2.owner
  4  group by object_type
  5  having sum(object_id) > 500;

Execution Plan
----------------------------------------------------------
Plan hash value: 2390704265

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   104 |  1872 |     3   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| T1_MV |   104 |  1872 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1_MV"."GRP"=2 AND "T1_MV"."SUM(OBJECT_ID)">500)

rajesh@ORA10G> set autotrace off

Queries are transparently rewritten using materialized views, we are good so far.  But when we had the queries in this way – rewrite doesn’t happen

rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G>
rajesh@ORA10G> select owner,count(*)
  2  from  t2
  3  group by owner
  4  having count(*) > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2529584183

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |    10 |   127   (4)| 00:00:02 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     2 |    10 |   127   (4)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| T2   | 39115 |   190K|   124   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)>100)

rajesh@ORA10G> set autotrace off

So how to find why the rewrites are not happening in this particular case,  even though we require data only from our Key persevered tables,  Enter into the explain_rewrite method added up in dbms_mview API from Oracle 10g database.

rajesh@ORA10G>
rajesh@ORA10G> create or replace function explain_rewrite(p_qry varchar2)
  2  return sys.RewriteArrayType
  3  pipelined
  4  as
  5     pragma autonomous_transaction;
  6     l_data sys.RewriteArrayType := sys.RewriteArrayType();
  7  begin
  8     dbms_mview.explain_rewrite(p_qry,null,l_data);
  9     for x in ( select * from table(l_data) )
 10     loop
 11             pipe row( sys.RewriteMessage(x.mv_owner ,
 12             x.mv_name         ,
 13             x.sequence        ,
 14             x.query_text      ,
 15             x.query_block_no  ,
 16             x.rewritten_text  ,
 17             x.message         ,
 18             x.pass            ,
 19             x.mv_in_msg       ,
 20             x.measure_in_msg  ,
 21             x.join_back_tbl   ,
 22             x.join_back_col   ,
 23             x.original_cost   ,
 24             x.rewritten_cost  ,
 25             x.flags           ,
 26             x.reserved1       ,
 27             x.reserved2       ));
 28     end loop;
 29     return;
 30  end explain_rewrite;
 31  /

Function created.

rajesh@ORA10G>

To find the details, just invoke this function by passing our query string as parameter.

rajesh@ORA10G> @printtbl ' select query_text,rewritten_text,message,pass,join_back_tbl,mv_in_msg from table
                                                (explain_rewrite("select owner,count(*) from  t2 group by owner having count(*) > 100 ")) '
                                               
QUERY_TEXT                    : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT                : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
MESSAGE                       : "QSM-01150: query did not rewrite"
PASS                          : "NO"
JOIN_BACK_TBL                 : ""
MV_IN_MSG                     : ""
-----------------
QUERY_TEXT                    : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT                : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
MESSAGE                       : "QSM-01110: query rewrite not possible with materialized view T1_MV because it contains a join between tables (T2 and T1) that is not present in the query and that potentially eliminates rows needed by the query"
PASS                          : "YES"
JOIN_BACK_TBL                 : "T2 and T1"
MV_IN_MSG                     : "T1_MV"
-----------------

PL/SQL procedure successfully completed.

rajesh@ORA10G>

From the highlighted output, optimizer suspect that certain rows are about to be eliminated

rajesh@ORA10G> desc t2
 Name              Null?    Type
 ----------------- -------- -------------
 OWNER                      VARCHAR2(30)
 OBJECT_NAME                VARCHAR2(128)
 SUBOBJECT_NAME             VARCHAR2(30)
 OBJECT_ID                  NUMBER
 DATA_OBJECT_ID             NUMBER
 OBJECT_TYPE                VARCHAR2(19)
 CREATED                    DATE
 LAST_DDL_TIME              DATE
 TIMESTAMP                  VARCHAR2(19)
 STATUS                     VARCHAR2(7)
 TEMPORARY                  VARCHAR2(1)
 GENERATED                  VARCHAR2(1)
 SECONDARY                  VARCHAR2(1)

rajesh@ORA10G>

The only case where certain rows from Child table get eliminated from join is only by means of Orphan child records (by having NULL values in foreign key columns), the moment we realized this and redefine a NOT NULL constraint on foreign key columns, We are back to support query rewrites.

 rajesh@ORA10G> alter table t2 modify owner not null;

Table altered.

rajesh@ORA10G> @printtbl ' select query_text,rewritten_text,message,pass,join_back_tbl,mv_in_msg from table (explain_rewrite("select owner,count(*) from  t2 group by owner having count(*) > 100 ")) '
QUERY_TEXT                    : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT                : "SELECT T1_MV.OWNER OWNER,T1_MV.COUNT(*) COUNT(*) FROM RAJESH.T1_MV T1_MV WHERE T1_MV.GRP=1 AND T1_
MV.COUNT(*)>100"
MESSAGE                       : "QSM-01151: query was rewritten"
PASS                          : "NO"
JOIN_BACK_TBL                 : ""
MV_IN_MSG                     : ""
-----------------
QUERY_TEXT                    : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT                : "SELECT T1_MV.OWNER OWNER,T1_MV.COUNT(*) COUNT(*) FROM RAJESH.T1_MV T1_MV WHERE T1_MV.GRP=1 AND T1_
MV.COUNT(*)>100"
MESSAGE                       : "QSM-01033: query rewritten with materialized view, T1_MV"
PASS                          : "YES"
JOIN_BACK_TBL                 : ""
MV_IN_MSG                     : "T1_MV"
-----------------
QUERY_TEXT                    : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT                : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
MESSAGE                       : "QSM-01082: Joining materialized view, T1_MV, with table, T2, not possible"
PASS                          : "YES"
JOIN_BACK_TBL                 : "T2"
MV_IN_MSG                     : "T1_MV"
-----------------
QUERY_TEXT                    : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT                : "select owner,count(*) from  t2 group by owner having count(*) > 100 "
MESSAGE                       : "QSM-01102: materialized view, T1_MV, requires join back to table, T2, on column, OWNER"
PASS                          : "YES"
JOIN_BACK_TBL                 : "T2"
MV_IN_MSG                     : "T1_MV"
-----------------

PL/SQL procedure successfully completed.

rajesh@ORA10G>
rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G>
rajesh@ORA10G> select owner,count(*)
  2  from  t2
  3  group by owner
  4  having count(*) > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2390704265

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   105 |  1365 |     3   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| T1_MV |   105 |  1365 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1_MV"."GRP"=1 AND "T1_MV"."COUNT(*)">100)

rajesh@ORA10G>
rajesh@ORA10G> set autotrace off
The dreaded NOT NULL constraint strikes again!
Posted by at No comments:

12c Implicit Result Sets

Starting with Oracle 12c Rather than defining explicit ref cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly. To see an example of this

rajesh@PDB1> declare
  2     c sys_refcursor;
  3     l_cursor number;
  4     l_sql varchar2(100);
  5     l_rows number;
  6  begin
  7     open c for
  8             select dname from dept;
  9     l_cursor := dbms_sql.open_cursor;
 10     l_sql :=' select ename from emp '||
 11                     ' where deptno = 10 ';
 12     dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
 13     l_rows := dbms_sql.execute(l_cursor);
 14     dbms_sql.return_result(c);
 15     dbms_sql.return_result(l_cursor);
 16  end;
 17  /

PL/SQL procedure successfully completed.

ResultSet #1

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

4 rows selected.

ResultSet #2

ENAME
----------
CLARK
KING
MILLER

3 rows selected.

rajesh@PDB1>

Typically we would expect these result sets to be processed by a client programming language like Java or C#, but they can now be processed in PL/SQL using GET_NEXT_RESULT procedure (newly added up in 12c) within dbms_sql package.

rajesh@PDB1> create or replace procedure p
  2  as
  3     c sys_refcursor;
  4     l_cursor number;
  5     l_sql varchar2(100);
  6     l_rows number;
  7  begin
  8     open c for
  9             select dname from dept;
 10     l_cursor := dbms_sql.open_cursor;
 11     l_sql :=' select ename,sal from emp '||
 12                     ' where deptno = 10 ';
 13     dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
 14     l_rows := dbms_sql.execute(l_cursor);
 15     dbms_sql.return_result(c);
 16     dbms_sql.return_result(l_cursor);
 17  end;
 18  /

Procedure created.

rajesh@PDB1> declare
  2     l_cursor number;
  3     l_refcursor sys_refcursor;
  4     l_rows number;
  5     l_cursor2 number;
  6     l_col_cnt number;
  7     l_desc_tab dbms_sql.desc_tab;
  8     l_name varchar2(20);
  9     l_sal number;
 10  begin
 11     l_cursor := dbms_sql.open_cursor
 12             (treat_as_client_for_results => TRUE) ;
 13     dbms_sql.parse
 14     ( c=> l_cursor,
 15       statement=>'begin p; end;' ,
 16       language_flag=> dbms_sql.native );
 17     l_rows := dbms_sql.execute(l_cursor);
 18     loop
 19             begin
 20                     dbms_sql.get_next_result(l_cursor,l_refcursor);
 21                     exception
 22                             when no_data_found then
 23                                     exit;
 24             end;
 25             l_cursor2 := dbms_sql.to_cursor_number(l_refcursor);
 26             dbms_sql.describe_columns(l_cursor2,l_col_cnt,l_desc_tab);
 27             l_refcursor := dbms_sql.to_refcursor(l_cursor2);
 28             CASE l_col_cnt
 29                     when 1 then
 30                             loop
 31                                     fetch l_refcursor into l_name;
 32                                     exit when l_refcursor%notfound;
 33                                     dbms_output.put_line(' Dname = '||l_name) ;
 34                             end loop;
 35                     when 2 then
 36                             loop
 37                                     fetch l_refcursor into l_name,l_sal;
 38                                     exit when l_refcursor%notfound;
 39                                     dbms_output.put_line(' Ename = '||l_name||
 40                                     ' Salary = '||l_sal);
 41                             end loop;
 42             end case;
 43             close l_refcursor;
 44     end loop;
 45  end;
 46  /
 Dname = ACCOUNTING
 Dname = RESEARCH
 Dname = SALES
 Dname = OPERATIONS
 Ename = CLARK Salary = 2450
 Ename = KING Salary = 5000
 Ename = MILLER Salary = 1300

PL/SQL procedure successfully completed.


rajesh@PDB1>
Posted by at No comments:

Tuesday, August 12, 2014

Linguistic Indexes

Characters are by default stored based on default encoding scheme. When it comes to “case-insensitive” searches, where data may be stored in many different cases and we want to return all data that matches a character value, regardless of its case.

The issue of course is that by default all searches are case-sensitive.

The standard fix is for the application to convert the data to a consistent case when performing the search. For example a search WHERE UPPER(Name) = ‘BELL’ will return all values of “BELL” regardless of their case but this will negate the use of any standard index on the Name column.

Therefore, a Function-Based index is required, say based on UPPER(Name), to ensure an efficient index access is possible for case insensitive searches.

However, this often requires an additional index to be created and for the application to be explicitly written to make use of the function-based index defined function.

Another possible solution is the use of a Linguistic Index. This is an index that is created based on a specific case insensitive linguistic language

Basic steps are

rajesh@ORA10G> create table t1 as
  2  select a.*, object_name as x
  3  from all_objects a;

Table created.

rajesh@ORA10G>
rajesh@ORA10G> insert into t1
  2  select a.*,
  3     decode(rownum,1,'Hello',2,'HELLO',3,'hello') x
  4  from all_objects a
  5  where rownum <=3;

3 rows created.

rajesh@ORA10G> commit;

Commit complete.

rajesh@ORA10G>
rajesh@ORA10G> create index t1_ind on t1( nlssort(x,'nls_sort=binary_ci'));

Index created.

rajesh@ORA10G> begin
  2     dbms_stats.gather_table_stats
  3     ( user,'T1',
  4      cascade=>true,
  5    method_opt=>'for all indexed columns size 254');
  6  end;
  7  /

PL/SQL procedure successfully completed.

rajesh@ORA10G>
rajesh@ORA10G>
rajesh@ORA10G> variable x varchar2(10);
rajesh@ORA10G> exec :x :='hello';

PL/SQL procedure successfully completed.

rajesh@ORA10G>
rajesh@ORA10G> set autotrace on explain statistics
rajesh@ORA10G> select x from t1 r1 where x = :x;

X
------------------------------
hello

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   584 |  9928 |   228   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |   584 |  9928 |   228   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("X"=:X)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       1011  consistent gets
          0  physical reads
          0  redo size
        331  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA10G> alter session set nls_sort=binary_ci;

Session altered.

rajesh@ORA10G> alter session set nls_comp=linguistic;

Session altered.

rajesh@ORA10G> select x from t1 r1 where x = :x;

X
------------------------------
Hello
HELLO
hello

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    12 |   204 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |    12 |   204 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IND |    12 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("X",'nls_sort=''BINARY_CI''')=NLSSORT(:X,'nls_sort=''BIN
              ARY_CI'''))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

rajesh@ORA10G> set autotrace off
rajesh@ORA10G>

Now let’s create a new table with its own set of B*Tree index.


rajesh@ORA10G> connect /@ora10g
Connected.
rajesh@ORA10G> set timing off
rajesh@ORA10G> create table t2 as select * from all_objects;

Table created.

rajesh@ORA10G> create index t2_ind on t2(object_name);

Index created.

rajesh@ORA10G> begin
  2     dbms_stats.gather_table_stats
  3     ( user,'T2',
  4      cascade=>true,
  5    method_opt=>'for all indexed columns size 254');
  6  end;
  7  /

PL/SQL procedure successfully completed.

rajesh@ORA10G> variable x2 varchar2(10);
rajesh@ORA10G> exec :x2 :='a%';

PL/SQL procedure successfully completed.

rajesh@ORA10G>
rajesh@ORA10G> set autotrace traceonly explain statistics
rajesh@ORA10G> select object_name from t2 where object_name like :x2;

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2876512201

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |  2920 | 70080 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T2_IND |  2920 | 70080 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME" LIKE :X2)
       filter("OBJECT_NAME" LIKE :X2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        398  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

rajesh@ORA10G>

So we did a Range scan and returned 2 rows. This looks fine. See what happens when nls_comp set to linguistic, Index Range scan changed to Index Fast Full Scan and returned nearly 1500+ rows.

rajesh@ORA10G> alter session set nls_sort=binary_ci;

Session altered.

rajesh@ORA10G> alter session set nls_comp=linguistic;

Session altered.

rajesh@ORA10G>
rajesh@ORA10G> select object_name from t2 x1 where object_name like :x2;

1559 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1290188030

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |  2920 | 70080 |    64   (2)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T2_IND |  2920 | 70080 |    64   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE :X2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        297  consistent gets
          0  physical reads
          0  redo size
      30570  bytes sent via SQL*Net to client
        456  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1559  rows processed

The next issue is Linguistic issues are ignored for some types of predicates like min, max operations.

rajesh@ORA10G> connect /@ora10g
Connected.
rajesh@ORA10G> set timing off
rajesh@ORA10G> set autotrace traceonly explain statistics
rajesh@ORA10G> select max(x) from t1;

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |   227   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 58395 |   969K|   227   (1)| 00:00:03 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       1011  consistent gets
          0  physical reads
          0  redo size
        356  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA10G>

With regular B*Tree index we see this.

rajesh@ORA10G> select max(object_name) from t2;

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2365471795

-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |        |     1 |    24 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T2_IND | 58394 |  1368K|     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


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

rajesh@ORA10G>

Even when nls_sort and nls_comp parameters set we don’t see changes in plan.

rajesh@ORA10G> alter session set nls_sort=binary_ci;

Session altered.

rajesh@ORA10G> alter session set nls_comp=linguistic;

Session altered.

rajesh@ORA10G> select max(x) from t1 x1;

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |   227   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 58395 |   969K|   227   (1)| 00:00:03 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       1011  consistent gets
          0  physical reads
          0  redo size
        334  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


rajesh@ORA10G>
Posted by at No comments:
Subscribe to: Posts (Atom)