Function based indexes?

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Function based indexes?

Function based indexes?

2005-12-06 - By Wolfgang Breitling

Reply: << 11 12 13 14

David and all,

this is why cursor_sharing != exact will make a difference:

20:01:55 ora92.scott> alter session set cursor_sharing=force;

Session altered.

20:02:31 ora92.scott> alter session set events '10046 trace name
context forever';

Session altered.

20:05:50 ora92.scott> alter session set events '10046 trace name
context forever, level 4';

Session altered.

20:06:00 ora92.scott> select count(t) from dmk where a = 1;

COUNT(T)
-- ---- --
30000

1 row selected.

20:06:17 ora92.scott> select count(t) from dmk where a = 0;

COUNT(T)
-- ---- --
2

1 row selected.

20:06:18 ora92.scott> select count(t) from dmk where case a when 0
then 0 end = 0;

COUNT(T)
-- ---- --
2

1 row selected.

20:06:18 ora92.scott> select hash_value, sql_text from v$sql where
sql_text like 'select count(%) from dmk%';

HASH_VALUE SQL_TEXT
-- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-
1544817296 select count(t) from dmk where a = :"SYS_B_0"
2741099339 select count(t) from dmk where case a when :"SYS_B_0" then
:"SYS_B_1" end = :"SYS_B_2"

2 rows selected.

20:07:42 ora92.scott> @(protected) 1544817296

cn plan
hash ELAPSED CPU exec LIO PIO
rows SQL_TEXT
--- -- ---- -- -- ---- --- -- ---- --- -- ----- -- ---- ------
-- ---- --- -- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
0
3494866697 0.202 0.135 2 3,584
198 2 select count(t) from dmk where a = :"SYS_B_0"

id cost CARD
operation
ROWS ELAPSED CR_GETS CU_GETS READS WRITES
-- --- -- --- -- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- -- -- ---- --- -- ---- --- -- ---- --- -- ---- -- -- ----
0 308 SELECT STATEMENT
1 1 SORT
AGGREGATE 1
0.030 1,792 0 99 0
2F 308 15,001 TABLE ACCESS FULL
DMK 2 0.030
1,792 0 99 0

id PREDICATES
---- -- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
2 Filter= "DMK"."A"=:SYS_B_0

20:07:55 ora92.scott> @(protected) 2741099339

plan
hash ELAPSED CPU exec LIO PIO
rows SQL_TEXT
-- ---- -- -- ---- --- -- ---- --- -- ----- -- ---- ------
-- ---- --- -- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
3494866697 0.064 0.040 1 1,792
99 1 select count(t) from dmk where case a when :"SYS_B_0"
then :"SYS

_B_1"
end = :"SYS_B_2"

id cost CARD
operation
ROWS ELAPSED CR_GETS CU_GETS READS WRITES
-- --- -- --- -- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- -- -- ---- --- -- ---- --- -- ---- --- -- ---- -- -- ----
0 314 SELECT STATEMENT
1 1 SORT
AGGREGATE 1
0.030 1,792 0 99 0
2F 314 300 TABLE ACCESS FULL
DMK 2 0.030
1,792 0 99 0

id PREDICATES
---- -- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
2 Filter= CASE "DMK"."A" WHEN :SYS_B_0 THEN :SYS_B_1 END =:SYS_B_2

20:08:05 ora92.scott>

I deliberately did not use autotrace and got the plans from
v$sql_plan in order to give bind variable peeking a chance to do its
thing. Well, obviously it leaves something to be desired.

At 06:46 PM 12/6/2005, David Kurtz wrote:
>I am quite rightly taken to task. My example was produced on 9.2.0.5.0 on
>Windows.
>I used analyze because I was being lazy. However, I have now checked
>dbms_stats on both 9.2.0.5 and 10.1.0.3 and got the same behaviour.
>
>But, why would cursor_sharing make any difference?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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