[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: RE: function-based index
From: <Ajay_K_Garg () Dell ! com>
Date: 2004-05-13 11:52:10
Message-ID: F001.005E495A.20040513043510 () fatcity ! com
[Download RAW message or body]
Justin
My query started using the index after i analyzed the table.
Thanks
Regards
Ajay
-----Original Message-----
Justin Cave (DDBC)
Sent: Wednesday, May 12, 2004 11:44 PM
To: Multiple recipients of list ODTUG-SQLPLUS-L
Note that the emp table, unless you've added a lot of data, will
never use any index if the statistics are correct. The CBO knows that
it is much easier to do a full table scan and get all 10 rows than it
would be to use an index and have to do a couple of scattered reads to
get the rows.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC <http://www.ddbcinc.com/askDDBC>
_____
From: ml-errors@fatcity.com [mailto:ml-errors@fatcity.com] On
Behalf Of Ajay_K_Garg@Dell.com
Sent: Wednesday, May 12, 2004 3:34 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
Subject: FW: function-based index
Please consider the following example :-
>SELECT UPPER(ENAME) FROM EMP
where UPPER(ENAME) = 'AJAY';
UPPER(ENAM
----------
AJAY
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'
Regards
Ajay K. Garg
Global Devt. Centre
Dell International Services
Bangalore, India
Austin Dial-in : 753-74677
Phone : 080-28419110 Ext: 77646/77645
Mobile : 080-56822544
-----Original Message-----
From: Garg, Ajay K - Authorised Dell Representative
Sent: Wednesday, May 12, 2004 12:48 PM
To: kishore.hejmady@wipro.com
Subject: function-based index
Hi
I am trying to use function-based index .
To create a function-based index in your own schema on
your own table, in addition to the prerequisites for creating a
conventional index, you must have the QUERY REWRITE system privilege.
To create the index in another schema or on another
schema's table, you must have the GLOBAL QUERY REWRITE privilege.
In both cases, the table owner must also have the
EXECUTE object privilege on the function(s) used in the function-based
index.
In addition, in order for Oracle to use function-based
indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to
TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED.
For this, these are the steps I followed:-
1. AS DBA
10:57:39 SYS>GRANT QUERY REWRITE TO SCOTT;
2. AS NORMAL USER
10:51:27 SCOTT>create index ind_func_demo on emp(
upper(ename));
Index created.
INDEX_NAME COLUMN_NAME
UNIQUENES
------------------------------
----------------------------------- ---------
IND_FUNC_DEMO SYS_NC00009$
NONUNIQUE
10:58:42 SCOTT@wwops>SELECT * FROM ALL_IND_EXPRESSIONS;
INDEX_OWNER INDEX_NAME
TABLE_OWNER TABLE_NAME
------------------------------
------------------------------ ------------------------------
---------------------------
COLUMN_EXPRESSION
COLUMN_POSITION
------------------------------------------------------------------------
-------- ---------------
SCOTT IND_FUNC_DEMO
SCOTT EMP
UPPER("ENAME")
1
3. AS DBA
10:57:48 SYS>SHO PARAMETER QUERY_REWRITE_
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
query_rewrite_enabled string FALSE
query_rewrite_integrity string
enforced
11:00:01 SYS>
11:02:06 SYS>ALTER SYSTEM SET query_rewrite_enabled =
'TRUE';
System altered.
Elapsed: 00:00:00.04
11:20:04 SYS>ALTER SYSTEM SET query_rewrite_integrity =
'TRUSTED' ;
System altered.
Elapsed: 00:00:00.00
11:21:08 SYS>SHO PARAMETER QUERY_REWRITE_
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string TRUSTED
4. AS NORMAL USER
11:23:02 SCOTT>SET AUTOTRACE TRACEONLY EXP
11:23:14 SCOTT>SELECT UPPER(ENAME) FROM EMP;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'
As you can see, even after setting the
QUERY_REWRITE_ENABLED parameter to TRUE, and the QUERY_REWRITE_INTEGRITY
parameter to TRUSTED, the function based index is not being used, and
the full table is being accessed.
I also tried closing the current session of scott, and
starting a new session after step 3.
What am I missing here?
Tia
Regards
Ajay
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2734.1600" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=529033311-13052004><FONT face=Arial color=#0000ff
size=2>Justin</FONT></SPAN></DIV>
<DIV><SPAN class=529033311-13052004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=529033311-13052004><FONT face=Arial color=#0000ff size=2>My
query started using the index after i analyzed the table.</FONT></SPAN></DIV>
<DIV><SPAN class=529033311-13052004><FONT face=Arial color=#0000ff
size=2>Thanks</FONT></SPAN></DIV>
<DIV><SPAN class=529033311-13052004><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN lang=en-us><FONT face=Arial size=2>Regards</FONT></SPAN> </DIV>
<P><SPAN lang=en-us><FONT face=Arial><FONT size=2>Ajay <SPAN
class=529033311-13052004><FONT
color=#0000ff> </FONT></SPAN></FONT></FONT></SPAN></P>
<P><SPAN lang=en-us><FONT face=Arial><FONT size=2><SPAN
class=529033311-13052004> </SPAN></FONT></FONT></SPAN><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> ml-errors@fatcity.com
[mailto:ml-errors@fatcity.com] <B>On Behalf Of </B>Justin Cave
(DDBC)<BR><B>Sent:</B> Wednesday, May 12, 2004 11:44 PM<BR><B>To:</B> Multiple
recipients of list ODTUG-SQLPLUS-L<BR><B>Subject:</B> RE: function-based
index<BR><BR></P></FONT>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV dir=ltr align=left><SPAN class=406111117-12052004><FONT face=Arial
color=#0000ff size=2>Note that the emp table, unless you've added a lot of
data, will never use any index if the statistics are correct. The CBO
knows that it is much easier to do a full table scan and get all 10 rows than
it would be to use an index and have to do a couple of scattered reads to get
the rows.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV align=left><FONT face=Arial size=2>Justin Cave</FONT></DIV>
<DIV align=left><FONT face=Arial size=2>Distributed Database Consulting,
Inc.</FONT></DIV>
<DIV align=left><FONT face=Arial><FONT size=2><A
href="http://www.ddbcinc.com/askDDBC">http://www.ddbcinc.com<SPAN
class=406111117-12052004>/askDDBC</A></SPAN></FONT></FONT></DIV>
<DIV> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> ml-errors@fatcity.com
[mailto:ml-errors@fatcity.com] <B>On Behalf Of
</B>Ajay_K_Garg@Dell.com<BR><B>Sent:</B> Wednesday, May 12, 2004 3:34
AM<BR><B>To:</B> Multiple recipients of list
ODTUG-SQLPLUS-L<BR><B>Subject:</B> FW: function-based
index<BR></FONT><BR></DIV>
<DIV></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial color=#0000ff size=2>Please consider the following example
:-</FONT> </P><BR>
<P><FONT face=Arial color=#0000ff size=2>>SELECT UPPER(ENAME) FROM
EMP</FONT> <BR><FONT face=Arial color=#0000ff size=2>where UPPER(ENAME) =
'AJAY';</FONT> </P>
<P><FONT face=Arial color=#0000ff size=2>UPPER(ENAM</FONT> <BR><FONT
face=Arial color=#0000ff size=2>----------</FONT> <BR><FONT face=Arial
color=#0000ff size=2>AJAY</FONT> </P>
<P><FONT face=Arial color=#0000ff size=2>Elapsed: 00:00:00.00</FONT> </P>
<P><FONT face=Arial color=#0000ff size=2>Execution Plan</FONT> <BR><FONT
face=Arial color=#0000ff
size=2>----------------------------------------------------------</FONT>
<BR><FONT face=Arial color=#0000ff size=2>
0 SELECT STATEMENT Optimizer=CHOOSE</FONT>
<BR><FONT face=Arial color=#0000ff size=2> 1
0 TABLE ACCESS (FULL) OF 'EMP'</FONT> </P><BR>
<P><FONT face=Arial size=2>Regards</FONT> </P>
<P><FONT face=Arial size=2>Ajay K. Garg</FONT> <BR><FONT face=Arial
size=2>Global Devt. Centre</FONT> <BR><FONT face=Arial size=2>Dell
International Services</FONT> <BR><FONT face=Arial size=2>Bangalore,
India</FONT> </P>
<P><FONT face=Arial size=2>Austin Dial-in :
753-74677<B></B></FONT><B></B><B></B> <BR><FONT face=Arial size=2>Phone :
080-28419110 Ext: 77646/77645</FONT> <BR><FONT face=Arial size=2>Mobile :
080-56822544</FONT> </P>
<UL>
<P><FONT face=Arial></FONT> <FONT face=Tahoma size=1>-----Original
Message-----</FONT> <BR><B><FONT face=Tahoma size=1>From: </FONT></B>
<FONT face=Tahoma size=1>Garg, Ajay K - Authorised Dell Representative
</FONT><BR><B><FONT face=Tahoma size=1>Sent: </FONT></B> <FONT
face=Tahoma size=1>Wednesday, May 12, 2004 12:48 PM</FONT> <BR><B><FONT
face=Tahoma size=1>To: </FONT></B> <FONT face=Tahoma
size=1>kishore.hejmady@wipro.com</FONT> <BR><B><FONT face=Tahoma
size=1>Subject: </FONT></B> <FONT
face=Tahoma size=1>function-based index</FONT> </P>
<P><FONT face=Arial size=2>Hi </FONT></P>
<P><FONT face=Arial size=2>I am trying to use</FONT><B></B><B> <FONT
face=Verdana size=2>function-based index .</FONT></B> </P><BR>
<P><B><FONT face=Verdana size=2>To create a function-based index</FONT></B>
<FONT face=Verdana size=2>in your own schema on your own table, in addition
to the prerequisites for creating a conventional index, you must have the
QUERY REWRITE system privilege. </FONT></P>
<P><FONT face=Verdana size=2>To create the index in another schema or on
another schema's table, you must have the GLOBAL QUERY REWRITE privilege.
</FONT></P>
<P><FONT face=Verdana size=2>In both cases, the table owner must also have
the EXECUTE object privilege on the function(s) used in the function-based
index. </FONT></P>
<P><FONT face=Verdana size=2>In addition, in order for Oracle to use
function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must
be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to
TRUSTED.</FONT></P><BR>
<P><B><FONT face=Verdana size=2>For this, these are the steps I
followed:-</FONT></B> </P>
<P><B><FONT face=Verdana size=2>1.</FONT></B><FONT face=Verdana
size=2></FONT><B> <FONT face=Verdana size=2>AS DBA
</FONT></B><BR><B> </B> <FONT
face=Verdana size=2>10:57:39 SYS>GRANT QUERY REWRITE TO SCOTT;</FONT>
</P><BR>
<P><B><FONT face=Arial size=2>2. AS NORMAL USER</FONT></B> </P>
<P><FONT face=Arial size=2>10:51:27 SCOTT>create index ind_func_demo on
emp( upper(ename));</FONT> </P>
<P><FONT face=Arial size=2>Index created.</FONT> </P><BR>
<P><FONT face=Arial
size=2>INDEX_NAME \
COLUMN_NAME \
UNIQUENES</FONT> <BR><FONT face=Arial size=2>------------------------------
----------------------------------- ---------</FONT> <BR><FONT face=Arial
size=2>IND_FUNC_DEMO \
SYS_NC00009$ \
NONUNIQUE</FONT> </P><BR>
<P><FONT face=Arial size=2>10:58:42 SCOTT@wwops>SELECT * FROM
ALL_IND_EXPRESSIONS;</FONT> </P>
<P><FONT face=Arial
size=2>INDEX_OWNER \
INDEX_NAME \
TABLE_OWNER \
TABLE_NAME</FONT> <BR><FONT face=Arial size=2>------------------------------
------------------------------ ------------------------------
---------------------------</FONT></P>
<P><FONT face=Arial
size=2>COLUMN_EXPRESSION &nbs \
p; \
&n \
bsp; \
COLUMN_POSITION</FONT> <BR><FONT face=Arial
size=2>-------------------------------------------------------------------------------- \
---------------</FONT> <BR><FONT face=Arial
size=2>SCOTT \
IND_FUNC_DEMO \
SCOTT \
EMP</FONT> <BR><FONT face=Arial
size=2>UPPER("ENAME") & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
1</FONT> </P><BR><BR>
<P><B><FONT face=Verdana size=2>3. AS DBA</FONT></B> </P>
<P><FONT face=Verdana size=2>10:57:48 SYS>SHO PARAMETER
QUERY_REWRITE_</FONT> </P>
<P><FONT face=Verdana
size=2>NAME   \
; \
TYPE VALUE</FONT> <BR><FONT
face=Verdana size=2>------------------------------------ -----------
------------------------------</FONT> <BR><FONT face=Verdana
size=2>query_rewrite_enabled \
string FALSE</FONT> <BR><FONT face=Verdana
size=2>query_rewrite_integrity \
string enforced</FONT> <BR><FONT face=Verdana
size=2>11:00:01 SYS></FONT> <BR><FONT face=Verdana size=2>11:02:06
SYS>ALTER SYSTEM SET query_rewrite_enabled = 'TRUE';</FONT> </P>
<P><FONT face=Verdana size=2>System altered.</FONT> </P>
<P><FONT face=Verdana size=2>Elapsed: 00:00:00.04</FONT> <BR><FONT
face=Verdana size=2>11:20:04 SYS>ALTER SYSTEM SET query_rewrite_integrity
= 'TRUSTED' ;</FONT> </P>
<P><FONT face=Verdana size=2>System altered.</FONT> </P>
<P><FONT face=Verdana size=2>Elapsed: 00:00:00.00</FONT> <BR><FONT
face=Verdana size=2>11:21:08 SYS>SHO PARAMETER QUERY_REWRITE_</FONT> </P>
<P><FONT face=Verdana
size=2>NAME   \
; \
TYPE VALUE</FONT> <BR><FONT
face=Verdana size=2>------------------------------------ -----------
------------------------------</FONT> <BR><FONT face=Verdana
size=2>query_rewrite_enabled \
string TRUE</FONT> <BR><FONT face=Verdana
size=2>query_rewrite_integrity \
string TRUSTED</FONT> </P><BR><BR><BR>
<P><B><FONT face=Arial size=2>4. AS NORMAL USER</FONT></B> </P>
<P><FONT face=Arial size=2>11:23:02 SCOTT>SET AUTOTRACE TRACEONLY
EXP</FONT> <BR><FONT face=Arial size=2>11:23:14 SCOTT>SELECT UPPER(ENAME)
FROM EMP;</FONT> <BR><FONT face=Arial size=2>Elapsed: 00:00:00.00</FONT>
</P>
<P><FONT face=Arial size=2>Execution Plan</FONT> <BR><FONT face=Arial
size=2>----------------------------------------------------------</FONT>
<BR><FONT face=Arial size=2> 0
SELECT STATEMENT Optimizer=CHOOSE</FONT> <BR><FONT face=Arial
size=2> 1 0 TABLE ACCESS (FULL) OF
'EMP'</FONT> </P><BR><BR>
<P><B><I><FONT face=Arial size=2>As you can see, even after setting</FONT>
<FONT face=Verdana size=2>the QUERY_REWRITE_ENABLED parameter to TRUE, and
the QUERY_REWRITE_INTEGRITY parameter to TRUSTED, the function based index
is not being used, and the full table is being accessed.</FONT></I></B></P>
<P><B><I><FONT face=Arial size=2>I also tried closing the current session of
scott, and starting a new session after step 3.</FONT></I></B> </P>
<P><B><I><FONT face=Verdana size=2>What am I missing here?</FONT></I></B>
</P>
<P><FONT face=Arial size=2>Tia</FONT> </P>
<P><FONT face=Arial size=2>Regards</FONT> </P>
<P><FONT face=Arial size=2>Ajay </FONT></P></UL></BLOCKQUOTE></BODY></HTML>
--
ODTUG Now! 2004, Let Us Show you How! June 20-24, 2004 at the Westin Kierland
Resort, Scottsdale, Arizona. Visit http://www.odtug.com for details.--
Author: <Ajay_K_Garg@Dell.com
INET: Ajay_K_Garg@Dell.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic