Tom,
I am using functional indexes a lot and found them very useful. Various reports run in against our datawarehouse format the data in different ways (like using to_char, to_number, substr, replace etc..) We store the base data in the tables and have functional indexes on this. Rebuilding these indexes after every load is time consuming. also I dont want to create indexes for some twice-a-mont-run query.

What would be your general suggestions for a warehouse where the data is formated in different ways. Our developement team keep asking for function based indexes but I would like to see if there are other things I can use. Is there any other alternatives? I explored the use of MVs but since we re-load the entire data on certain fact tables, we cant use it currently. Could you pls give your valuable suggestion? Thanks a lot.

However with Standard Edition the function based indexes option is not enabled. In such cases how can we optimize the query having

UPPER(NAME) LIKE UPPER('Jo')

Thanks

Well, you can do a couple of things:

1) maintain a shadow column, 'UPPER_NAME'.  Have this column be maintained by a trigger.  Create an index on this column and use "where upper_name like upper('jo%')"

2) use the technique outlined above with the (name like 'J%' or name like 'j%' ) (using an index hint as well with bind variables perhaps)

3) use an index fast full scan in a subquery.  We can full scan a "skinny index" much faster then a full scan of the table perhaps:

DOC>drop table t;
DOC>
DOC>
DOC>create table t as select * from all_objects;
DOC>
DOC>
DOC>create index t_idx on t(object_name) unrecoverable;
DOC>*/
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2   from t
  3   where rowid in ( select /*+ index_ffs( t t_idx ) */ rowid from t where upper(object_name) = 'FOOBAR' )
  4  /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=193 Card=192 Bytes=30528)
   1    0   NESTED LOOPS (Cost=193 Card=192 Bytes=30528)
   2    1     INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=192 Bytes=4608)
   3    1     TABLE ACCESS (BY USER ROWID) OF 'T' (Cost=1 Card=19114 Bytes=2580390)




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

ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2   from t
  3   where upper(object_name) = 'FOOBAR'
  4  /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'




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


 

Is it worth mentioning fast full index scans? If the function to be applied to the column was not known, or highly variable, then can we rely on Oracle to consider this or would it need an optimizer hint?

Hello TOM,
I have read about redundant indexes from this url
</code> https://asktom.oracle.com/magazine-archive.htm <code>
I couldn't get What is redundant indexes?. Can you please explain me?

regards,
siva

Tom,

SQL> select *
  2    from t
  3    where object_name in ( select /*+ index_ffs( t t_idx ) */ object_name from t where 
  4    upper(object_name) = 'FOOBAR' )
  5  ;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=993 Bytes=14
          3985)

   1    0   HASH JOIN (Cost=19 Card=993 Bytes=143985)
   2    1     VIEW OF 'VW_NSO_1' (Cost=6 Card=32 Bytes=544)
   3    2       SORT (UNIQUE) (Cost=6 Card=32 Bytes=544)
   4    3         INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost
          =4 Card=32 Bytes=544)

   5    1     TABLE ACCESS (FULL) OF 'T' (Cost=6 Card=3104 Bytes=39731
          2)





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

Is there a reason why you equi-joined, the rowid instead of
the object_name that I used in this query. 
With rowid = got 4  db block gets+ 33  consistent gets+
                 12  physical reads
With object_name = got 4  db block gets+19 consistent gets+
                    0 physical read

Thanks 

if you need to set query_rewrite_integrity to values other than trusted (whcih happened when people use MV), you can not use FBI

That is not true.  To use MV's you can set the level to one of three settings (this is a cut and paste from my book, i have a complete chapter on MV's and FBI's as well)

o QUERY_REWRITE_INTEGRITY: This parameter controls how Oracle re-writes queries and may be set to one of three values:

ENFORCED: Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms by which we can tell Oracle about other inferred relationships - and this would allow for more queries to be rewritten - but since Oracle does not enforce those relationships, it would not make use of those facts at this level.

TRUSTED: Queries will be rewritten using the constraints that are enforced by Oracle as well as the relationships we have told Oracle about that exist in the data but are not enforced by Oracle. For example, in our initial example we could have created the physical table MY_ALL_OBJECTS_AGGS manually using a parallel, nologging CREATE TABLE AS SELECT (to speed up the building of the summary table).  We could have then created the  materialized view instructing it to use this pre-built table instead of creating the summary table itself   If we wish Oracle to use this pre-built table during a subsequent query rewrite, we must specify a value of TRUSTED. This is because we want Oracle to 'trust' that we have supplied the correct data in the pre-built table - Oracle does not enforce that the data in that table is correct.

STALE_TOLERATED: Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is 'stale' (out of sync with the details). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly 'out of sync' answer is acceptable.


You can use any of them with FBI's and ONLY need trusted or stale_tolerated if you use a USER DEFINED (plsql code) funciton in the FBI.  Consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function my_upper( p_str in varchar2 ) return varchar2
  2  DETERMINISTIC
  3  as
  4  begin
  5          return upper(p_str);
  6  end;
  7  /
Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(25) );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(upper(x));
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx2 on t(substr(my_upper(x),1,25));

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_enabled = true;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain;

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_integrity=enforced;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index( t t_idx ) */ * from t where upper(x) = 'FOO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index( t t_idx2 ) */ * from t where substr(my_upper(x),1,25) = 'FOO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=1 Bytes=14)

<b>At enforced, upper (and in fact -- any builtin) is used.  my_upper -- not used..</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_integrity=trusted;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index( t t_idx ) */ * from t where upper(x) = 'FOO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index( t t_idx2 ) */ * from t where substr(my_upper(x),1,25) = 'FOO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1 Card=1)

<b>Now, and for the next case, my_upper index is used as well...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index( t t_idx ) */ * from t where upper(x) = 'FOO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index( t t_idx2 ) */ * from t where substr(my_upper(x),1,25) = 'FOO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off


<b>so, MV's and FBI's co-exist very nicely</b>
 

Hi Tom,

I tried your example on our DB (Oracle 8.1.7 Unix)
drop table t;

create table t as
select * from all_objects;

create index t_idx on t(object_name) unrecoverable;

select *
from t
where rowid in ( select /*+ index_ffs( t t_idx ) */ rowid from t where upper(object_name) = 'FOEVENT')

for above got explain plan like:

SELECT STATEMENT Hint=CHOOSE 286 60
HASH JOIN 286 44 K 60
INDEX FAST FULL SCAN T_IDX 286 6 K 4
TABLE ACCESS FULL T 28 K 3 M 54

and for query :
select *
from t
where upper(object_name) = 'FOEVENT'

got

SELECT STATEMENT Hint=CHOOSE
TABLE ACCESS FULL T
In first case I got again full table scan not
TABLE ACCESS (BY USER ROWID) OF 'T' like in your's case.

What's the reason for difference ?

Regards,

Sasa

Thank you for clearing that up for me!

Tom,

I ran 2 different queries , both using a function based index in them, one of them seem to use the function based index without using an hint , and the other uses function based index only if an hint is used. Here is the steps I used :-

SQL> alter session set query_rewrite_enabled =true;

Session altered.

Query 1:-
==========
SQL> set autotrace on explain
SQL> select * from t where UPPER(email) = 'ABC@DEF.COM';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=47)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=1 Bytes=47

SQL> select /*+ index(T T_UPP_EMAIL_IDX) */ * from T where UPPER(email) = 'ABC@DEF.COM';

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=137)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=
      1 Bytes=137)

   2    1     INDEX (RANGE SCAN) OF 'T_UPP_EMAIL_IDX' (NON-UNI
      QUE) (Cost=1 Card=1)

Query 1, uses an hint to use the function based index.

Query 2:-
=======

SQL>select name from T2 where lower(email)='abc@def.com';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2
       Card=2 Bytes=34)

   2    1     INDEX (RANGE SCAN) OF 'T2_LWR_EML_IDX' (NON-UNIQ
      UE) (Cost=1 Card=2)


As you can see the difference between query 1 and query 2,
where a hint is required in query 1  but not in query 2  to
use the function based index. Can you pls. explain why ?

Thanks,
TS 

Only to remember that in Oracle standar edition 9i
Function indexes are enabled.
If you really need and can migrate you can be planning to.

Tom,

Followup on my Question..

I tried running both the queries, Query1 and Query2
on production where there is a lot of rows, both of them use the function based index with out having to use the hint.

Conclusion : As Always You Are Right.

Thanks a ton,
TS

Hi tom

Here Given below the query and execution plan ...I build a query with 3 tables.,I have created the index on all the tables.The table tbl_letter_dtl having index on two columns(ORD_CD,CLI_INV_YN).
But this table is very big and not using index.
Can you suggest me how to Insist optimizer to use index.
How to optimise it.
====================================================
1 select a.cli_Cd,b.ORD_DEF_SEA_CD,c.LETTER_UNI_CD
2 from tbl_letter_dtl c,
3 tbl_cust_mast a,
4 tbl_ord_mast b
5 where
6 a.cust_cd=b.cust_cd and
7 b.ord_cd=c.ord_cd and
8 c.cli_inv_yn =0 and
9 b.ORD_DEF_SEA_CD=100 and
10* a.cli_cd=1000
11 /
Execution Plan
----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=989 Card=971 Bytes=36898)
1 0 HASH JOIN (Cost=989 Card=971 Bytes=36898)
2 1 HASH JOIN (Cost=601 Card=831 Bytes=20775)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_ORD_MAST1' (NON-UNIQUE)(Cost=33 Card=831 Bytes=12465)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_CLI_cUST_CD' (NON-UNIQUE) (Cost=280 Card=157478 Bytes=1574780)
5 1 TABLE ACCESS (FULL) OF 'TBL_LETTER_DTL' (Cost=362 Card=7558 Bytes=98254)
=================================================

Thanks

Ramu

Even though the documentation says it's not available, I have Standard edition for Windows NT installed and just created a function-based index. That option is not even in the v$option table anymore (as it was in 8i).

In 8i:
Select * from v$option where upper(parameter) like 'FUNC%';
PARAMETER VALUE
Function-based indexes FALSE

In 9i:
No rows returned...

Hi TOm,

How do I make it work for this.

alter session set query_rewrite_enabled =true;
alter session set query_rewrite_integrity = trusted;

create table t
(name varchar2(30),
created date);

insert into t values ('SPAWN',SYSDATE);
insert into t values ('ANGELA' SYSDATE);
commit;

create index t_idx2 on t(to_char(created,'YYYYMMDDHH24MISS'));

select * from t where
to_char(sysdate,'YYYYMMDDHH24MISS') > TO_CHAR(CREATED,'YYYYMMDDHH24MISS');

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Developmen
NLSRTL Version 3.4.1.0.0 - Production

Is it possible to make function based indexes work with dates?

thanks a lot.

Hi Tom,

SOrry Tom for even disturbing you. I tried it again and it worked. And you know why it didn't work the first time. COz I didnt analyze or gather statistics from the table t. hehehe. I forgot. SO no stats, RBO will be used, right?
The table table was just created for this question so I'm sure there are no stats as seen from last_analyzed column.

Thanks though. If I'm wrong please respond. thanks again.

Rory,

Why use FBI and a complicated statement like
select * from t where
to_char(sysdate,'YYYYMMDDHH24MISS') > TO_CHAR (CREATED,'YYYYMMDDHH24MISS');

instead of just
select * from t where created < sysdate;

or

select * from t where created < to_date('14-MAR-2003 08:10:10', 'DD-MON-YYYY HH24:MI:SS');

You don't need FBI for this unless you are using it to show/test how FBI works.

Am I not understanding something?

Hello Tom !

Consider this:

cr@STARR.LHR> desc custom_be_master
Name Null? Type
----------------------- -------- ----------------
COLLECTORATE_CODE NOT NULL VARCHAR2(4)
BE_TYPE NOT NULL VARCHAR2(2)
BILLOFENTRY_NO NOT NULL NUMBER(7)
BILLOFENTRY_DATE NOT NULL DATE
ST_REG_NO VARCHAR2(13)
IMPORTER_NAME VARCHAR2(50)
IMPORTER_NTN VARCHAR2(7)
IGM_NO NUMBER(7)
TOTAL_SALESTAX NUMBER(13)
CLAIM_NO VARCHAR2(13)
BE_STATUS VARCHAR2(1)
OFFICER_CODE VARCHAR2(6)
CR_VERIFICATION VARCHAR2(1)
COLL_VERIFICATION VARCHAR2(1)
CR_VERIFICATION_UID VARCHAR2(10)
COLL_VERIFICATION_UID VARCHAR2(10)
CR_VERIFICATION_DATE DATE
UPDATION_UID VARCHAR2(10)
UPDATION_DATE DATE
GROSS_WEIGHT NUMBER(15,4)
UOM_WEIGHT VARCHAR2(4)
COLL_VERIFICATION_DATE DATE
CASH_DATE DATE
DATA_DATE DATE

cr@STARR.LHR> set linesize 500
cr@STARR.LHR> select count(1) from custom_be_master;

COUNT(1)
==========
891583

Elapsed: 00:00:02.10

Execution Plan
========================================================== 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q460000
3 2 INDEX* (FAST FULL SCAN) OF 'CUS_BE_MST_UK' (UNIQUE) (C :Q460000
ost=457 Card=891583)
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT 0 FROM :I."CUSTOM_BE_MAS
3 PARALLEL_COMBINED_WITH_PARENT

Statistics
========================================================== 19 recursive calls
1697 db block gets
3596 consistent gets
3031 physical reads
684 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

cr@STARR.LHR> CREATE UNIQUE INDEX "CR"."CUS_BE_MST_UK"
2 ON "CR"."CUSTOM_BE_MASTER"("COLLECTORATE_CODE", "BE_TYPE",
3 "BILLOFENTRY_NO", TRUNC("BILLOFENTRY_DATE"))
4 TABLESPACE "STARINDEXES" PCTFREE 10 INITRANS 2 MAXTRANS 255
5 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
6 PCTINCREASE 0 FREELISTS 1)
7 PARALLEL ( DEGREE DEFAULT )
8 LOGGING
9 COMPRESS 3
10
cr@STARR.LHR>
cr@STARR.LHR> ed
Wrote file afiedt.buf

1* SELECT MIN(BILLOFENTRY_DATE) FROM CUSTOM_BE_MASTER WHERE COLLECTORATE_CODE ='LAFU' and be_Type='HC'
cr@STARR.LHR> /

MIN(BILLO
=========
02-MAY-02

Elapsed: 00:00:06.91

Execution Plan
========================================================== 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1830 Card=1 Bytes=16
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CUSTOM_BE_MASTER' (Cost=1830 Card=22290 Bytes=356640)

Statistics
========================================================== 0 recursive calls
10 db block gets
12053 consistent gets
8869 physical reads
0 redo size
385 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

cr@STARR.LHR> ed
Wrote file afiedt.buf

1* SELECT /*+Rule */ MIN(BILLOFENTRY_DATE) FROM CUSTOM_BE_MASTER WHERE COLLECTORATE_CODE ='LAFU' and be_Type='HC'
cr@STARR.LHR> /

MIN(BILLO
========= 02-MAY-02

Elapsed: 00:00:00.97

Execution Plan
========================================================== 0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOM_BE_MASTER'
3 2 INDEX (RANGE SCAN) OF 'CUS_BE_MST_UK' (UNIQUE)

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

cr@STARR.LHR> spo off


My Question:

Why my index is not getting used when i use CBO and is used when i use RBO ??? I am having great performace degrades due to this problem. Please advise

Riaz

Hello Tom !

Here's information u asked for:

cr@STARR.LHR> show parameter optimizer

NAME TYPE VALUE
==================================== ======= ======
optimizer_features_enable string 8.1.7
optimizer_index_caching integer 40
optimizer_index_cost_adj integer 50
optimizer_max_permutations integer 80000
optimizer_mode string CHOOSE
optimizer_percent_parallel integer 0

Regards

From your article on Function based indexes:
----------
·    For the optimizer to use function based indexes, the following session or system variables must be set: 
·    QUERY_REWRITE_ENABLED=TRUE
·    QUERY_REWRITE_INTEGRITY=TRUSTED
---

On my Oracle 9.2.0.1 server I have these option

·    QUERY_REWRITE_ENABLED=FALSE
·    QUERY_REWRITE_INTEGRITY=enforced

I have a Test table with id number, user_name varchar(30); I have an function based index (upper(user_name)) on user name column.

When I executed the following query it picked the function based index.

SQL> set autotrace traceonly

SQL> select * from test where UPPER(user_name) = 'TEST';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=100)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Ca
          rd=1 Bytes=100)

   2    1     INDEX (UNIQUE SCAN) OF 'TEST_USERNAME_IDX' (UNIQU
          E) (Cost=1 Card=912)

I never changed the query rewrite option for my session. How the optimizer picked my function based index even though I didn’t enable query rewrite?

Thanks 
 

oh! Then I don't need to set these parameters? In which version onwards it has been changed?

Oracle9iR2 relaxed this so that the FBI on the *builtin* function may be used. Eg, you would expect in 9iR2 to see this behaviour:

ops$tkyte@ORA920> create table t ( c1 varchar2(20), c2 varchar2(20) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 'hello', 'world' );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace function f( p_str in varchar2 ) return varchar2
  2  DETERMINISTIC
  3  as
  4  begin
  5          return upper(p_str);
  6  end;
  7  /

Function created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create index t_c1_idx on t( upper(c1) );

Index created.

ops$tkyte@ORA920> create index t_c2_idx on t( substr(f(c2),1,20) );

Index created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace traceonly explain
ops$tkyte@ORA920> alter session set query_rewrite_enabled=FALSE;

Session altered.

ops$tkyte@ORA920> alter session set query_rewrite_integrity=enforced;

Session altered.

ops$tkyte@ORA920> select * from t where upper(c1) = 'HELLO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10000 Bytes=240000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=10000 Bytes=240000)
   2    1     INDEX (RANGE SCAN) OF 'T_C1_IDX' (NON-UNIQUE) (Cost=1 Card=4000)



ops$tkyte@ORA920> select * from t where substr(f(c2),1,20) = 'WORLD';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9619 Card=10000 Bytes=240000)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=9619 Card=10000 Bytes=240000)



ops$tkyte@ORA920> alter session set query_rewrite_enabled=TRUE;

Session altered.

ops$tkyte@ORA920> alter session set query_rewrite_integrity=TRUSTED;

Session altered.

ops$tkyte@ORA920> select * from t where upper(c1) = 'HELLO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10000 Bytes=240000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=10000 Bytes=240000)
   2    1     INDEX (RANGE SCAN) OF 'T_C1_IDX' (NON-UNIQUE) (Cost=1 Card=4000)



ops$tkyte@ORA920> select * from t where substr(f(c2),1,20) = 'WORLD';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10000 Bytes=240000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=10000 Bytes=240000)
   2    1     INDEX (RANGE SCAN) OF 'T_C2_IDX' (NON-UNIQUE) (Cost=1 Card=4000)



ops$tkyte@ORA920> set autotrace off

<b>the fbi on upper -- used, on f(x) -- not until you have the correct settings</b>

In 9iR1 and before, neither would use the fbi in the first case. 

Hello Tom !

Consider this:


cr@STARR.LHR> show parameter query

query_rewrite_enabled boolean TRUE
query_rewrite_integrity string enforced

create table test1 (a number);
cr@STARR.LHR> insert into test1 select 1 from dba_objects;

5061 rows created.

(and so on until a significant no. of rows generated)

1* create index test1 on test1(to_char(a)) tablespace starindexes
cr@STARR.LHR> /

Index created.

cr@STARR.LHR> set autotrace on
cr@STARR.LHR> select count(1) from test1
2 where to_char(a)='1';

COUNT(1)
==========
18138624

Elapsed: 00:00:22.13

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST1' (NON-UNIQUE) (Cost=1 Card=
181387 Bytes=544161)





Statistics
==========================================================
27 recursive calls
0 db block gets
32868 consistent gets
32861 physical reads
0 redo size
370 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

cr@STARR.LHR> ed
Wrote file afiedt.buf

1 select count(1) from test1
2* where to_char(a)='1'
cr@STARR.LHR> show parameter query

NAME TYPE VALUE
==================================== ======= =========================
query_rewrite_enabled boolean TRUE
query_rewrite_integrity string enforced

cr@STARR.LHR> alter session set query_rewrite_enabled=false

Session altered.

Elapsed: 00:00:00.46
cr@STARR.LHR> select count(1) from test1
2 where to_char(a)='1'
3 /

COUNT(1)
==========
18138624

Elapsed: 00:00:19.31

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4160 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=4160 Card=18138624
Bytes=54415872)





Statistics
==========================================================
0 recursive calls
13 db block gets
27403 consistent gets
27326 physical reads
0 redo size
370 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

cr@STARR.LHR> alter session set query_rewrite_enabled=true;

Session altered.

Elapsed: 00:00:00.31
cr@STARR.LHR> select count(1) from test1
2 where to_char(a)='1'
3 /

COUNT(1)
==========
18138624

Elapsed: 00:00:22.22

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST1' (NON-UNIQUE) (Cost=1 Card=
181387 Bytes=544161)





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


In the above example, the parameter query_rewrite_Enabled affects the optimizer behaviour. But i can't see any change due to query_rewrite_integrity parameter (which is set to enforced in this case).

So how can you say
"For the optimizer to use function based indexes, the following session or system variables must be set:
· QUERY_REWRITE_ENABLED=TRUE
· QUERY_REWRITE_INTEGRITY=TRUSTED"

With Regarids

really really wish people would include versions -- why is it they never do?

so, try it with a user defined function -- like my example -- then tell us what happens.


....
ops$tkyte@ORA817DEV> alter session set query_rewrite_enabled=TRUE;

Session altered.

ops$tkyte@ORA817DEV> alter session set query_rewrite_integrity=enforced;

Session altered.

ops$tkyte@ORA817DEV> select * from t where upper(c1) = 'HELLO';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10000 Bytes=240000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=10000 Bytes=240000)
   2    1     INDEX (RANGE SCAN) OF 'T_C1_IDX' (NON-UNIQUE) (Cost=1 Card=10000)



ops$tkyte@ORA817DEV> select * from t where substr(f(c2),1,20) = 'WORLD';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2879 Card=10000 Bytes=240000)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2879 Card=10000 Bytes=240000)



ops$tkyte@ORA817DEV> set autotrace off


 

I have a question regarding to calculate distance for a give post code
to get all other post code within certial distance.

The Table structure likes

postal_data(
POSTALCODE Not NULL VARCHAR2(10)
STATE NOT NULL CHAR(2)
LONGITUDE FLOAT(126)
LATITUDE FLOAT(126))

This table has 820000 records.

A query need to get all postal code within 30 miles from this point

Longtitude: -87.755811
Latitude: 41.715508

I was given the following query. The performance is realy slow since the query
was contructed in a way that index cannot be used.
Is it possible to create a function index for this query?

Select state,POSTALCODE,
round(69.1 * SQRT(POWER(LATITUDE - (41.715508), 2)
+ 0.6* POWER(LONGITUDE - (-87.755811), 2)),2) Miles
from postal_data
where (round(69.1 * SQRT(POWER(LATITUDE -(41.715508), 2)
+ 0.6 * POWER(LONGITUDE - (-87.755811), 2)),2) ) < 30
Order by round(69.1 * SQRT(POWER(LATITUDE -(41.715508), 2)
+ 0.6 * POWER(LONGITUDE - (-87.755811), 2)),2) ASC

This site(</code> http://tutorial153.easycfm.com <code> shows another way to calculate it:

SELECT zipcode, latitude, longitude, statename, city,
ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963) AS distance
FROM zipcodes
WHERE (latitude >= #passedzip.latitude# - (#passedradius#/111))
And (latitude <= #passedzip.latitude# + (#passedradius#/111))
AND (longitude >= #passedzip.longitude# - (#passedradius#/111))
AND (longitude <= #passedzip.longitude# + (#passedradius#/111))
ORDER BY distance

This query seems much better than the previous one.
But this query give different results. I don't know which query is correct.

I know Oracle has spacial data function to do this.
But it is not as easy as a simple query.

I would like to know what is the best way to get this done.


Thanks,

Hi, Mr.Kyte.

Do we also have redundant indexes in the examples of the following link? Or, as you usually say, am I comparing oranges to apples?

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5671539468597 <code>

Thanks again.

Hi

We have some queries in oracle 7.3.4 with very strange behaviours.

For example a query such as

variable b1 varchar2(10)
exec :b1 := '0765'

select *
from a
where a_col1 = :b1

It takes ages to run, whereas if I use

variable b1 varchar2(10)
exec :b1 := 0765

select *
from a
where a_col1 = :b1

It runs instanenously.

If I do

set autotrace traceonly exp

both '0765' and 0765 have same explain plan....

Why these behaviours...? Optimizer bug? We cannot migrate our Oracle version because our Vantive version is only certified on 7.3.4

Hi

tkprof for the slow query (I have NOT let it finish since it takes over 2 hours already!)

using '0254' for variable :b1

SELECT run.mru_msefa_ins_valor_generico codigo
FROM tgcprt10.msrun_rel_unicidades run
WHERE run.mspar_id_parametro = 'IDVPN'
AND run.msefa_ins_valor_generico = :b1
AND run.mru_msfam_id_familia = 'EQ'
AND run.mru_mscom_id_componente = 'RO'
AND run.mru_mspar_id_parametro = 'CODIGO'
AND NOT EXISTS (
SELECT efa.msefa_ins_valor_generico
FROM tgcprt10.msefa_elem_fase efa
WHERE efa.msfam_id_familia = 'EQ'
AND efa.mscom_id_componente = 'RO'
AND efa.mspar_id_parametro = 'CODIGO'
AND efa.msval_id_valor = 'CODIGO'
AND run.mru_msefa_ins_valor_generico LIKE efa.msefa_ins_valor_generico || '%')

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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 86 (TTD$ORACLE7)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_MSRUN_REL_UNICIDADES'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'MSEFA_ELEM_FASE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'MSEFA_ELEM_FASE_FK1'
(NON-UNIQUE)

tkprof for the fast query using 0254 for variable :b1

SELECT run.mru_msefa_ins_valor_generico codigo
FROM tgcprt10.msrun_rel_unicidades run
WHERE run.mspar_id_parametro = 'IDVPN'
AND run.msefa_ins_valor_generico = :b1
AND run.mru_msfam_id_familia = 'EQ'
AND run.mru_mscom_id_componente = 'RO'
AND run.mru_mspar_id_parametro = 'CODIGO'
AND NOT EXISTS (
SELECT efa.msefa_ins_valor_generico
FROM tgcprt10.msefa_elem_fase efa
WHERE efa.msfam_id_familia = 'EQ'
AND efa.mscom_id_componente = 'RO'
AND efa.mspar_id_parametro = 'CODIGO'
AND efa.msval_id_valor = 'CODIGO'
AND run.mru_msefa_ins_valor_generico LIKE efa.msefa_ins_valor_generico || '%')

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 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 86 (TTD$ORACLE7)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_MSRUN_REL_UNICIDADES'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'MSEFA_ELEM_FASE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'MSEFA_ELEM_FASE_FK1'
(NON-UNIQUE)

same explain plan!

the first query suffers TONs of buffer busy waits, I think the problem is

AND run.mru_msefa_ins_valor_generico LIKE efa.msefa_ins_valor_generico || '%'

if I get rid of that condition query runs very fast with any variable

run.mru_msefa_ins_valor_generico is varchar2(100)
efa.msefa_ins_valor_generico is varchar2(2000)

cheers

Hi

SLOW QUERY:
==============
BINDS #1:
bind 0: dty=1 mxl=128(100) mal=00 scl=00 pre=00 oacflg=01
bfp=0086f5a4 bln=100 avl=04 flg=05
value="0254"

FAST QUERY
==============
BINDS #1:
bind 0: dty=1 mxl=128(100) mal=00 scl=00 pre=00 oacflg=01
bfp=0086d5c4 bln=100 avl=03 flg=05
value="254"

what can this be?

Tom,

I 've a query below:-

select a.ID,a.EMAIL,a.NAME
from table1 a, table2 b
where a.ID = b.ID
AND (LOWER(a.NAME) like '%sometext'
OR LOWER(b.NAME) like '%sometext' )
order by ID

both of the columns using the LOWER function has
function indexes. If I take the '%sometext' and reverse
it to 'sometext%' then function indexes are used. whereas
if i do it as '%sometext', function indexes are ignored.
Is there anyway i could force the function indexes to be
used in this case?

Thanks,
TS

Tom,

I 've a query like this:-

select a.ID,a.EMAIL,a.NAME from table1 a, table2 b
where (a.ID = b.ID)
AND ( ( LOWER(a.EMAIL) = 'abc@def.com' )
OR ( LOWER(b.EMAIL) = 'abc@def.com'))
order by ID

The query was doing a full table scan, so I created function indexes on LOWER(EMAIL) for both tables 1 and 2 and re-analyzed the tables along with indexes. When I run the query again, it doesn't seem to use the function indexes. I replaced "OR" with an "AND" and the query uses function index for both the columns. Do I need to do something to make the query use function index with the OR operator in it?

Thanks,
TS

tell me the logic you would envision it using with the OR.

Say it used the index on t1 to find all rows with abc@def.com.  
Then it used the index on t2.

Then it "joined" them.  Would that be the right answer?  (rhetorical q, no, it would not)

It has to JOIN and then COMPARE.  It cannot select and then JOIN.  It is not semantically the "same thing".  See:

ops$tkyte@ORA920> set autotrace on explain
ops$tkyte@ORA920> select a.ID,a.EMAIL,a.NAME from t1 a, t2 b
  2  where (a.ID = b.ID)
  3  AND ( (  LOWER(a.EMAIL) = 'abc@def.com' )
  4        OR
  5            (  LOWER(b.EMAIL) = 'abc@def.com')
  6          )
  7  order by ID
  8  /
 
        ID EMAIL                          NAME
---------- ------------------------------ ------------------------------
         1 abc@def.com                    x
         2 Xabc@def.com                   x
         3 abc@def.com                    x
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2123 Card=1990 Bytes=173130)
   1    0   SORT (ORDER BY) (Cost=2123 Card=1990 Bytes=173130)
   2    1     HASH JOIN (Cost=2095 Card=1990 Bytes=173130)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=963 Card=100000 Bytes=3000000)   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=963 Card=100000 Bytes=5700000) 
 
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.id, a.email, a.name
  2    from ( select * from t1 where lower(email) = 'abc@def.com' ) A,
  3         ( select * from t2 where lower(email) = 'abc@def.com' ) B
  4   where (a.ID = b.ID)
  5   order by id
  6  /
 
        ID EMAIL                          NAME
---------- ------------------------------ ------------------------------
         3 abc@def.com                    x
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1000 Bytes=87000)
   1    0   SORT (ORDER BY) (Cost=20 Card=1000 Bytes=87000)
   2    1     HASH JOIN (Cost=5 Card=1000 Bytes=87000)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1000 Bytes=30000)
   4    3         INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=400)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1000 Bytes=57000)
   6    5         INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card=400)
 
 
 
ops$tkyte@ORA920> set autotrace off


The join with an OR across the tables precludes the indexes.  Perhaps what you might want is:

ops$tkyte@ORA920> select a.id, a.email, a.name
  2    from ( select * from t1 where lower(email) = 'abc@def.com' ) A,
  3         t2 b
  4   where (a.ID = b.ID)
  5   union
  6  select a.id, a.email, a.name
  7    from t1 a,
  8         ( select * from t2 where lower(email) = 'abc@def.com' ) B
  9   where (a.ID = b.ID)
 10   order by id
 11  /
 
        ID EMAIL                          NAME
---------- ------------------------------ ------------------------------
         1 abc@def.com                    x
         2 Xabc@def.com                   x
         3 abc@def.com                    x
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=894 Card=2000 Bytes=157000)
   1    0   SORT (UNIQUE) (Cost=866 Card=2000 Bytes=157000)
   2    1     UNION-ALL
   3    2       NESTED LOOPS (Cost=2 Card=1000 Bytes=70000)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1000 Bytes=57000)
   5    4           INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card=400)
   6    3         INDEX (UNIQUE SCAN) OF 'SYS_C007109' (UNIQUE)
   7    2       MERGE JOIN (Cost=836 Card=1000 Bytes=87000)
   8    7         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=100000 Bytes=5700000)
   9    8           INDEX (FULL SCAN) OF 'SYS_C007108' (UNIQUE) (Cost=26 Card=100000)
  10    7         SORT (JOIN) (Cost=10 Card=1000 Bytes=30000)
  11   10           TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1000 Bytes=30000)
  12   11             INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=100)

as long as you are aware the UNION "sort/distincts" the data -- that is, de-dups it (so it is NOT the same as your original query, but might be what you want) 

Tom,

I rewrote my query as per your example
and it picks up the function index and also
executes much faster.

Thanks a ton,
TS

Hi Tom,

I've a varchar2 column in the table that stores numeric as well as varchar2 data (not my design). I'm running a query against this table and I need rows where this field has numeric value. I tried using like, not like but none of the option is using index and does full table scan. Question is is there a way to make this query run faster, how does like and not like clause, <,>,between evaluated by the optimizer.

select count(ps.party_id)
from hz_party_sites ps, hz_parties p
where p.party_id = ps.PARTY_ID
and p.ORIG_SYSTEM_REFERENCE < 'A%'

both these tables have more than 1.6 million records.

explain plan shows hash join and full table scan of both the tables. Please help me understand how optimizer will evaluate these operators. Many Thanks.

Hi, Thanks for showing one more way of counting. Question is how to return numeric value stored in varchar2 field containing numeric as well as character data without doing a full table scan.

More importantly if you throw some light or give some reference on how optimizer will evaluate like and NOT like clauses. Does 'Not like' clause has to perform a full table scan. Is it beeter to use <,> or between. Please pardon my ignorance if this sounds too trivial. Thanks.

Hi

i have a slow querye, it uses index fast full scan instead of range scan when use bind variables... How can I force it to use range scan? (it goes range scan using literals)

Tom,
I created an index on (col1 || col2) as per requirement from a colleague . Then I tried to find out whether this index is being used or not and I came to conclusion that its not using that index.

Query was something like this

Update table tab1
set col_id = (select tab2.col_id from tab2
where tab1.col1||tab1.col2 = tab2.col1||tab2.col2 );

Note : There is non-unique index on both the tables for (col1||col2)

If I add some range to above query like
AND tab1.col1||tab1.col2 in('x','v')

it uses the index otherwise not.

Can you please explain me why it used the index with AND clause and why not without it.

Thanks

Thanks for your time.
I used it the same way as you described me in your reply, by using
Update (SELECT)

but I want to know and understand the reason why it was not using that index in join condition. So, that If I suggest this to somebody not to do like this way, what reason should I give. Before giving a reason, atleast I should be clear myself.

Here is the exact SQL

ALTER TABLE SP_JCT_STKH_ADDR
ADD (DC_ACCT_ID NUMBER(15));

UPDATE SP_JCT_STKH_ADDR A
SET DC_ACCT_ID = (SELECT B.DC_ACCT_ID
FROM SP_STKH B
WHERE A.STKH_NBR = B.STKH_NBR AND
A.STKH_ACCT_NBR = B.STKH_ACCT_NBR);

This is the SQL which I'm using now. Now it has a unique index on STKH_NBR ,STKH_ACCT_NBR on both the tables.

But earlier one developer wrote the SQL like this :

UPDATE SP_JCT_STKH_ADDR A
SET DC_ACCT_ID = (SELECT B.DC_ACCT_ID
FROM SP_STKH B
WHERE A.STKH_NBR||A.STKH_ACCT_NBR = B.STKH_NBR||B.STKH_ACCT_NBR);

And an index was created on (STKH_NBR||STKH_ACCT_NBR) for both the tables.

When I did autotrace, I saw that its not using the Index.
You have given a very good example where this combination will fail. I want to know because of this reason its not using the Index or beacuse its updating almost all the rows of SP_JCT_STKH_ADDR tables thats why optimizer finds the FULL TABLE ACCESS is faster than INDEX ACCESS.
Whats the reason Index is not being hit.

Thanks

I cannot say "why" -- more then likely you were using the RBO and RBO cannot see function based indexes.

I can say "thank goodness", since it made you look at this and code it correctly!  using the || 'trick' is a bad bad practice -- tell them to just stop doing that period!

But this shows "it could" use an index if it wanted to.

ops$tkyte@ORA9IR2> create table t1 ( id int, c1 int, c2 int );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( id int, c1 int, c2 int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t2_idx on t2(c1||c2);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set optimizer_goal=first_rows;
 
Session altered.
 
ops$tkyte@ORA9IR2> delete from plan_table;
 
5 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for
  2  update t1
  3     set id = ( select id from t2 where t1.c1||t1.c2 = t2.c1||t2.c2 )
  4  /
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display() )
  2  /
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
 
----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |             |    82 |  3198 |     2 |
|   1 |  UPDATE                      | T1          |       |       |       |
|   2 |   TABLE ACCESS FULL          | T1          |    82 |  3198 |     2 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2          |     1 |    39 |     1 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX      |     1 |       |     1 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(TO_CHAR("T2"."C1")||TO_CHAR("T2"."C2")=TO_CHAR(:B1)||TO_CHA
              R(:B2))
 
Note: cpu costing is off
 
18 rows selected.
 

Thanks very much.
Optimizer is CBO.
Both the tables were analyzed but still it was not using the Index but as per you example I issued the statement

alter session set optimizer_goal=first_rows;

It worked. Now I would try myself to find out why this happened and if no luck then I'll get back to you for your precious advice.

Hi, Tom.

I have to write a distance locator function. On the web user would enter zipcode or city and state along with radius in miles. I then have to get the longitude and latitude of that zipcode and get all the locations that are in that radius.

We are using Oracle 8i which I think does not have spacial function. What do you think is the best way of handeling this?

i am getting incorrect results.

Query: select distinct z1.zip_code, z2.zip_code, round(F_DIST(z1.LATITUDE, z1.LONGITUDE, z2.LATITUDE, z2.LONGITUDE)) as distance
from zip_detail z1, zip_detail z2
where Z2.LATITUDE <
(z1.LATITUDE + ( :dist / 69 ))
and Z2.LATITUDE >
(z1.LATITUDE - ( :dist / 69 ))

and Z2.LONGITUDE <
(z1.LONGITUDE + ( :dist / 47 ))
and Z2.LONGITUDE >
(z1.LONGITUDE - ( :dist / 47 ))
and F_DIST(z1.LATITUDE, z1.LONGITUDE,
Z2.LATITUDE, Z2.LONGITUDE) <= :dist
and z2.zip_code != '22206'
and z1.zip_code = '22206';


what is 47 and 69 is tehre a decimal point?

--mw

I tried to use the spacial but turns out our version does not support it.

I figured out the query. Problem is that it takes 20 seconds for just one zip code to run. and I got about 43,000. i will try to optimize the index and see if that could speed up performace.

THanks.

Tom, 

my query now runs only 20 seconds for one zip code but i have 43,000 source zipcodes and the same ammount of destination zip codes.  so query would take a month to complete.  any ideas how i can optimize?

SQL> select z1.zip_code, z2.zip_code, round(F_DIST(z1.LATITUDE, z1.LONGITUDE, z2.LATITUDE, z2.LONGIT

UDE)) 

  2  from (select distinct zip_code, LATITUDE, longitude from zip_INFO) z1, (select distinct zip_cod

e, LATITUDE, longitude from zip_INFO) z2

  3   where F_DIST(z1.LATITUDE, z1.LONGITUDE, 

  4        Z2.LATITUDE, Z2.LONGITUDE) <= 100

  5     and z2.zip_code != '22206'

  6     and z1.zip_code = '22206';

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=333 Card=3526 Bytes=

          211560)

 

   1    0   NESTED LOOPS (Cost=333 Card=3526 Bytes=211560)

   2    1     VIEW (Cost=14 Card=1 Bytes=30)

   3    2       SORT (UNIQUE) (Cost=14 Card=1 Bytes=17)

   4    3         INDEX (RANGE SCAN) OF 'ZIP_INFO_INDEX1' (NON-UNIQUE)

           (Cost=2 Card=2 Bytes=34)

 

   5    1     VIEW

   6    5       SORT (UNIQUE) (Cost=319 Card=70522 Bytes=1198874)

   7    6         INDEX (FAST FULL SCAN) OF 'ZIP_INFO_INDEX1' (NON-UNI

          QUE) (Cost=46 Card=70522 Bytes=1198874)



FUNCTION:
CREATE OR REPLACE function f_dist

 

 (in_lat1 in number, in_long1 in number,

  in_lat2 in number, in_long2 in number)

return number

as

begin

  /*lat/57.29577951  converts it from degrees to radiants

 

  */

   return 3963 * ( acos (

     (sin(in_lat1/57.29577951)*

 

      sin(in_lat2/57.29577951))

        +

     (cos(in_lat1/57.29577951)*

      cos(in_lat2/57.29577951)*

      cos(abs(in_long2)/57.29577951

        - abs(in_long1)/57.29577951))

     ) );

exception

 

  when others then return 9999;

end f_dist;

/


Thank you  

I tried using it but when i created a column of type geometry i get an error that GEOMETRY type not supported in this version. The data still got inserted however. but my query does not work.

Version: Oracle8i Enterprise Edition Release 8.1.7.0.0

Query:
select substr(Z2.zip_code, 1, 7) ZIP
from (select distinct zip_code, LATITUDE, longitude from zip_detail) z1, (select distinct zip_code, LATITUDE, longitude from zip_detail) z2
where z1.zip_code = 22206
AND MDSYS.LOCATOR_WITHIN_DISTANCE (Z2.longitude, z1.latitude, 'distance = '||:dist||',units=mile')= 'TRUE';

Error: Wrong number or type of arguments in MDSYS.LOCATOR_WITHIN_DISTANCE

-- what am i missing?

thanks

Tom, i got my select working on our dev server.

however on production i cannot create spacial index do to following error:

/*ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13236: internal error in R-tree processing: [failed to cluster in memory]
ORA-29400: data cartridge error
ORA-01653: unable to extend table
*/

-- why is this?

Yikes. can i create the spacial_index in a different tablespace?

Tom,

I have added more disk space to my table space and was able to create spacial index.
That fixed the speed of my query significantly. Now the query ran under 3 seconds! Thank you so much for that. Spacial function is great.

But i am still not done. :(

I have a total of 43000 zip codes . I need to run the query against every combination of those zip codes. I ran the query with the where clause where a.zip_code != b.zip code but then realized that that does not give me a unique combination. I now think that may be I need to change the where clause to where a.zip_code > b.zip code to get the unique combination. Do you think that would help?
I found the stats generating query that you wrote that produced these results (you will see that at that point that query ran for about 4 hours and i am afraid it has been running to at least 10 now):

NAME VALUE
-------------------------------------------------- ------------
SQL*Net roundtrips to/from client 20
bytes received via SQL*Net from client 3196
bytes sent via SQL*Net to client 2523
logons cumulative 1
logons current 1
opened cursors cumulative 537937
opened cursors current 5
recursive calls 538142
session logical reads 161916034
session pga memory 69726940
session pga memory max 69726940

NAME VALUE
-------------------------------------------------- ------------
session uga memory 69337508
session uga memory max 69341744
user calls 26
redo entries 215
redo size 584980
enqueue releases 2
enqueue requests 4
consistent gets 159226163
db block changes 436
db block gets 2689871
free buffer requested 7

NAME VALUE
-------------------------------------------------- ------------
calls to get snapshot scn: kcmgss 537962
cluster key scan block gets 2689627
cluster key scans 2689560
execute count 537935
parse count (hard) 2
parse count (total) 537933
rows fetched via callback 1075816
sorts (memory) 5
sorts (rows) 27
table fetch by rowid 1075892
table fetch continued row 1

NAME VALUE
-------------------------------------------------- ------------
table scan blocks gotten 150081334
table scan rows gotten 514851677
table scans (long tables) 537915
table scans (short tables) 1
buffer is not pinned count 154922739
buffer is pinned count 71
no buffer to keep pinned count 3
calls to kcmgcs 62
no work - consistent read gets 152771075

I am not sure what to do at this point. I need to get every combination of zipcodes that are within a 100 mile radius of each other and then calculate the exact distance on them.

Like i said before it takes 4 seconds to do it for one zip code with this query:

insert into zip_distance (zip_code_from, zip_code_to, distance)

select a.zip_code , b.zip_code, round(F_DIST(a.LATITUDE, a.LONGITUDE,
b.LATITUDE, b.LONGITUDE)) as "distance"

from (select zip_code, geometry, LATITUDE, LONGITUDE from zip_info) a,
(select zip_code, geometry, LATITUDE, LONGITUDE from zip_info) b

where a.zip_code != b.zip_code
and a.zip_code = 22206

AND (MDSYS.LOCATOR_WITHIN_DISTANCE

(a.geometry, b.geometry, 'distance = '||:dist||',units=mile')=
'TRUE');

Any ideas how i can optimize it or at least figure out how many results i would get with 43,000 of unique zip codes?

This data is for a web application and if it was up to me i would have done a dynamic calculation on the fly, but ....

Thank you so much for all that you have done so far.

Hmm, i could not find a spacial function that would calculate the exact distance. Which one is it?

thanks

Mary -

Please look at the chapter describing spatial functions
in the Spatial User's Guide and Reference (for version
9.2, it's chapter 11, "Geometry Functions") and you
should find words about sdo_geom.sdo_distance. Please
keep in mind that you'll need to use arguments to
this function (and all functions for that fact) that
are the right type. For spatial stuff, that type
is usually mdsys.sdo_geometry.

Creating an object in the mdsys.sdo_geometry type is
real easy. To describe a simple point like you are
doing, do this:

create table x (value mdsys.sdo_geometry);

insert into x values (
mdsys.sdo_geometry(2001, -- use this value for a point
null, -- often, but needs to be non-null
-- to compute distances in miles
mdsys.sdo_point_type(-30, 24), -- longitude, latitude
null, -- for point type
null); -- for point type

There is other stuff (i.e. metadata, spatial index)
that you need to do, but you can research that
on your own. It's all in the guide. Just read.

After you've established your tables, then you'll be ready
to use the sdo_geom.sdo_distance function.

Good luck.

Tom G

Yes, my bad. I usually execute my statements
before posting them anywhere, but I didn't do it
this time.

Try this insert instead:
insert into x values (
mdsys.sdo_geometry(2001,
null,
mdsys.sdo_point_type(-30, 24, null),
null,
null));

The point needs to have a "z" value specified, and I
also forgot the ending right parenthesis.

Sorry.

i already have geometry column in my table that is of geometry datatype

SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, .00005), SDO_DIM_ELEMENT('Y', -90,
90, .00005))

i try using sdo_geom.sdo_distance(a.geometry,b.geometry, c_d.shape, m.diminfo) to calcualte distance between two points. i get results really fst now too. but they dont appear to be correct. do u know what unit of measurment this function returns? do i need to do manipulation to get it in miles. i will look at Ref Guide again.

thanks

Mary -

Unless you want to bother Tom K. and all of his readers with
the nitty and moderately gritty details, perhaps we can
just chat between ourselves. The thing that you're
describing is your entry in the user_sdo_geom_metadata
view, and it is absolutely necessary for you to do
anything real with the spatial data. But it's not all
that you need.

Please send me a small (not 43,000 rows!) test case that
shows me what you're trying to do. I've gotten pretty good
at this spatial stuff, and I can probably help you out.
Write to me at thomas.gaines@noaa.gov, okay?

Tom and Mary,
Please let us know how you solved this problem. I, and I am sure other readers, would be very interested in how you inserted hundreds of millions of records in a table (300-500 millions?). Can Oracle do it in a reasonable time, Tom? (Don't ask me what is "resonable", I have no idea, I never inserted that many records).

I have a Index IDX1 (Global Partitioned Index)that is defined on 6 different columns.
The base table TAB1(around 250M records) is a range partitioned table (partitioned on dist_id)
The leading column of the index "period_code".

I have two SQL queries Q1 and Q2

Query Q1: This SQL query joins 5 different tables and groups by on a particular column.
It reads only from the IDX1 and doesn't read the table segments.
With the help of the index, I get the output within one minute.
If I force this query to go to table segments, this query takes for ever.

Query Q2: This SQL query too joins with 5 different tables and reads only the Index segments
However, the query just needs to read only one partition and full table scan on TAB1
runs fast like 30 seconds.
But the optimizer is reading the Index IDX1 and doing a fast full scan. This is resulting
the query to run for ever.
We can't put hints to ignore the Index as this query is generated by Business Objects.

With Index, Q1 runs fast and Q2 runs very slow. Without Index, Q1 runs slow and Q2 runs fast.

How to make Q1 and Q2 to run fast (I don't care even if the Index is not required).

thanks

Sorry for my way of putting my question.
Here are the Quries with their explan plan. Each query has two explain plan statements, one with Index and the other without Index.

Query Q1 with Index

SELECT
ACSS_PARENT_DIST.PARENT_DIST_NAME,
count (DISTINCT ACSS_FACT_PURCH_RETAIL.MASTER_STORE_OID)
FROM
ACSS_PARENT_DIST,
ACSS_FACT_PURCH_RETAIL,
ACSS_CLNT_BRAND,
ACSS_XREF_PERIOD_DISPLAY_VW,
ACSS_CLNT_DIST,
ACSS_DIST_TO_PARENT
WHERE
( ACSS_FACT_PURCH_RETAIL.CLIENT_BRAND_OID =ACSS_CLNT_BRAND.CLIENT_BRAND_OID )
AND ( ACSS_FACT_PURCH_RETAIL.PERIOD_CODE=ACSS_XREF_PERIOD_DISPLAY_VW.PERIOD_CODE )
AND ( ACSS_DIST_TO_PARENT.DIST_ID=ACSS_CLNT_DIST.DIST_ID )
AND ( ACSS_DIST_TO_PARENT.PARENT_DIST_ID=ACSS_PARENT_DIST.PARENT_DIST_ID )
AND ( ACSS_FACT_PURCH_RETAIL.DIST_ID=ACSS_CLNT_DIST.DIST_ID )
AND ( ACSS_FACT_PURCH_RETAIL.BOX_VOL_YAGO IS NOT NULL )
AND (
ACSS_CLNT_BRAND.BRAND_DESC = 'BUBBLICIOUS GUM'
AND ACSS_PARENT_DIST.PARENT_DIST_NAME = 'COREMARK'
AND ACSS_XREF_PERIOD_DISPLAY_VW.YEAR = '2003'
)
GROUP BY
ACSS_PARENT_DIST.PARENT_DIST_NAME
------------------------------------------------------------

Statement Id=228 Type=
Cost=1.95306968219662E-307 TimeStamp=13-05-04::15::52:26

(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 12
(19) SORT GROUP BY
Est. Rows: 1 Cost: 12
(18) NESTED LOOPS
Est. Rows: 22 Cost: 12
(15) HASH JOIN
Est. Rows: 5 Cost: 9
(13) HASH JOIN
Est. Rows: 5 Cost: 8
(11) MERGE JOIN CARTESIAN
Est. Rows: 1 Cost: 7
(8) MERGE JOIN CARTESIAN
Est. Rows: 1 Cost: 5
(5) TABLE ACCESS BY INDEX ROWID DW_BOA.ACSS_XREF_PERIOD [Analyzed]
(5) Blocks: 1 Est. Rows: 1 of 165 Cost: 2
Tablespace: DATA_SML_DWH
(4) NESTED LOOPS
Est. Rows: 1 Cost: 2
(2) TABLE ACCESS FULL DW_BOA.ACSS_PERIOD_RANGE [Analyzed]
(2) Blocks: 15 Est. Rows: 1 of 1 Cost: 1
Tablespace: DATA_SML_DWH
(3) UNIQUE INDEX RANGE SCAN DW_BOA.PK_ACSS_XREF_PERIOD [Analyzed]
Est. Rows: 8 Cost: 1
(7) BUFFER SORT
Est. Rows: 3 Cost: 3
(6) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_BRAND [Analyzed]
(6) Blocks: 71 Est. Rows: 3 of 2,178 Cost: 2
Tablespace: DATA_SML_DWH
(10) BUFFER SORT
Est. Rows: 1 Cost: 5
(9) TABLE ACCESS FULL DW_BOA.ACSS_PARENT_DIST [Analyzed]
(9) Blocks: 1 Est. Rows: 1 of 4 Cost: 1
Tablespace: DATA_SML_DWH
(12) TABLE ACCESS FULL DW_BOA.ACSS_DIST_TO_PARENT [Analyzed]
(12) Blocks: 1 Est. Rows: 76 of 76 Cost: 1
Tablespace: DATA_SML_DWH
(14) UNIQUE INDEX FULL SCAN DW_BOA.PK_ACSS_CLNT_DIST [Analyzed]
Est. Rows: 209 Cost: 1
(17) PARTITION RANGE ITERATOR
(16) NON-UNIQUE INDEX RANGE SCAN DW_BOA.IX_GBL_PER_DIST_CLBR_MSID [Analyzed]
Est. Rows: 4 Cost: 1


Query Q1 without Index(Just the Plan)

Statement Id=228 Type=
Cost=1.95306966204761E-307 TimeStamp=13-05-04::15::53:28

(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 1,030
(17) SORT GROUP BY
Est. Rows: 1 Cost: 1,030
(16) HASH JOIN
Est. Rows: 22 Cost: 1,030
(2) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_BRAND [Analyzed]
(2) Blocks: 71 Est. Rows: 3 of 2,178 Cost: 2
Tablespace: DATA_SML_DWH
(15) HASH JOIN
Est. Rows: 11,021 Cost: 1,028
(3) TABLE ACCESS FULL DW_BOA.ACSS_XREF_PERIOD [Analyzed]
(3) Blocks: 1 Est. Rows: 2 of 165 Cost: 1
Tablespace: DATA_SML_DWH
(14) NESTED LOOPS
Est. Rows: 14,961,509 Cost: 1,027
(11) HASH JOIN
Est. Rows: 19 Cost: 12
(9) HASH JOIN
Est. Rows: 19 Cost: 11
(7) MERGE JOIN CARTESIAN
Est. Rows: 1 Cost: 3
(4) TABLE ACCESS FULL DW_BOA.ACSS_PERIOD_RANGE [Analyzed]
(4) Blocks: 15 Est. Rows: 1 of 1 Cost: 1
Tablespace: DATA_SML_DWH
(6) BUFFER SORT
Est. Rows: 1 Cost: 2
(5) TABLE ACCESS FULL DW_BOA_EXT.ACSS_PARENT_DIST [Analyzed]
Est. Rows: 1 Cost: 1
(8) TABLE ACCESS FULL DW_BOA_EXT.ACSS_DIST_TO_PARENT [Analyzed]
Est. Rows: 76 Cost: 8
(10) UNIQUE INDEX FULL SCAN DW_BOA.PK_ACSS_CLNT_DIST [Analyzed]
Est. Rows: 209 Cost: 1
(13) PARTITION RANGE ITERATOR
(12) TABLE ACCESS FULL DW_BOA.ACSS_FACT_PURCH_RETAIL [Analyzed]
Blocks: 763,626 Est. Rows: 787,448 of 281,632,600 Cost: 214

-----------------------------------------------------------------------------------------------------

Query Q2 with Index

SQL Statement from editor:


SELECT
ACSS_ROLLING_PERIODS.ROLLING_CODE,
ACSS_CLNT_BRAND.BRAND_DESC,
ACSS_CLNT_DIST.HD_DIST_NAME || ' ' || ACSS_CLNT_DIST.DELIVERABLE_NUMBER,
ACSS_MAST_STORE.STORE_NAME || ' ' || ACSS_MAST_STORE.RETAIL_NUMBER,
sum(ACSS_FACT_PURCH_RETAIL.BOX_VOL),
sum(ACSS_FACT_PURCH_RETAIL.BOX_VOL_YAGO),
sum(ACSS_FACT_PURCH_RETAIL.UNIT_SALES),
sum(ACSS_FACT_PURCH_RETAIL.UNIT_SALES_YAGO),
sum(ACSS_FACT_PURCH_RETAIL.DOLLAR_SALES),
sum(ACSS_FACT_PURCH_RETAIL.DOLLAR_SALES_YAGO),
ACSS_CLNT_BRAND.BRAND_TITLE
FROM
ACSS_ROLLING_PERIODS,
ACSS_CLNT_BRAND,
ACSS_CLNT_DIST,
ACSS_MAST_STORE,
ACSS_FACT_PURCH_RETAIL,
ACSS_XREF_PERIOD_DISPLAY_VW
WHERE
( ACSS_XREF_PERIOD_DISPLAY_VW.PERIOD_CODE=ACSS_ROLLING_PERIODS.PERIOD_CODE )
AND ( ACSS_FACT_PURCH_RETAIL.CLIENT_BRAND_OID =ACSS_CLNT_BRAND.CLIENT_BRAND_OID )
AND ( ACSS_FACT_PURCH_RETAIL.MASTER_STORE_OID=ACSS_MAST_STORE.MASTER_STORE_OID )
AND ( ACSS_FACT_PURCH_RETAIL.PERIOD_CODE=ACSS_XREF_PERIOD_DISPLAY_VW.PERIOD_CODE )
AND ( ACSS_FACT_PURCH_RETAIL.DIST_ID=ACSS_CLNT_DIST.DIST_ID )
AND ( ACSS_ROLLING_PERIODS.AGG_TIME = 26 )
AND (
ACSS_CLNT_DIST.HD_DIST_NAME || ' ' || ACSS_CLNT_DIST.DELIVERABLE_NUMBER = 'EBY BROWN - OHIO 005948'
AND ACSS_CLNT_BRAND.BRAND_DESC = 'TRIDENT ORIGINAL GUM'
AND ACSS_ROLLING_PERIODS.ROLLING_CODE = '26 WEEK ENDING 2004/01/17' AND (ACSS_ROLLING_PERIODS.AGG_TIME = 26)
AND ACSS_MAST_STORE.RETAIL_NUMBER IN (SELECT
ACSS_MAST_STORE.RETAIL_NUMBER
FROM
ACSS_MAST_STORE,
ACSS_CLNT_DIST,
ACSS_CLNT_BRAND,
ACSS_ROLLING_PERIODS,
ACSS_FACT_PURCH_RETAIL,
ACSS_XREF_PERIOD_DISPLAY_VW
WHERE
( ACSS_XREF_PERIOD_DISPLAY_VW.PERIOD_CODE=ACSS_ROLLING_PERIODS.PERIOD_CODE )
AND ( ACSS_FACT_PURCH_RETAIL.CLIENT_BRAND_OID =ACSS_CLNT_BRAND.CLIENT_BRAND_OID )
AND ( ACSS_FACT_PURCH_RETAIL.MASTER_STORE_OID=ACSS_MAST_STORE.MASTER_STORE_OID )
AND ( ACSS_FACT_PURCH_RETAIL.PERIOD_CODE=ACSS_XREF_PERIOD_DISPLAY_VW.PERIOD_CODE )
AND ( ACSS_FACT_PURCH_RETAIL.DIST_ID=ACSS_CLNT_DIST.DIST_ID )
AND (
ACSS_CLNT_DIST.HD_DIST_NAME || ' ' || ACSS_CLNT_DIST.DELIVERABLE_NUMBER = 'EBY BROWN - OHIO 005948'
AND ACSS_CLNT_BRAND.BRAND_DESC = 'WRIGLEY SPEARMINT GUM'
AND ACSS_ROLLING_PERIODS.ROLLING_CODE = '26 WEEK ENDING 2004/01/17' AND (ACSS_ROLLING_PERIODS.AGG_TIME = 26)
)
GROUP BY
ACSS_MAST_STORE.RETAIL_NUMBER
HAVING
(
sum(ACSS_FACT_PURCH_RETAIL.UNIT_SALES) > sum(ACSS_FACT_PURCH_RETAIL.UNIT_SALES_YAGO)
)
)
)
GROUP BY
ACSS_ROLLING_PERIODS.ROLLING_CODE,
ACSS_CLNT_BRAND.BRAND_DESC,
ACSS_CLNT_DIST.HD_DIST_NAME || ' ' || ACSS_CLNT_DIST.DELIVERABLE_NUMBER,
ACSS_MAST_STORE.STORE_NAME || ' ' || ACSS_MAST_STORE.RETAIL_NUMBER,
ACSS_CLNT_BRAND.BRAND_TITLE
HAVING
(
sum(ACSS_FACT_PURCH_RETAIL.UNIT_SALES) < sum(ACSS_FACT_PURCH_RETAIL.UNIT_SALES_YAGO)
)
------------------------------------------------------------

Statement Id=4203132 Type=
Cost=2.64039087471493E-308 TimeStamp=13-05-04::15::55:29

(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 101
FILTER
(39) SORT GROUP BY
Est. Rows: 1 Cost: 101
(38) SORT GROUP BY
Est. Rows: 1 Cost: 101
(37) HASH JOIN SEMI
Est. Rows: 1 Cost: 98
(17) NESTED LOOPS
Est. Rows: 22 Cost: 47
(14) HASH JOIN
Est. Rows: 22 Cost: 44
(12) NESTED LOOPS
Est. Rows: 422 Cost: 38
(10) HASH JOIN
Est. Rows: 5,735 Cost: 38
(2) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_BRAND [Analyzed]
(2) Blocks: 71 Est. Rows: 3 of 2,178 Cost: 2
Tablespace: DATA_SML_DWH
(9) NESTED LOOPS
Est. Rows: 2,816,326 Cost: 36
(6) MERGE JOIN CARTESIAN
Est. Rows: 2 Cost: 4
(3) TABLE ACCESS FULL DW_BOA.ACSS_PERIOD_RANGE [Analyzed]
(3) Blocks: 15 Est. Rows: 1 of 1 Cost: 1
Tablespace: DATA_SML_DWH
(5) BUFFER SORT
Est. Rows: 2 Cost: 3
(4) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_DIST [Analyzed]
(4) Blocks: 71 Est. Rows: 2 of 209 Cost: 2
Tablespace: DATA_SML_DWH
(8) PARTITION RANGE ALL
(7) NON-UNIQUE INDEX FAST FULL SCAN DW_BOA.IX_GBL_PER_DIST_CLBR_MSID [Analyzed]
Est. Rows: 1,347,524 Cost: 5,502
(11) UNIQUE INDEX UNIQUE SCAN DW_BOA.PK_ACSS_XREF_PERIOD [Analyzed]
Est. Rows: 2
(13) TABLE ACCESS FULL DW_BOA.ACSS_ROLLING_PERIODS [Analyzed]
(13) Blocks: 199 Est. Rows: 5 of 3,998 Cost: 6
Tablespace: DATA_SML_DWH
(16) TABLE ACCESS BY INDEX ROWID DW_BOA.ACSS_MAST_STORE [Analyzed]
(16) Blocks: 9,111 Est. Rows: 945,850 of 945,850 Cost: 1
Tablespace: DATA_SML_DWH
(15) UNIQUE INDEX UNIQUE SCAN DW_BOA.SYS_C003050 [Analyzed]
Est. Rows: 1
(36) VIEW SYS.VW_NSO_1
Est. Rows: 2 Cost: 51
(35) FILTER
(34) SORT GROUP BY
Est. Rows: 2 Cost: 51
(33) NESTED LOOPS
Est. Rows: 22 Cost: 47
(30) HASH JOIN
Est. Rows: 22 Cost: 44
(28) NESTED LOOPS
Est. Rows: 422 Cost: 38
(26) HASH JOIN
Est. Rows: 5,735 Cost: 38
(18) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_BRAND [Analyzed]
(18) Blocks: 71 Est. Rows: 3 of 2,178 Cost: 2
Tablespace: DATA_SML_DWH
(25) NESTED LOOPS
Est. Rows: 2,816,326 Cost: 36
(22) MERGE JOIN CARTESIAN
Est. Rows: 2 Cost: 4
(19) TABLE ACCESS FULL DW_BOA.ACSS_PERIOD_RANGE [Analyzed]
(19) Blocks: 15 Est. Rows: 1 of 1 Cost: 1
Tablespace: DATA_SML_DWH
(21) BUFFER SORT
Est. Rows: 2 Cost: 3
(20) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_DIST [Analyzed]
(20) Blocks: 71 Est. Rows: 2 of 209 Cost: 2
Tablespace: DATA_SML_DWH
(24) PARTITION RANGE ALL
(23) NON-UNIQUE INDEX FAST FULL SCAN DW_BOA.IX_GBL_PER_DIST_CLBR_MSID [Analyzed]
Est. Rows: 1,347,524 Cost: 5,502
(27) UNIQUE INDEX UNIQUE SCAN DW_BOA.PK_ACSS_XREF_PERIOD [Analyzed]
Est. Rows: 2
(29) TABLE ACCESS FULL DW_BOA.ACSS_ROLLING_PERIODS [Analyzed]
(29) Blocks: 199 Est. Rows: 5 of 3,998 Cost: 6
Tablespace: DATA_SML_DWH
(32) TABLE ACCESS BY INDEX ROWID DW_BOA.ACSS_MAST_STORE [Analyzed]
(32) Blocks: 9,111 Est. Rows: 945,850 of 945,850 Cost: 1
Tablespace: DATA_SML_DWH
(31) UNIQUE INDEX UNIQUE SCAN DW_BOA.SYS_C003050 [Analyzed]
Est. Rows: 1

Query Q2 without Index(Just the Plan)

Statement Id=4203132 Type=
Cost=2.64039087471493E-308 TimeStamp=13-05-04::15::58:11

(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 257
FILTER
(39) SORT GROUP BY
Est. Rows: 1 Cost: 257
(38) SORT GROUP BY
Est. Rows: 1 Cost: 257
(37) HASH JOIN SEMI
Est. Rows: 1 Cost: 253
(17) NESTED LOOPS
Est. Rows: 22 Cost: 125
(14) HASH JOIN
Est. Rows: 22 Cost: 119
(12) NESTED LOOPS
Est. Rows: 422 Cost: 113
(10) HASH JOIN
Est. Rows: 5,735 Cost: 113
(2) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_BRAND [Analyzed]
(2) Blocks: 71 Est. Rows: 3 of 2,178 Cost: 2
Tablespace: DATA_SML_DWH
(9) NESTED LOOPS
Est. Rows: 2,816,326 Cost: 111
(6) MERGE JOIN CARTESIAN
Est. Rows: 2 Cost: 4
(3) TABLE ACCESS FULL DW_BOA.ACSS_PERIOD_RANGE [Analyzed]
(3) Blocks: 15 Est. Rows: 1 of 1 Cost: 1
Tablespace: DATA_SML_DWH
(5) BUFFER SORT
Est. Rows: 2 Cost: 3
(4) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_DIST [Analyzed]
(4) Blocks: 71 Est. Rows: 2 of 209 Cost: 2
Tablespace: DATA_SML_DWH
(8) PARTITION RANGE ITERATOR
(7) TABLE ACCESS FULL DW_BOA.ACSS_FACT_PURCH_RETAIL [Analyzed]
Blocks: 763,626 Est. Rows: 1,347,524 of 281,632,600 Cost: 54
(11) UNIQUE INDEX UNIQUE SCAN DW_BOA.PK_ACSS_XREF_PERIOD [Analyzed]
Est. Rows: 1
(13) TABLE ACCESS FULL DW_BOA.ACSS_ROLLING_PERIODS [Analyzed]
(13) Blocks: 199 Est. Rows: 5 of 3,998 Cost: 6
Tablespace: DATA_SML_DWH
(16) TABLE ACCESS BY INDEX ROWID DW_BOA.ACSS_MAST_STORE [Analyzed]
(16) Blocks: 9,111 Est. Rows: 1 of 945,850 Cost: 1
Tablespace: DATA_SML_DWH
(15) UNIQUE INDEX UNIQUE SCAN DW_BOA.SYS_C003050 [Analyzed]
Est. Rows: 1
(36) VIEW SYS.VW_NSO_1
Est. Rows: 2 Cost: 129
(35) FILTER
(34) SORT GROUP BY
Est. Rows: 2 Cost: 129
(33) NESTED LOOPS
Est. Rows: 22 Cost: 125
(30) HASH JOIN
Est. Rows: 22 Cost: 119
(28) NESTED LOOPS
Est. Rows: 422 Cost: 113
(26) HASH JOIN
Est. Rows: 5,735 Cost: 113
(18) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_BRAND [Analyzed]
(18) Blocks: 71 Est. Rows: 3 of 2,178 Cost: 2
Tablespace: DATA_SML_DWH
(25) NESTED LOOPS
Est. Rows: 2,816,326 Cost: 111
(22) MERGE JOIN CARTESIAN
Est. Rows: 2 Cost: 4
(19) TABLE ACCESS FULL DW_BOA.ACSS_PERIOD_RANGE [Analyzed]
(19) Blocks: 15 Est. Rows: 1 of 1 Cost: 1
Tablespace: DATA_SML_DWH
(21) BUFFER SORT
Est. Rows: 2 Cost: 3
(20) TABLE ACCESS FULL DW_BOA.ACSS_CLNT_DIST [Analyzed]
(20) Blocks: 71 Est. Rows: 2 of 209 Cost: 2
Tablespace: DATA_SML_DWH
(24) PARTITION RANGE ITERATOR
(23) TABLE ACCESS FULL DW_BOA.ACSS_FACT_PURCH_RETAIL [Analyzed]
Blocks: 763,626 Est. Rows: 1,347,524 of 281,632,600 Cost: 54
(27) UNIQUE INDEX UNIQUE SCAN DW_BOA.PK_ACSS_XREF_PERIOD [Analyzed]
Est. Rows: 1
(29) TABLE ACCESS FULL DW_BOA.ACSS_ROLLING_PERIODS [Analyzed]
(29) Blocks: 199 Est. Rows: 5 of 3,998 Cost: 6
Tablespace: DATA_SML_DWH
(32) TABLE ACCESS BY INDEX ROWID DW_BOA.ACSS_MAST_STORE [Analyzed]
(32) Blocks: 9,111 Est. Rows: 1 of 945,850 Cost: 1
Tablespace: DATA_SML_DWH
(31) UNIQUE INDEX UNIQUE SCAN DW_BOA.SYS_C003050 [Analyzed]
Est. Rows: 1

I use SQL Navigator and gathered the statistics of the Queries.
I am sorry if the query or the explain plan is not in a readable format.

The Index what I am talking about is in the ACSS_FACT_PURCH_RETAIL table. Both the queries uses this table.

I still don't understand what you want? The explain plan was generated by SQL navigator tool. This plan was not generated on SQL plus by saying "set autotrace on" or something like that.

Now, what do you think about the query? Why Index is useful in query and not in the other?

Thanks a lot

Sorry..... I didn't understood you. I am so S*u*id.

I did "analyze table... estimate statistics sample 2 percent".

Thanks again.

Do you mean just analyze the index and the table ?
Shall I use dbms_stats or analyze table statement?
What is the difference between dbms_stats and analyze?
What does analyze... for all indexes for all indexed columns mean?
Of the above methods which one generates histogram?

Thanks,

Hi,

I have read the total thread. Now this parameter is clear in respect of FBI. But this parameter is not clear in respect of MV. When should I use "TRUSTED" and when should I use "stale-tolerated". Would u like to give example on this ? Your example is always great to clear any one's doubt. I have understood that If I use "enforced" then if base table gets changed then query rewritten will be done from base table not from MV. Am I right ?

Thanks
Deba

Hi,

I am trying simulate the difference. Pls give some examples which will simulate the fact what you have mentioned. Still the language is not clear to me. Please give one example .

Thanks
Deba

Hi,

I don't have access. I will try to access this book. I have found another important difference.

Hi,

Please consider the following example. Though the value is "ENFORCED" still user-defined function is considered. Why ? Is it correct behaviour in 9.2.0.5 ?

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> drop table x;

Table dropped.

SQL> drop package das;

Package dropped.

SQL> drop function ldas;

Function dropped.

SQL> create table x as select * from all_objects;

SQL> create or replace package das as
  2  function lupper (lstring varchar2) return varchar2;
  3  function llower (lstring varchar2) return varchar2 deterministic;
  4  end;
  5  /

Package created.

SQL> create or replace package body das as
  2  function lupper (lstring varchar2) return varchar2 as
  3  begin
  4  return upper(lstring);
  5  end;
  6  function llower (lstring varchar2) return varchar2 deterministic as
  7  begin
  8  return lower(lstring);
  9  end;
 10  end;
 11  /
 
SQL> select name,value from v$parameter where name like '%rewrite%';

NAME                                                             VALUE
---------------------------------------------------------------- ---------
query_rewrite_enabled                                            TRUE
query_rewrite_integrity                                          enforced

SQL>  create index x_1 on x(das.llower(OWNER));

Index created.

SQL> analyze table x compute statistics;

Table analyzed.

SQL> analyze index x_1 compute statistics;

Index analyzed.

SQL> set timing on;
SQL> set autotrace on;

SQL> select * from x where das.llower(OWNER) = 'YMDBAADM';

no rows selected

Elapsed: 00:00:01.43

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=1827 Bytes=1
          57122)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=32 Card=1827 By
          tes=157122)

   2    1     INDEX (RANGE SCAN) OF 'X_1' (NON-UNIQUE) (Cost=5 Card=18
          27)

Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        506  bytes sent via SQL*Net to client
        270  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed
          
Not only that if I change the value of query_rewrite_enabled to "FALSE" still then the index ( based on user defined function ) is used.          
          
SQL> alter session set query_rewrite_enabled = 'FALSE';

Session altered.

SQL> select name,value from v$parameter where name like '%rewrite%';

NAME                                                             VALUE
---------------------------------------------------------------- ---------
query_rewrite_enabled                                            FALSE
query_rewrite_integrity                                          enforced

SQL> set autotrace on;
SQL> select * from x where das.llower(OWNER) = 'YMDBAADM';

no rows selected

Elapsed: 00:00:01.45

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=1827 Bytes=1
          57122)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=32 Card=1827 By
          tes=157122)

   2    1     INDEX (RANGE SCAN) OF 'X_1' (NON-UNIQUE) (Cost=5 Card=18
          27)

Statistics
----------------------------------------------------------
         25  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        512  bytes sent via SQL*Net to client
        270  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
          
Should I say that from 9.2.0.5 onwards there is no dependency on query_rewrite_enabled and query_rewrite_integrity in respect of FBI ?

Thanks

Deba 

Hi Tom,

I have not got ur reply what I have posted in above portion. This is quite important for me. U can see it
and after reading out the total thread , the above part proves every thing wrong. It might be dependent on version also. I need ur comments as always ur comment has been helpful.

Thanks
Deba

Hello Tom,
From the test result below, it seems the optimizer does not pick up the best plan. 

For example, the cost of "select min(sales_id) from sales" is 2 with index full scan(min/max),
so had optimizer picked up the same plan, the cost of "select min(sales_id), max(sales_id) from sales" would have been 2+2=4, it should not be worse than running two seperate SQLs, while each one returns either max() or min(), right?

However, when db_file_multiblock_read_count=1, it still picks fast full scan which has cost 59, when i set the parameter to 32, cost is 7, which is still bigger than theoratical number 4 if it uses full scan(min/max). 

Could you explain why the plan is different between select min/max alone and select max&max together even if the multiblock reads taken into account?

Here is the cut and paste of the session
<<
SQL>desc sales
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 SALES_ID                                  NOT NULL NUMBER(15)
 PRODUCT_ID                                NOT NULL NUMBER(3)
 STATE_ID                                  NOT NULL VARCHAR2(2)
 TS                                        NOT NULL DATE
 AMOUNT                                    NOT NULL NUMBER(10)
 DESCRIPTION                                        CHAR(1024)

SQL>analyze table audit_dbo.sales compute statistics
SQL>for table 
SQL>for all indexes;

Table analyzed.

SQL>select sum(blocks) from dba_tables 
SQL>where table_name='SALES'
SQL>and owner='AUDIT_DBO';

SUM(BLOCKS)
-----------
       5710

1 row selected.

SQL>select sum(leaf_blocks) from dba_indexes
SQL>where index_name='PK_SALES'
SQL>and owner='AUDIT_DBO';

SUM(LEAF_BLOCKS)
----------------
              96

1 row selected.

SQL>select count(*) from sales;

  COUNT(*)
----------
     15452

1 row selected.

SQL>show parameter multi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_file_multiblock_read_count        integer     1
mts_multiple_listeners               boolean     FALSE
parallel_adaptive_multi_user         boolean     FALSE
SQL>set autotrace on
SQL>select min(sales_id) from sales;

MIN(SALES_ID)
-------------
      2020731

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'PK_SALES' (UNIQUE) (Cost
          =2 Card=15452 Bytes=200876)





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

SQL>select min(sales_id), max(sales_id) from sales;

MIN(SALES_ID) MAX(SALES_ID)
------------- -------------
      2020731       2038682

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_SALES' (UNIQUE) (Cost=59 C
          ard=15452 Bytes=200876)





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

SQL>alter session set db_file_multiblock_read_count=32
SQL>/

Session altered.

SQL>select min(sales_id) from sales;

MIN(SALES_ID)
-------------
      2020731

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'PK_SALES' (UNIQUE) (Cost
          =2 Card=15452 Bytes=200876)





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

SQL>select min(sales_id), max(sales_id) from sales;

MIN(SALES_ID) MAX(SALES_ID)
------------- -------------
      2020731       2038682

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_SALES' (UNIQUE) (Cost=7 Ca
          rd=15452 Bytes=200876)





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

Hum, Optimizer does not know how to read first and last block when doing select min and max together, anyway i'll use UNION but just being curious.

Tom,

I'm hoping you can offer an explanation and/or advice for me... We have this query that's running a little slow (ever heard that before? :)). I made an FBI to speed it up, and it works great when I use a constant for the value of this particular variable in the query. However when I use a bind variable, the FBI isn't used. Unfortunately the end user has to wait in the application - a bind variable is automatically used, because the query is static and run in a PL/SQL procedure. I could make the query dynamic and concatenate a constant, but I'd rather not go there if possible. Please see the info below, and if you need any more info just let me know.

Thanks,
Mark

PS: I tried analyzing both the table and the FBI, and the results are the same. Automatic Workload Repository is "on". Version 10gR1.

The deterministic function which the query calls:
=================================================
function getBatchNumPart(in_batch in work.clientorder.batch%type)
return int deterministic as
ret_val int;
begin
utility.pkg_debug.f('FUNC GETBATCHNUMPART: Enter: inputs: in_batch=' || in_batch);
--batch is formatted CCC-NNNN (CCC is clientcode part; NNNN is number part)
ret_val := to_number(substr(in_batch, 5));
utility.pkg_debug.f('FUNC GETBATCHNUMPART: Exit: normal: return ' || ret_val);
return ret_val;
end;


The FBI on this function:
=========================
CREATE INDEX WORK.FBI_CO_BATCH_GETBATCHNUMPART
ON WORK.CLIENTORDER
(WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH));

TKProf snippet (bind variable used):
====================================
SELECT WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH)
FROM
(SELECT BATCH FROM WORK.CLIENTORDER WHERE CLIENTID=:B1 AND
WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH) IS NOT NULL ORDER BY
WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH) DESC) WHERE ROWNUM=1


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 1 4.87 4.69 0 122 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 4.87 4.69 0 122 0 1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=15822 pr=0 pw=0 time=7292419 us)
1 VIEW (cr=15822 pr=0 pw=0 time=7292349 us)
1 SORT ORDER BY STOPKEY (cr=15822 pr=0 pw=0 time=7292330 us)
3842 TABLE ACCESS FULL CLIENTORDER (cr=8138 pr=0 pw=0 time=3310776 us)

===============

TKProf snippet (bind variable not used):
========================================
select work.pkg_orders.getBatchNumPart(batch) from
(select batch from work.clientorder
where clientid=339 and work.pkg_orders.getBatchNumPart(batch) is not null
order by work.pkg_orders.getBatchNumPart(batch) desc)
where rownum=1

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 2 0.01 0.01 0 1392 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 1392 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=1392 pr=0 pw=0 time=11904 us)
1 VIEW (cr=1392 pr=0 pw=0 time=11893 us)
1 TABLE ACCESS BY INDEX ROWID CLIENTORDER (cr=1392 pr=0 pw=0 time=11877 us)
3237 INDEX FULL SCAN DESCENDING FBI_CO_BATCH_GETBATCHNUMPART (cr=15 pr=0 pw=0 time=9800 us)(object id 69894)

seems you would really want to have the client id on the leading edge.  For example:

ops$tkyte@ORA9IR2> create table t ( client_id number, batch number, data char(100) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  select mod(rownum,1000), rownum, 'x'
  3    from all_objects;
 
27990 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(client_id, foo(batch) );
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=> TRUE );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> exec :x := 339
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> select foo_batch
  2    from ( select foo(batch) foo_batch
  3             from t
  4            where client_id = :x
  5              and foo(batch) is not null
  6            order by client_id DESC, foo(batch) DESC
  7         )
  8   where rownum = 1
  9  /
 
 FOO_BATCH
----------
     27339




select foo_batch
  from ( select foo(batch) foo_batch
           from t
          where client_id = :x
            and foo(batch) is not null
          order by client_id DESC, foo(batch) DESC
       )
 where rownum = 1
                                                                                                                                                                      
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        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1
                                                                                                                                                                      
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 298
                                                                                                                                                                      
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=2 r=0 w=0 time=39 us)
      1   VIEW  (cr=2 r=0 w=0 time=33 us)
      1    INDEX RANGE SCAN DESCENDING OBJ#(40751) (cr=2 r=0 w=0 time=30 us)(object id 40751)


You won't get much better than that. 

Thanks for the response Tom. Strangely, after toying with the new index that you suggested (both enabled and disabled) the query from the HTML DB app does use an FBI (the old one if the new one is disabled), and it flies now. I'm not sure why.

When I test the query from SQL, as you did in your response (using the bind variable :x) it also uses an FBI (again, the old one is used if I disable the new one) and it flies. I have never tried this the way you did - I just ran the query in a PL/SQL block, using PL/SQL variables. Whenever I do this, neither FBI gets used. This isn't particularly important - I'm more confused than anything. Would you be able to shed some light onto why this happens? This PL/SQL block contains the "bad" query in my previous post - I did not post the PL/SQL part of the TKProf before.

Thanks a lot,
Mark

-----------

declare
l_batchnumpart number;
l_clientid number;
begin
l_clientid := 339;
select work.pkg_orders.getBatchNumPart(batch) into l_batchNumPart from
(select batch from work.clientorder
where clientid=l_clientid and work.pkg_orders.getBatchNumPart(batch) is not null
order by work.pkg_orders.getBatchNumPart(batch) desc)
where rownum=1;
end;

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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 7.44 7.44
********************************************************************************

SELECT WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH)
FROM
(SELECT BATCH FROM WORK.CLIENTORDER WHERE CLIENTID=:B1 AND
WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH) IS NOT NULL ORDER BY
WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH) DESC) WHERE ROWNUM=1


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 1 9.52 9.18 0 84 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 9.52 9.18 0 84 0 1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=18932 pr=0 pw=0 time=11467246 us)
1 VIEW (cr=18932 pr=0 pw=0 time=11467193 us)
1 SORT ORDER BY STOPKEY (cr=18932 pr=0 pw=0 time=11467181 us)
2273 TABLE ACCESS FULL CLIENTORDER (cr=9840 pr=0 pw=0 time=5501722 us)

Not sure if I was clear enough. Basically I'm confused about why I get these results:

Let Index A = Index on just the function (the FBI I originally asked you about)
Let Index B = Index on clientid and the function, clientid is leading edge (your suggestion)

no indexes index A index B
---------------- ------- -------
SQL Query: slow (full scan) fast (uses index A) fast (uses index B)
PL/SQL Block: slow (full scan) slow (full tbl scan) slow (full tbl scan)

The SQL Query
=============
variable x number
exec :x := 339
select work.pkg_orders.getBatchNumPart(batch) from
(select batch from work.clientorder
where clientid=:x and work.pkg_orders.getBatchNumPart(batch) is not null
order by work.pkg_orders.getBatchNumPart(batch) desc)
where rownum=1;

The PL/SQL Block
================
declare
l_batchnumpart number;
l_clientid number;
begin
l_clientid := 339;
select work.pkg_orders.getBatchNumPart(batch) into l_batchNumPart from
(select batch from work.clientorder
where clientid=l_clientid and work.pkg_orders.getBatchNumPart(batch) is not null
order by work.pkg_orders.getBatchNumPart(batch) desc)
where rownum=1;
end;
/

I would really like to understand why the two are considered different beasts to the optimizer. I will try to come up with a test case if this is not enough info. But I guess I'm trying to understand in a more abstract way, "Why would identical queries be given different plans, the only difference (that I know of) being that one is run from PL/SQL (and it has a 'select into' rather than just 'select')?"

Thanks,
Mark

Interesting. The SQL used ALL_ROWS while the PL/SQL block used CHOOSE. Experimenting, I added the /*+ all_rows */ hint to the queries, and the results are the same as before, aside from PL/SQL using the ALL_ROWS mode now. You can probably guess that I'm wondering why this happens. Now that the optimizer modes are the same, what is the difference? Should the optimizer still not know that my function is slow when I run the PL/SQL? But why does it know that fact when I run the SQL? Guess I'm just not "getting it". If you could refer me to an enlightening part of the documentation I would appreciate that...

Thanks,
Mark

TKProf Snippet: SQL Query
=========================
select /*+ all_rows */work.pkg_orders.getBatchNumPart(batch) from
(select batch from work.clientorder
where clientid=:x and work.pkg_orders.getBatchNumPart(batch) is not null
order by work.pkg_orders.getBatchNumPart(batch) desc)
where rownum=1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 659 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 0 659 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=659 pr=0 pw=0 time=2339 us)
1 VIEW (cr=659 pr=0 pw=0 time=2330 us)
1 TABLE ACCESS BY INDEX ROWID OBJ#(93098) (cr=659 pr=0 pw=0 time=2321 us)
1310 INDEX FULL SCAN DESCENDING OBJ#(94986) (cr=4 pr=0 pw=0 time=1366 us)(object id 94986)


TKProf Snippet: PL/SQL Block
============================
SELECT /*+ all_rows */WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH)
FROM
(SELECT BATCH FROM WORK.CLIENTORDER WHERE CLIENTID=:B1 AND
WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH) IS NOT NULL ORDER BY
WORK.PKG_ORDERS.GETBATCHNUMPART(BATCH) DESC) WHERE ROWNUM=1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 0 0 0
Fetch 1 8.93 8.84 0 84 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 8.95 8.85 0 84 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=19264 pr=0 pw=0 time=10839814 us)
1 VIEW (cr=19264 pr=0 pw=0 time=10839786 us)
1 SORT ORDER BY STOPKEY (cr=19264 pr=0 pw=0 time=10839776 us)
2273 TABLE ACCESS FULL CLIENTORDER (cr=10172 pr=0 pw=0 time=5141226 us)

I have read from your site somewhere that it does not matter
who owns the index, the index can still be accessed

I once created a unique index IDX1 logged as SYS for
table T1 owned by USER1. Then created a primary key fot T1
which used this index IDX1. The users of the application
were using TOAD and could not automatically find the
primary key. When IDX1 was created by USER1, no problem.

My question is,
1. is this situation specific because SYS was used.
2. In general can any index be created as logged as another
user , USER2 and will not encounter any problem
( this can be done by giving USER2 "select all on table
T1 owned by USER1 )

SQL> set autotrace traceonly exp

SQL> select ename,dname
  2  from emp e,dept d
  3  where to_char(e.deptno) = to_char(d.deptno);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=252)
   1    0   HASH JOIN (Cost=5 Card=14 Bytes=252)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44)

SQL> create index scott.idx_1
  2  on scott.dept (to_char(deptno));

Index Created.

SQL> analyze table dept compute statistics for table for all indexes;

Table analyzed.

SQL> select /*+ index(d idx_1) */
  2         ename,dname
  3  from   emp e,dept d
  4  where  to_char(e.deptno) = to_char(d.deptno);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=19 Bytes=342)
   1    0   HASH JOIN (Cost=5 Card=19 Bytes=342)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)

Can you please explain why the function index is not being used? 

because it did not have to - it did not want to, it would not make sense to.  You'd have to get a bit more specific with your hints.  

And just be thankful it didn't, it would be, well, not the right way to do it.

ops$tkyte%ORA9IR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA9IR2> create table dept as select * from scott.dept;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'EMP' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'DEPT' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create index idx_1
  2  on dept (to_char(deptno));

Index created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> @at
ops$tkyte%ORA9IR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select /*+ full(e) ordered use_nl(d) index(d idx_1) */
  2         ename,dname
  3  from   emp e,dept d
  4  where  to_char(e.deptno) = to_char(d.deptno);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=19 Bytes=418)
   1    0   NESTED LOOPS (Cost=30 Card=19 Bytes=418)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=126)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=1 Bytes=13)
   4    3       INDEX (RANGE SCAN) OF 'IDX_1' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte%ORA9IR2> set autotrace off
 

Hi Tom,

We have been running a program for more than a year that started misbehaving lately, the second create table lasted about 8 hours in comparison of less than 5 minutes before.

the hint for the spatial index was ignored. Why ? There was no significant change to the system.

In the documentation i read that the ordered hint should be used and ist worked. Why is an index hint not sufficient? why is any hint necessary? My experience with spatial is little, can you provide links for me to read about tuning spatial queries?

Thanks in advance,
martina

def gi_kust_fa_gis_fil = mpa_kust_fa_gis_fil
def gi_kust_filezg_link_roh = mpa_kust_filezg_link_roh
drop table &gi_kust_fa_gis_fil;
drop table &gi_kust_filezg_link_roh;
def firma = '00'
def filiale =  1735
create table &gi_kust_fa_gis_fil nologging as
select '&firma.' firma, &filiale filiale, kunden_nr, geoloc
from gi_kust_fa_gis
where firma = '&firma.'
and zsp_nr in (select b.zsp_nr
from gi_filezg    a,  gi_filezg_zsp b 
where a.firma          = '&firma.'
and a.filiale        = &filiale.
and a.firma          = b.firma
and a.filiale        = b.filiale
and a.ref_ezg_art    = b.ezg_art
and a.ref_ezg_method = b.ezg_method
and a.ref_ezg_wert   = b.ezg_wert);
CREATE INDEX &gi_kust_fa_gis_fil._SX 
      ON &gi_kust_fa_gis_fil (GEOLOC) 
      INDEXTYPE IS MDSYS.SPATIAL_INDEX;
analyze table &gi_kust_fa_gis_fil estimate statistics;
explain plan for
create table &gi_kust_filezg_link_roh nologging as
select --+ index (a &gi_kust_fa_gis_fil._sx)
a.kunden_nr, a.firma, b.filiale, b.ezg_grp, min(b.ezg_id) ezg_id
,min(round(sdo_geom.sdo_distance(d.geoloc, a.geoloc,0.1),2)) entfernung_ll
from &gi_kust_fa_gis_fil a
     ,gi_filezg          b,
      gi_filezg_basis    c,
      gi_filst_gis       d
where b.firma          = c.firma
and b.filiale          = c.filiale
and b.ref_ezg_art      = c.ezg_art
and b.ref_ezg_method   = c.ezg_method
and b.ref_ezg_wert   = c.ezg_wert          
and c.firma          = d.firma
and c.filiale        = d.filiale
and d.firma = '&firma'
and d.filiale        = &filiale.
and sdo_relate(a.geoloc, c.geoloc
  , 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE' 
group by a.kunden_nr, a.firma, d.filiale,b.ezg_grp;

Operation                         | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)
------------------------------------------------------------------------------------------------
CREATE TABLE STATEMENT            |                        |  4554 |   942K|       |   329  (25)
 LOAD AS SELECT                   | MPA_KUST_FILEZG_LINK_R |       |       |       |
  HASH GROUP BY                   |                        |  4554 |   942K|  2128K|   308  (27)
   HASH JOIN                      |                        |  4554 |   942K|       |   172  (46)
    TABLE ACCESS BY INDEX ROWID   | GI_FILEZG              |    17 |   510 |       |     4   (0)
     INDEX RANGE SCAN             | GI_FILEZG_PK           |    17 |       |       |     2   (0)
    MERGE JOIN                    |                        | 14178 |  2519K|       |   167  (47)
     NESTED LOOPS                 |                        | 84812 |     9M|       |    97  (16)
      TABLE ACCESS BY INDEX ROWID | GI_FILST_GIS           |     1 |    82 |       |     2   (0)
       INDEX UNIQUE SCAN          | GI_FILST_GIS_PK        |     1 |       |       |     1   (0)
      TABLE ACCESS FULL           | MPA_KUST_FA_GIS_FIL    | 84812 |  3395K|       |    95  (16)
     FILTER                       |                        |       |       |       |
      SORT JOIN                   |                        |    17 |  1003 |       |     9  (12)
       TABLE ACCESS BY INDEX ROWID| GI_FILEZG_BASIS        |    17 |  1003 |       |     8   (0)
        INDEX RANGE SCAN          | GI_FILEZG_BASIS_PK     |    17 |       |       |     2   (0)

New create:

INTMAT_PROD> explain plan set statement_id = 'Q1' for
create table &gi_kust_filezg_link_roh as
select --+ ordered
       a.kunden_nr,
       a.firma,
       b.filiale,
       b.ezg_grp,
       min(b.ezg_id) ezg_id
       ,min(round(sdo_geom.sdo_distance(d.geoloc, a.geoloc,0.1),2)) entfernung_ll
  from gi_filst_gis          d,
       gi_filezg_basis    c,
       gi_filezg          b,
       &gi_kust_fa_gis_fil a
 where b.firma          = c.firma
   and b.filiale        = c.filiale
   and b.ref_ezg_art    = c.ezg_art
   and b.ref_ezg_method = c.ezg_method
   and b.ref_ezg_wert   = c.ezg_wert
   and c.firma          = d.firma
   and c.filiale        = d.filiale
   and d.firma          = '&firma'
   and d.filiale        = &filiale.
   and sdo_relate(a.geoloc, c.geoloc, 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'
 group by a.kunden_nr,
          a.firma,
          b.filiale,
          b.ezg_grp;
Operation                        | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)
-----------------------------------------------------------------------------------------------
CREATE TABLE STATEMENT           |                        |  4554 |   942K|       |   512   (2)
 LOAD AS SELECT                  | MPA_KUST_FILEZG_LINK_R |       |       |       |
  HASH GROUP BY                  |                        |  4554 |   942K|  2160K|   490   (2)
   NESTED LOOPS                  |                        |  4554 |   942K|       |   355   (2)
    HASH JOIN                    |                        |     5 |   855 |       |    14   (8)
     NESTED LOOPS                |                        |    17 |  2397 |       |     9   (0)
      TABLE ACCESS BY INDEX ROWID| GI_FILST_GIS           |     1 |    82 |       |     2   (0)
       INDEX UNIQUE SCAN         | GI_FILST_GIS_PK        |     1 |       |       |     1   (0)
      TABLE ACCESS BY INDEX ROWID| GI_FILEZG_BASIS        |    17 |  1003 |       |     7   (0)
       INDEX RANGE SCAN          | GI_FILEZG_BASIS_PK     |    17 |       |       |     1   (0)
     TABLE ACCESS BY INDEX ROWID | GI_FILEZG              |    17 |   510 |       |     4   (0)
      INDEX RANGE SCAN           | GI_FILEZG_PK           |    17 |       |       |     2   (0)
    TABLE ACCESS BY INDEX ROWID  | MPA_KUST_FA_GIS_FIL    |   848 | 34768 |       |   355   (2)
     DOMAIN INDEX                | MPA_KUST_FA_GIS_FIL_SX |       |       |       |

Hi Tom

I realize this is an older thread but my question about index access seems to fit. I've searched dozens of other threads here and on several OTN Forums but haven't found the light yet.

Environment: Oracle 10.2.0.2 EE on AIX 5L

I have a table containing about 1 million rows and is one of our fact tables for the Data Warehouse. The PK on the table contains about 6 columns and is lead by the DAY_ID. This particular table only holds data for the current month so there will be a maximum of 31 distinct DAY_ID values.

The data gets loaded pretty much by DAY_ID based on the final aggregation of data before the INSERT.

The table has current statistics and the PK is enforced by a regular B*Tree index.

When I do a simple query to get the maximum DAY_ID it takes only 3 consistent gets from the PK index as seen here:

select max(day_id) from INV_TRANS_P2_PM;

MAX(DAY_ID)
-----------
     248093

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3423341591

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                    |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                    |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| INV_TRANS_P2_PM_PK |  1098K|  5362K|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


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

When I do the same query looking for the minimum value the query takes over 17,000 consistent gets as seen here:

select min(day_id) from INV_TRANS_P2_PM;

MIN(DAY_ID)
-----------
     248064

1 row selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 3423341591

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                    |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                    |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| INV_TRANS_P2_PM_PK |  1098K|  5362K|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


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

The queries were run several times, one right after the other to load the buffer cache and basically eliminate the physical reads.

I have run the two queries many times and the MAX function always takes 3 consistent gets and the MIN takes thousands.

Is there something with the way the index is created or accessed?

Are the MAX and MIN values retrieved differently? I would have assumed that each 'end' of the index would be equally accessible.

What would cause this behavior?

Thanks very much!!!

-gary

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select rownum id, rpad('*',40,'*') data, a.*
  4    from all_objects a
  5   where rownum <= 5000
  6  /

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(id,data);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select max(id) from t;

   MAX(ID)
----------
      5000

ops$tkyte%ORA10GR2> select min(id) from t;

   MIN(ID)
----------
         1

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select max(id) from t;


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

ops$tkyte%ORA10GR2> select min(id) from t;


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

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> delete from t where id <= 4900;

4900 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select max(id) from t;


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

ops$tkyte%ORA10GR2> select min(id) from t;


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

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select rownum id, rpad('*',40,'*') data, a.*
  2    from all_objects a
  3   where rownum <= 5000
  4  /

5000 rows created.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select max(id) from t;


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

ops$tkyte%ORA10GR2> select min(id) from t;


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

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>

Tom, 

I have a very simple query

SELECT call_req.open_date, call_req.ref_num, call_req.template_name, 
  call_req.id 
FROM
 call_req WHERE call_req.problem = 'cr:546002' ORDER BY call_req.open_date 
  DESC

The table call_req has all proper statistics (even histograms) and it has has an index on column 'PROBLEMS':

SQL> select last_analyzed, blocks, num_rows from dba_tables where table_name like 'CALL_REQ'
  2  ;
¿LAST_ANAL     BLOCKS   NUM_ROWS
--------- ---------- ----------
25-JUL-08      31517     139630

SQL> select num_distinct, density, num_nulls, num_buckets, histogram from dba_tab_col_statistics where table_name like 'CALL_REQ' and COLUMN_name like 'PROBLEM';
¿NUM_DISTINCT   DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------ ---------- ---------- ----------- ---------------
          50  .00462963     139522         108 HEIGHT BALANCED

SQL> select distinct_keys, clustering_factor from dba_indexes where index_name like 'CALL_REQ_X18';
¿DISTINCT_KEYS CLUSTERING_FACTOR
------------- -----------------
           50               101

Column has 139630 rows from which 139522 are NULLs. 

Index was created on this column only:

SQL> select index_name, column_name, column_position from dba_ind_columns where table_name like 'CALL_REQ' and column_name like 'PROBLEM';
¿INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------
COLUMN_POSITION
---------------
CALL_REQ_X18
PROBLEM
              1

End users complains about bad performance of this query and trace file shows that optimizer choose full table scan which leads to a lot of phys reads.


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      2.31       4.31      24541      32197          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.32       4.31      24541      32197          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27  (MDBADMIN)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=32197 pr=24541 pw=0 time=4311992 us)
      0   TABLE ACCESS FULL CALL_REQ (cr=32197 pr=24541 pw=0 time=4311935 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   SORT (ORDER BY)
      0    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'CALL_REQ' 
               (TABLE)
      0     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'CALL_REQ_X18' (INDEX)


What could be a reason Oracle prefer to do a full table scan here?

maybe datatype of call_req.problem ?

Tom, 

Yes it seems to be a datatype mismatch (see my original question above), but could you explain why oracle implicit applies nlssort and hextoraw funcions? Column PROBLEMS is NVARCHAR2(30)

select t.* from v$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number)) t where s.sql_id = '5cw90638cykay'

SQL_ID  5cw90638cykay, child number 0
-------------------------------------
SELECT call_req.open_date, call_req.ref_num, call_req.template_name,
call_req.id FROM call_req WHERE call_req.problem = 'cr:550087' ORDER BY
call_req.open_date DESC

Plan hash value: 2557942924

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |  7252 (100)|          |
|   1 |  SORT ORDER BY     |          |     2 |    60 |  7252   (5)| 00:01:28 |

|*  2 |   TABLE ACCESS FULL| CALL_REQ |     2 |    60 |  7251   (5)| 00:01:28 |
-------------------------------------------------------------------------------

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

   2 - filter(NLSSORT("CALL_REQ"."PROBLEM",'nls_sort=''BINARY_CI''')=HEX
              TORAW('00630072003A00350035003000300038003700') )

SQL> select * from nls_database_parameters;

NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.2.0

somewhere in your client environment (either via OS env or alter session) you must be setting

NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI

Check this note 227335.1

Tom,

How I can see the nls_settings of someone else session. I know how to find my session settings by:

select * from nls_session_parameters where parameter like 'NLS_COMP' or parameter like 'NLS_SORT';

but how I can see the setting for any session I see in the v$session (I think we have an issue with different clients having different environment (set either in sql session or on the os level).

Tom:

I have two tables

The domain table has two columns:

Category_id number, category_type_string(cat_string) varchar2(4000), category_indicator.

The category type is a string with '|' delimited.
for eg: 1|2|3|4|5| All possible categories that a given categoryid can have...

insert into category (category_id, cat_string, category_indicator) values (100,'|1|2|3|4|',0);

insert into category (category_id, cat_string, category_indicator) values (200,'|1|2|',1);


The transaction table has

FE_TRANS:

Category_id number, category_type number, category_rec_flg varchar2(1) and other columns.

The record structure will be

insert into fe_trans(category_id,category_type,category_rec_flg) values
(100, 1,NULL);

insert into fe_trans(category_id,category_type,category_rec_flg) values
(100, 2,NULL);

insert into fe_trans(category_id,category_type,category_rec_flg) values
(100, 10,NULL);





We have a function based index on FE_TRANS

Decode(category_rec_flg,NULL,1,NULL),
Decode(category_rec_flg,NULL,Category_id,NULL)

I want to now add another column to the function based index


Decode(category_rec_flg,NULL,1,NULL),
Decode(category_rec_flg,NULL,Category_id,NULL)
Decode(categroy_rec_flg,NULL,category_type,NULL);



When writing the query:


select count(*) from category,
fe_Trans
where
Decode(category_rec_flg,NULL,1,NULL) = 1 and
Decode(category_rec_flg,NULL,Category_id,NULL)=category.category_id and
(case when category_ind=0 and (Decode(category_rec_flg,NULL,category_type,NULL)
in (select * from THE ( select cast( se_strtoken_fn(category.cat_string) as senumtableType ) from dual) then 1
when category_ind=1 other logic ) =1

is not using index on category_type. The first two columns are used and the INLIST interator is applied last. How can I force the index on all the three columns...


Decode(category_rec_flg,NULL,1,NULL),
Decode(category_rec_flg,NULL,Category_id,NULL)
Decode(categroy_rec_flg,NULL,category_type,NULL);