Multi tool use

Is the character “A” a reserved keyword in Oracle Database? [on hold]

scenario:
In Oracle Database, This SQL it's much slower:

SELECT *
FROM TBL_MAIN A, TBL_CHILD_1 B, TBL_CHILD_2 C, TBL_CHILD_3 D, TBL_CHILD_4 E
WHERE A.ID_MAIN = B.ID_MAIN
AND A.ID_MAIN = C.ID_MAIN
AND A.ID_MAIN = D.ID_MAIN
AND A.ID_MAIN = E.ID_MAIN;


than that:

SELECT *
FROM TBL_MAIN X, TBL_CHILD_1 B, TBL_CHILD_2 C, TBL_CHILD_3 D, TBL_CHILD_4 E
WHERE X.ID_MAIN = B.ID_MAIN
AND X.ID_MAIN = C.ID_MAIN
AND X.ID_MAIN = D.ID_MAIN
AND X.ID_MAIN = E.ID_MAIN;


In other words, When I have 3 or more tables joined if I use "A" as an aliasing for "TBL_MAIN" table, it results in a slower query.

It becomes worst when I added more tables in an inner join with table "TBL_MAIN" AS "A".

What is happening?

This question appears to be off-topic. The users who voted to close gave this specific reason:

5 Answers
5

Yes it is. It is part of unary collection operator "IS A SET". In Oracle it is bit complicated, in order to guarantee backward compatibility Oracle has "reserved words" and "keywords". One of them can not be used as identifiers, while others might have special meaning is some contexts - but still can be used as identifiers.

For example you can still use SQL like

select * from commit;
or
select * from join where X is a set;


words like commit, join, model can still be used as identifiers.

Of course this can not have influence on speed of SQL evaluation. Especially when cursor is reused. It might only slow down speed of parsing.

I found that X is actually faster than A, but Z is even slower:

X

A

Z

Alias A 60.257 seconds
Alias X 57.747 seconds
Alias Y 58.383 seconds
Alias Z 62.157 seconds


To be honest, these differences are to small to prove a difference between the names of the aliases.

I tested it with 5 large tables (tbl_main 22 million, tbl_child 17 million etc). And I changed SELECT * into SELECT COUNT(*) to make sure all rows are processed. The first execution is ignored to warm the caches. The next three runs are averaged.

SELECT *

SELECT COUNT(*)

DECLARE
FUNCTION test1(c VARCHAR2) RETURN NUMBER IS
time1 NUMBER; time2 NUMBER; stmt VARCHAR2(3000); n NUMBER;
BEGIN
stmt := q'!
SELECT count(*)
FROM tbl_main #, tbl_child1 B, tbl_child2 C, tbl_child3 D, tbl_child4 E
WHERE #.geb_id = B.geb_id
AND #.geb_id = C.geb_id
AND #.geb_id = D.geb_id
AND #.geb_id = E.geb_id
!';
stmt := REPLACE(stmt, '#', c);
--dbms_output.put_line(stmt);
time1 := dbms_utility.get_time();
EXECUTE IMMEDIATE stmt INTO n;
time2 := dbms_utility.get_time();
return (time2-time1)/100;
END test1;

PROCEDURE test3(c VARCHAR2) IS
ignore NUMBER; seconds NUMBER;
BEGIN
ignore := test1(c);
seconds := (test1(c)+test1(c)+test1(c)) / 3;
DBMS_OUTPUT.PUT_LINE('Alias '||c||' '||round(seconds,3)||' seconds');
END test3;
BEGIN
test3('A');
test3('X');
test3('Y');
test3('Z');
END;
/


Yes. It is in 12c.

SELECT KEYWORD
FROM V$RESERVED_WORDS
WHERE KEYWORD = 'A'
;


Short answer no. A is not a reserved word. If it would be a reserved word, it would raise an error. However to analyze your performance problem, you would need to post the metrics for query execution.

The problem solved itself!
Now the two SQL has the same time, just a little faster like @wolφi said.

This scenario was going on for two days, but now its normal.

I believe after a shutdown the database became normal.

An interesting thing about this is when the problems were happening, SQL Tuning Advisor suggested an execution plan for the SQL who make work normal (99,9 % faster). unfortunately, I didn't save recommendation report in that time to show here.

but now without any SQL profile active, the SQL's are with the same time.