--- Problem
Select owner, index_name, DEGREE from DBA_INDEXES where DEGREE> 1
*
ERROR at line 1:
ORA-01722: invalid number.
-- 1 get execution trace information
SQL> set autotrace traceonly
SQL> select owner, index_name, DEGREE from DBA_INDEXES where DEGREE = 1 and rownum <2;
Elapsed: 00:00:00. 05
Execution Plan
----------------------------------------------------------
Plan hash value: 3920975716
Bytes -------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 120 | 18 (0) | 00:00:01 |
| * 1 | count stopkey |
| 2 | nested loops outer | 1 | 120 | 18 (0) | 00:00:01 |
| 3 | nested loops outer | 1 | 117 | 17 (0) | 00:00:01 |
| 4 | nested loops | 1 | 106 | 16 (0) | 00:00:01 |
| 5 | nested loops | 1 | 102 | 15 (0) | 00:00:01 |
| 6 | nested loops outer | 1 | 85 | 14 (0) | 00:00:01 |
| 7 | nested loops | 1 | 81 | 13 (0) | 00:00:01 |
| 8 | nested loops outer | 1 | 73 | 12 (0) | 00:00:01 |
| 9 | nested loops | 1 | 65 | 11 (0) | 00:00:01 |
| * 10 | table access full | IND $ | 25 | 750 | 10 (0) | 00:00:01 |
| * 11 | table access by index rowid | OBJ $ | 1 | 35 | 1 (0) | 00:00:01 |
| * 12 | index unique scan | I _OBJ1 | 1 | 0 (0) | 00:00:01 |
| 13 | table access by index rowid | OBJ $ | 1 | 8 | 1 (0) | 00:00:01 |
| * 14 | index unique scan | I _OBJ1 | 1 | 0 (0) | 00:00:01 |
| 15 | table access by index rowid | OBJ $ | 1 | 8 | 1 (0) | 00:00:01 |
| * 16 | index unique scan | I _OBJ1 | 1 | 0 (0) | 00:00:01 |
| 17 | table access cluster | USER $ | 1 | 4 | 1 (0) | 00:00:01 |
| * 18 | index unique scan | I _USER # | 1 | 0 (0) | 00:00:01 |
| 19 | table access cluster | USER $ | 1 | 17 | 1 (0) | 00:00:01 |
| * 20 | index unique scan | I _USER # | 1 | 0 (0) | 00:00:01 |
| 21 | table access cluster | USER $ | 1 | 4 | 1 (0) | 00:00:01 |
| * 22 | index unique scan | I _USER # | 1 | 0 (0) | 00:00:01 |
| 23 | table access cluster | SEG $ | 1 | 11 | 1 (0) | 00:00:01 |
| * 24 | index unique scan | I _FILE # _ BLOCK # | 1 | 0 (0) | 00:00:01 |
| 25 | table access cluster | TS $ | 1 | 3 | 1 (0) | 00:00:01 |
| * 26 | index unique scan | I _TS # | 1 | 0 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (ROWNUM <2)
10-filter (TO_NUMBER (DECODE ("I ". "DEGREE", 32767, 'default', TO_CHAR (NVL ("I ". "DEGREE", 1) = 1
And bitand ("I". "FLAGS", 4096) = 0)
11-filter (BITAND ("O". "FLAGS", 128) = 0)
12-access ("O". "OBJ #" = "I". "OBJ #")
14-access ("I". "INDMETHOD #" = "ITO". "OBJ #" (+ ))
16-access ("I". "BO #" = "IO". "OBJ #")
18-access ("ITO". "OWNER #" = "ITU". "USER #" (+ ))
20-access ("U". "USER #" = "O". "OWNER #")
22-access ("IO". "OWNER #" = "IU". "USER #")
24-access ("I ". "TS #" = "S ". "TS #" (+) AND "I ". "FILE #" = "S ". "FILE #" (+) AND
"I". "BLOCK #" = "S". "BLOCK #" (+ ))
26-access ("I". "TS #" = "TS". "TS #" (+ ))
Statistics
----------------------------------------------------------
8 recursive cballs
0 db block gets
23 consistent gets
0 physical reads
0 redo size
655 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 2
Select TO_NUMBER (DECODE ("I ". "DEGREE", 32767, 'default', TO_CHAR (NVL ("I ". "DEGREE", 1) from sys. ind $ I -- Statement Test
-- 3. An object exception is found.
Select TO_NUMBER (DECODE ("I ". "DEGREE", 32767, 'default', TO_CHAR (NVL ("I ". "DEGREE", 1) from sys. ind $ I where obj # <> '123'
-- 4 Query 11 GB in several databases.
-- 10046 abnormal tracking, no problem found
-- The errorstatk trace contains too much internal information and the specific cause cannot be determined.
-- Still reasonable conjecture + data verification to draw conclusions