Update statement wouldn’t use primary index

In execution plan part of sqlt report, we can see SYS_OP_C2C filter, which is the implicit function which is used to convert the column between nchar and char. That’s exactly why the update statement won’t use primary index.

CREATE UNIQUE INDEX “ETLUSER”.”PK_CSTP_DEAL_DAY” ON “ETLUSER”.”CSTP_DEAL_DAY” (“GID_ID”) <<<UNIQUE INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

------------------------------------------------------------------------------------ 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------------ 
| 0 | UPDATE STATEMENT | | | | 8898 (100)| | 
| 1 | UPDATE | CSTP_DEAL_DAY | | | | | 
|* 2 | TABLE ACCESS FULL| CSTP_DEAL_DAY | 6983 | 2113K| 8898 (1)| 00:01:47 | <<<<TABLE ACCESS FULL 
SQL: [+] 
#	Last Captured	Name	Type	Value 
1	2016-07-12/18:35:48	:68	NVARCHAR2(2000)	"5.1.40847880" <<<<Nvarchar2 

SQL Statements Performed Across Database Links run Slowly. Explain Plan Shows Function SYS_OP_C2C has been Applied to Predicates, and Query uses a Full Table Scan. ( Doc ID 2010872.1 )

SYS_OP_C2C Causing Full Table/Index Scans ( Doc ID 732666.1 )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s