RE: oracle-l Digest V3 #375

From: gao gaozhiwen <nmgzw_at_hotmail.com>
Date: Fri, 22 Dec 2006 08:09:53 +0000
Message-ID: <BAY113-F2E4A8FB209FBB6FC26F24A6CD0@phx.gbl>

I think because your nls_comp value is ansi at present,not set it to ansi.You should set nls_comp=binary!

THANKS!!!! EXAMPLE:
SQL> alter session set nls_comp=binary;

Session altered.

SQL> explain plan for select * from employees where first_name='gzw';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

Plan hash value: 1445457117

| 0 | SELECT STATEMENT | | 1 | 68 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 68 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("FIRST_NAME"='gzw')

13 rows selected.

SQL> alter session set nls_comp=binary;

Session altered.

SQL> explain plan for select * from employees where first_name='gzw';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

Plan hash value: 1445457117

| 0 | SELECT STATEMENT | | 1 | 68 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 68 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("FIRST_NAME"='gzw')

13 rows selected.

SQL> alter session set nls_comp=binary;

Session altered.

SQL> explain plan for select * from employees where first_name='gzw';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

Plan hash value: 1445457117

| 0 | SELECT STATEMENT | | 1 | 68 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 68 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("FIRST_NAME"='gzw')

13 rows selected.

>From: FreeLists Mailing List Manager <ecartis_at_freelists.org>
>Reply-To: oracle-l_at_freelists.org
>To: oracle-l digest users <oracle-l_at_freelists.org>
>Subject: oracle-l Digest V3 #375
>Date: Fri, 22 Dec 2006 02:11:20 -0500 (EST)
>
>oracle-l Digest Fri, 22 Dec 2006 Volume: 03 Issue: 375
>
>In This Issue:
> RE: Why isn't Oracle Using My Index
>
>----------------------------------------------------------------------
>
>Subject: RE: Why isn't Oracle Using My Index
>Date: Thu, 21 Dec 2006 23:07:54 -0800
>From: "William Wagman" <wjwagman_at_ucdavis.edu>
>
>Wolfgang,
>
>nls_sort=GENERIC_BASELETTER is set in the database and, as you noted,
>nls_comp=ANSI. I believe that was done by the vendor but I don't know
>why. I have asked. But your duplication is exactly what I am seeing.
>Thanks.
>
>Bill Wagman
>Univ. of California at Davis
>IET Campus Data Center
>wjwagman_at_ucdavis.edu
>(530) 754-6208
>
>
>
>________________________________
>
>From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]
>Sent: Thursday, December 21, 2006 9:48 PM
>To: William Wagman
>Cc: oracle-l_at_freelists.org
>Subject: RE: Why isn't Oracle Using My Index
>
>
>What I don't understand is why the simple statement
>
>SELECT C240000008 FROM aradmin.t185 WHERE C1 = 'HD0000000041608'
>
>requires the nlssort filter in the first place. Can you share the ddl
>(gotten from dbms_metadata) for the table and its indexes with us.
>
>If is set NLS_SORT = GENERIC_BASELETTER and run a similar statement on
>the following table:
>
>CREATE TABLE "SCOTT"."M1"
>( "ID" VARCHAR2(13),
>"UNIFORM" VARCHAR2(13),
>"RANDOM" VARCHAR2(13),
>"FILLER" VARCHAR2(4000)
>) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
>STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
>PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
>TABLESPACE "USERS"
>
>
>
> CREATE UNIQUE INDEX "SCOTT"."M1_IX" ON "SCOTT"."M1" ("ID")
> PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "USERS"
>
>22:26:44 ora101.scott> explain plan for select random from m1 where
>id='000000000550';
>
>Explained.
>
>22:27:11 ora101.scott> select * from table(dbms_xplan.display);
>
>PLAN_TABLE_OUTPUT
>------------------------------------------------------------------------
>------------------
>Plan hash value: 2146983540
>
>------------------------------------------------------------------------
>-------------
>| Id | Operation | Name | Rows | Bytes | Cost
>(%CPU)| Time |
>------------------------------------------------------------------------
>-------------
>| 0 | SELECT STATEMENT | | 1 | 26 | 2
>(0)| 00:00:01 |
>| 1 | TABLE ACCESS BY INDEX ROWID| M1 | 1 | 26 | 2
>(0)| 00:00:01 |
>|* 2 | INDEX UNIQUE SCAN | M1_IX | 1 | | 1
>(0)| 00:00:01 |
>------------------------------------------------------------------------
>-------------
>
>Predicate Information (identified by operation id):
>---------------------------------------------------
>
> 2 - access("ID"='000000000550')
>
>I get the index use and an access predicate rather than a filter
>predicate. There is obviously more to the situation than your simple
>introduction pretends. Note also that in my testcase the optimizer
>correctly predicts a cardinality of 1 for the resultset.
>
>At 04:02 PM 12/21/2006, William Wagman wrote:
>
>
> Greetings,
>
> Thanks to Riyajh Shamsudeen for pointing out to me that NLS_SORT
>is in part the culprit. In our database NLS_SORT = GENERIC_BASELETTER.
>According to the Oracle documentation this forces a full table scan. I
>set nls_sort=binary and the query used the indexes. I still don't fully
>understand what is going on and need to do some further reading. I also
>need to turn on cpu_costing and see if that will resolve the problem if
>nls_sort is left at it's current setting. Thanks to all who responded, I
>truly appreciate the help.
>
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
>
>------------------------------------------------------------------------
>--------
>
>
>--------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
>Cost |
>
>--------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 416 | 226K|
>1430 |
> |* 1 | TABLE ACCESS FULL | T185 | 416 | 226K|
>1430 |
>
>--------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>
> PLAN_TABLE_OUTPUT
>
>------------------------------------------------------------------------
>--------
> 1 - filter(NLSSORT("T185"."C1")=NLSSORT('HD0000000041608'))
>
> Note: cpu costing is off
>
>
>Regards
>
>Wolfgang Breitling
>Centrex Consulting Corporation
>www.centrexcc.com <http://www.centrexcc.com/>
>
>
>
>------------------------------
>
>End of oracle-l Digest V3 #375
>******************************
>

ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓÊ¼þÏµÍ³¡ª MSN Hotmail¡£ http://www.hotmail.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 22 2006 - 02:09:53 CST