Home » SQL & PL/SQL » SQL & PL/SQL » Substring on digit in a character expression gives ORA-1722 (Oracle 11.1.0.7.0 )
| Substring on digit in a character expression gives ORA-1722 [message #607071] |
Sun, 02 February 2014 07:28  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I am trying to run a query on a digit that is part of an expression in a varchar2(128 byte) column (object_name , of DBA_OBJECTS view).
When I try to run the following query, I get ORA-1722:
SQL> select *
2 from (select to_number(substr(object_name, 6, 1)) calc_dig,
3 object_name oname,
4 do.*
5 from dba_objects do
6 where object_name like 'TEMP_%'
7 and length(object_name) = 12
8 and do.created < sysdate - 1
9 and object_name in (select table_name from dba_tables)
10 and object_type = 'TABLE')
11 where calc_dig > 1;
from (select to_number(substr(object_name, 6, 1)) calc_dig,
*
ERROR at line 2:
ORA-01722: invalid number
I am convinced that the 6th symbol is a digit,
And when I ran the code in an anonymous block ,
In order to try and catch the place where the error is invoked,
I got no error:
SQL> set serveroutput on
SQL> declare
2 var_a number(2);
3 begin
4 for x in (select to_number(substr(object_name,6,1)) calc_dig ,object_name o
name, do.* from dba_objects do
5 where object_name like 'TEMP_%'
6 and length(object_name) =12
7 and do.created < sysdate-1
8 and object_name in
9 (select table_name from dba_tables)
10 and object_type = 'TABLE')
11 loop
12 dbms_output.put_line(x.object_name);
13 select count(*) into var_a from dba_objects where object_name=x.oname
14 and
15 to_number(substr(object_name,6,1)) in (0,1,2,3,4,5,6,7,8,9);
16 end loop;
17 end;
18 /
TEMP_0005726
TEMP_0094316
TEMP_0285824
TEMP_0358803
...
TEMP_9447168
TEMP_9811212
TEMP_7201104
PL/SQL procedure successfully completed.
1. So, why in the first query do I receive the "ORA-01722: invalid number" ?
2. What can I do in order to perform the check and not get the error?
Thanks in advance,
Andrey
|
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607072 is a reply to message #607071] |
Sun, 02 February 2014 08:02  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>to_number(substr(object_name,6,1)) in (0,1,2,3,4,5,6,7,8,9);
please post result after changing the line above to be the line below
substr(object_name,6,1) NOT in (0,1,2,3,4,5,6,7,8,9);
|
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607073 is a reply to message #607072] |
Sun, 02 February 2014 08:13 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Strangely, it displays the same values...but obviously, the 6th character is still a number here:
SQL> declare
2 var_a number(2);
3 begin
4 for x in (select to_number(substr(object_name,6,1)) calc_dig ,object_name o
name, do.* from dba_objects do
5 where object_name like 'TEMP_%'
6 and length(object_name) =12
7 and do.created < sysdate-1
8 and object_name in
9 (select table_name from dba_tables)
10 and object_type = 'TABLE')
11 loop
12 dbms_output.put_line(x.object_name);
13 select count(*) into var_a from dba_objects where object_name=x.oname
14 and
15 to_number(substr(object_name,6,1)) NOT in (0,1,2,3,4,5,6,7,8,9);
16 end loop;
17 end;
18 /
TEMP_0005726
TEMP_0094316
TEMP_0285824
TEMP_0358803
TEMP_0609784
TEMP_0672720
TEMP_0719374
TEMP_0734556
TEMP_0871600
TEMP_0885671
TEMP_0990275
TEMP_1344832
TEMP_1376136
TEMP_1542471
TEMP_1548788
TEMP_1555941
TEMP_1985120
TEMP_2065280
TEMP_2172676
TEMP_2310800
TEMP_2466075
TEMP_2512072
TEMP_2552685
TEMP_2662516
TEMP_2718140
TEMP_2979265
TEMP_3182396
TEMP_3264976
TEMP_3362625
TEMP_3376276
TEMP_3483504
TEMP_3607352
TEMP_3616670
TEMP_3724492
TEMP_3817381
TEMP_3901550
TEMP_3963840
TEMP_3989888
TEMP_4074908
TEMP_4107144
TEMP_4192576
TEMP_4212744
TEMP_4263009
TEMP_4550364
TEMP_4589109
TEMP_4685104
TEMP_4948239
TEMP_5274104
TEMP_5428316
TEMP_5489730
TEMP_5599956
TEMP_5612172
TEMP_5631982
TEMP_5646992
TEMP_5817964
TEMP_6012522
TEMP_6041488
TEMP_6055989
TEMP_6160694
TEMP_6300590
TEMP_6301196
TEMP_6308113
TEMP_6329184
TEMP_6348063
TEMP_6552000
TEMP_6595068
TEMP_6680243
TEMP_6695548
TEMP_6781462
TEMP_6956861
TEMP_7000168
TEMP_7163674
TEMP_7221120
TEMP_7286832
TEMP_7341800
TEMP_7347400
TEMP_7474904
TEMP_7478522
TEMP_8143857
TEMP_8272592
TEMP_8327040
TEMP_8414438
TEMP_8475222
TEMP_8541254
TEMP_8635952
TEMP_8677279
TEMP_8688703
TEMP_8783956
TEMP_8904347
TEMP_9013731
TEMP_9083333
TEMP_9088782
TEMP_9230024
TEMP_9251633
TEMP_9447168
TEMP_9811212
TEMP_7201104
PL/SQL procedure successfully completed.
SQL>
|
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607074 is a reply to message #607073] |
Sun, 02 February 2014 08:19 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
what results when your run SQL below
SELECT *
FROM (SELECT To_number(Substr(object_name, 6, 1)) calc_dig,
object_name oname,
do.*
FROM dba_objects do
WHERE object_name LIKE 'TEMP_%'
AND Length(object_name) = 12
AND do.created < SYSDATE - 1
AND To_number(Substr(object_name, 6, 1)) IN ( 0, 1, 2, 3,
4, 5, 6, 7,
8, 9 )
AND object_name IN (SELECT table_name
FROM dba_tables)
AND object_type = 'TABLE')
WHERE calc_dig > 1;
|
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607075 is a reply to message #607074] |
Sun, 02 February 2014 08:33 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM (SELECT To_number(Substr(object_name, 6, 1)) calc_dig,
3 object_name oname,
4 do.*
5 FROM dba_objects do
6 WHERE object_name LIKE 'TEMP_%'
7 AND Length(object_name) = 12
8 AND do.created < SYSDATE - 1
9 AND To_number(Substr(object_name, 6, 1)) IN
10 (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
11 AND object_name IN (SELECT table_name FROM dba_tables)
12 AND object_type = 'TABLE')
13 WHERE calc_dig > 1;
FROM (SELECT To_number(Substr(object_name, 6, 1)) calc_dig,
*
ERROR at line 2:
ORA-01722: invalid number
SQL> |
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607076 is a reply to message #607075] |
Sun, 02 February 2014 08:39 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
one more time, please
SELECT count(*)
FROM (SELECT To_number(Substr(object_name, 6, 1)) calc_dig,
object_name oname,
do.*
FROM dba_objects do
WHERE object_name LIKE 'TEMP_%'
AND Length(object_name) = 12
AND do.created < SYSDATE - 1
AND Substr(object_name, 6, 1) IN ( '0', '1', '2', '3',
'4', '5', '6', '7',
'8', '9' )
AND object_name IN (SELECT table_name
FROM dba_tables)
AND object_type = 'TABLE')
WHERE calc_dig > 1;
|
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607077 is a reply to message #607076] |
Sun, 02 February 2014 08:43 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
SQL> SELECT count(*)
2 FROM (SELECT To_number(Substr(object_name, 6, 1)) calc_dig,
3 object_name oname,
4 do.*
5 FROM dba_objects do
6 WHERE object_name LIKE 'TEMP_%'
7 AND Length(object_name) = 12
8 AND do.created < SYSDATE - 1
9 AND Substr(object_name, 6, 1) IN ( '0', '1', '2', '3',
10 '4', '5', '6', '7',
11 '8', '9' )
12 AND object_name IN (SELECT table_name
13 FROM dba_tables)
14 AND object_type = 'TABLE')
15 WHERE calc_dig > 1;
FROM (SELECT To_number(Substr(object_name, 6, 1)) calc_dig,
*
ERROR at line 2:
ORA-01722: invalid number
|
|
|
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607079 is a reply to message #607078] |
Sun, 02 February 2014 08:58 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
SQL> SELECT Substr(object_name, 6, 1),
2 Count(*)
3 FROM dba_objects do
4 WHERE object_name LIKE 'TEMP_%'
5 AND Length(object_name) = 12
6 AND do.created < SYSDATE - 1
7 AND object_name IN (SELECT table_name
8 FROM dba_tables)
9 AND object_type = 'TABLE'
10 GROUP BY Substr(object_name, 6, 1);
SUBS COUNT(*)
---- ----------
1 6
3 12
6 15
0 11
8 11
5 8
7 9
9 7
2 9
4 9
10 rows selected.
SQL> |
|
|
|
| Re: Substring on digit in a character expression gives ORA-1722 [message #607080 is a reply to message #607071] |
Sun, 02 February 2014 09:14 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Andrey_R wrote on Sun, 02 February 2014 08:28I am convinced that the 6th symbol is a digit,
I believe you. But that's exactly what is throwing you offtrack. You are forgetting it is optimizer who decides how to rewrite query and in what order to apply predicates:
SQL> explain plan for
2 select *
3 from (select to_number(substr(object_name, 6, 1)) calc_dig,
4 object_name oname,
5 do.*
6 from dba_objects do
7 where object_name like 'TEMP_%'
8 and length(object_name) = 12
9 and do.created < sysdate - 1
10 and object_name in (select table_name from dba_tables)
11 and object_type = 'TABLE')
12 where calc_dig > 1
13 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3874874694
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 327 | 469 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | DBA_OBJECTS | 2 | 654 | 273 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 30 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | NESTED LOOPS | | 1 | 123 | 267 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 106 | 266 (0)| 00:00:01 |
| 12 | INDEX FULL SCAN | I_USER2 | 132 | 3168 | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 82 | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
|* 17 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 18 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
|* 19 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
|* 22 | FILTER | | | | | |
| 23 | NESTED LOOPS | | 1 | 53 | 3 (0)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | LINK$ | 1 | 36 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 2 | 282 | 196 (1)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
| 28 | NESTED LOOPS OUTER | | 2 | 274 | 194 (1)| 00:00:01 |
| 29 | NESTED LOOPS OUTER | | 2 | 258 | 192 (1)| 00:00:01 |
| 30 | NESTED LOOPS OUTER | | 2 | 248 | 191 (1)| 00:00:01 |
| 31 | NESTED LOOPS | | 2 | 226 | 189 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 2 | 220 | 188 (1)| 00:00:01 |
| 33 | NESTED LOOPS | | 2 | 160 | 187 (1)| 00:00:01 |
| 34 | NESTED LOOPS | | 77 | 3157 | 2 (50)| 00:00:01 |
|* 35 | HASH JOIN | | 1 | 37 | 1 (100)| 00:00:01 |
|* 36 | FIXED TABLE FULL | X$KSPPI | 1 | 31 | 0 (0)| 00:00:01 |
|* 37 | FIXED TABLE FULL | X$KSPPCV | 3341 | 20046 | 0 (0)| 00:00:01 |
| 38 | INDEX FULL SCAN | I_USER2 | 77 | 308 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
|* 39 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 39 | 3 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 2 (0)| 00:00:01 |
|* 41 | TABLE ACCESS CLUSTER | TAB$ | 1 | 30 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 43 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 45 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 48 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 1 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM SYS."X$KSPPI" "X$KSPPI",SYS."X$KSPPCV" "X$KSPPCV","SYS"."USER$"
"CU","SYS"."OBJ$" "CX","SYS"."OBJ$" "O","SYS"."TAB$" "T","SYS"."OBJ$" "CO","SYS"."SEG$" "S","SYS"."TS$"
"TS","SYS"."USER$" "U" WHERE "T"."TS#"="TS"."TS#" AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
"T"."FILE#"="S"."FILE#"(+) AND "T"."TS#"="S"."TS#"(+) AND "T"."BOBJ#"="CO"."OBJ#"(+) AND
"O"."OBJ#"="T"."OBJ#" AND BITAND("T"."PROPERTY",1)=0 AND "O"."NAME"=:B1 AND "O"."OWNER#"="U"."USER#"
AND BITAND("O"."FLAGS",128)=0 AND "T"."DATAOBJ#"="CX"."OBJ#"(+) AND "CX"."OWNER#"="CU"."USER#"(+) AND
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
"INDX"="INDX" AND ("CON_ID"=0 OR "CON_ID"=3) AND "KSPPINM"='_dml_monitoring_enabled' AND ("CON_ID"=0 OR
"CON_ID"=3)))
4 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
5 - access("S"."OBJ#"=:B1)
6 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)
8 - access("EO"."OBJ#"=:B1)
9 - filter( NOT EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "UE" WHERE "TYPE#"=:B1 AND
"UE"."USER#"=:B2) AND "O"."TYPE#"<>88 OR BITAND("O"."FLAGS",1048576)=1048576 OR
BITAND("U"."SPARE1",16)=0 OR (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
"U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))
OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
"O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B3 AND "U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) AND EXISTS (SELECT 0 FROM
SYS."USER_EDITIONING$" "UE" WHERE "UE"."TYPE#"=:B4 AND "UE"."USER#"=:B5))
13 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."CTIME"<SYSDATE@!-1)
14 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME" LIKE 'TEMP_%' AND "O"."LINKNAME" IS NULL)
filter("O"."NAME" LIKE 'TEMP_%' AND LENGTH("O"."NAME")=12 AND
TO_NUMBER(SUBSTR("O"."NAME",6,1))>1 AND "O"."LINKNAME" IS NULL AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."TYPE#"<>10 AND DECODE("O"."TYPE#",0,'NEXT
OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'
PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB
PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE
"S"."OBJ#"=:B1 AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD
PROCESS',100,'FILE WATCHER',101,'DESTINATION',114,'SQL TRANSLATION PROFILE',115,'UNIFIED AUDIT
POLICY','UNDEFINED')='TABLE')
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
16 - access("O"."SPARE3"="U"."USER#")
17 - filter("TYPE#"=:B1 AND "UE"."USER#"=:B2)
19 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
20 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
21 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)
22 - filter(NULL IS NOT NULL AND NULL IS NOT NULL)
24 - filter(LENGTH("L"."NAME")=12 AND "L"."NAME" LIKE 'TEMP_%' AND
TO_NUMBER(SUBSTR("L"."NAME",6,1))>1 AND "L"."CTIME"<SYSDATE@!-1)
26 - access("L"."OWNER#"="U"."USER#")
35 - access("INDX"="INDX")
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
36 - filter("KSPPINM"='_dml_monitoring_enabled' AND ("CON_ID"=0 OR "CON_ID"=3))
37 - filter("CON_ID"=0 OR "CON_ID"=3)
39 - filter(BITAND("O"."FLAGS",128)=0)
40 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1)
41 - filter(BITAND("T"."PROPERTY",1)=0)
42 - access("O"."OBJ#"="T"."OBJ#")
44 - access("T"."TS#"="TS"."TS#")
46 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+))
47 - access("T"."BOBJ#"="CO"."OBJ#"(+))
48 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
49 - access("CX"."OWNER#"="CU"."USER#"(+))
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
125 rows selected.
SQL>
Optimizer simply merges outer query and inline view queries into a single query. Look at predicate information 14: TO_NUMBER(SUBSTR("O"."NAME",6,1))>1 is applied before checking if object type is table. Normally it is resolved by using NO_MERGE hint, however DBA_OBJECTS it quite complex view and hint gets ignored. We can force optimizer not to merge by adding ROWNUM to inline view. This way optimizer realizes it can't merge since then ROWNUM will be calculated incorrectly:
SQL> create table temp_2bcdefg(n number)
2 /
Table created.
SQL> create or replace
2 view tempabcdefgh
3 as select * from dual
4 /
View created.
SQL> select *
2 from (select to_number(substr(object_name, 6, 1)) calc_dig,
3 object_name oname --,
4 -- do.*
5 from dba_objects do
6 where object_name like 'TEMP_%'
7 and length(object_name) = 12
8 -- and do.created < sysdate - 1
9 and object_name in (select table_name from dba_tables)
10 and object_type = 'TABLE')
11 where calc_dig > 1
12 /
from (select to_number(substr(object_name, 6, 1)) calc_dig,
*
ERROR at line 2:
ORA-01722: invalid number
SQL> select *
2 from (select to_number(substr(object_name, 6, 1)) calc_dig,
3 object_name oname,
4 -- do.*,
5 rownum rn
6 from dba_objects do
7 where object_name like 'TEMP_%'
8 and length(object_name) = 12
9 -- and do.created < sysdate - 1
10 and object_name in (select table_name from dba_tables)
11 and object_type = 'TABLE') v
12 where calc_dig > 1
13 /
CALC_DIG ONAME RN
---------- ------------------------------ ----------
2 TEMP_2BCDEFG 1
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 01:46:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
