Its been an interesting week on AskTom. Here's a couple of question that seem like odd scenarios, but ultimately (as is often the case) have simple and straightforward explanations.
1) Different object counts after import
Neil posted as question, where he had performed an export in version 9i of the database, and imported it into 11g as part of an upgrade process. What was interesting was that as part of this exercise, the number of indexes in the target system had increased. It was as though the import had auto-magically created more objects in the database. How could this be ?
After some further exploration, the solution becaome apparent. There were Text indexes as part of the schema. When a Text index is created, several other objects are created to support the usage of the index. You can see these tables in your schema, prefixed with DR$. More accurately, you can query USER_TABLES with SECONDARY=Y to see those tables that Oracle created on your behalf.
When Neil moved from 9i to 11g, the internal structure of these DR$ tables has also changed from 9i to 11g. Hence during import, when the import process encountered the task:
create index MY_INDEX on MY_TABLE ( MY_COL ) indextype is ctxsys.context
the number of secondary objects created was different.
See the original post here
2) Predicates in ANSI joins
Giuseppe presented two simple tables, and asked about ANSI outer joins.
SQL> select * from A;
COL_A
----------
1
2
3
4
4 rows selected.
SQL> select * from B;
COL_B
----------
1
2
3
5
4 rows selected.
Giuseppe asked how come what he thought was a standard outer join was not returning the un-matched rows ?
SQL> select a.col_a, b.col_b
2 from a left outer join b
3 on a.col_a=b.col_b
4 where b.col_b <> 5;
COL_A COL_B
---------- ----------
1 1
2 2
3 3
3 rows selected.
But when using the ANSI syntax, there is a subtle difference in the way you specify your predicates. They are either "part" of the join, or applied "after" the join. What Giuseppe probably wanted was:
SQL> select a.col_a, b.col_b
2 from a left outer join b
3 on a.col_a=b.col_b
4 and b.col_b <> 5;
COL_A COL_B
---------- ----------
1 1
2 2
3 3
4
4 rows selected.
See the original post here.
