9

We recently got some errors in our alert log of the form

301598: Tue Jul 16 03:58:29 2013
301599: ORA-01555 caused by SQL statement below 
301600: SELECT * FROM RELATIONAL(<owner>.<table>)

I know how to handle ORA-01555 errors, but I never encountered something like SELECT * FROM RELATIONAL. I looked in DBA_OBJECTS, but there is no such function/operator/whatever.

Googling for 'SELECT * FROM RELATIONAL' only returned questions related to ORA-01555.

So, my question is: What is the meaning of the RELATIONAL keyword here?

8
  • 2
    It seems that this is something deeply internal. Because support.oracle.com also shows only the same information that you already googled. Jul 18, 2013 at 13:56
  • 2
    I am 99% certain this is for querying the XMLTYPE datatype, but I can't find the reference as obviously relational is a very difficult word to search DB documentation for.
    – Gaius
    Jul 25, 2013 at 11:02
  • 1
    @Gaius I don't think it's XMLTYPE related (the table doesn't contain an XMLTYPE column). Jul 25, 2013 at 11:19
  • 1
    Found this, might help: forums.oracle.com/thread/1042943?start=0&tstart=0
    – Iľja
    Sep 6, 2013 at 9:01
  • change your undo_retention parameter Apr 21, 2014 at 12:18

4 Answers 4

1

It appears to be inert. I tested this on XE 11 and found that it does nothing.

CREATE TABLE dbausers AS SELECT * FROM dba_users;

-- Function doesn't affect result set.
SELECT * FROM dbausers
MINUS
SELECT * FROM RELATIONAL(dbausers);

SELECT * FROM RELATIONAL(dbausers)
MINUS
SELECT * FROM dbausers;
0

I believe this was a deprecated alias to today's TABLE function for pipelined functions, but now I can't find my reference that says this.

0

According to this blog article the specific hint RELATIONAL instructs the Query Optimizer to

... convert the object to a relational table, similar to RELATIONAL function ...

A second article in the Oracle Community states that

The RELATIONAL hint causes Oracle to unnest the object data and insert the attribute data itself rather than the object. The hints used can only be used by Oracle - which is why they are undocumented...

I think this is just an issue of a long running query, which is being exposed by the ORA-01555 error while internally using a query hint, which is undocumented.

1
  • 1
    Query hints are nested in /* comment blocks */. This appears to be something else.
    – durette
    Jan 17, 2018 at 17:14
0

As demonstrated by durette, this a useless cast. Most tables are RELATIONAL, and that's the default. However, Oracle also supports OBJECT tables (see the syntax on in Oracle's CREATE TABLE docs), which explains the reason for the internal syntax. There isn't any reason to use RELATIONAL in a query but there is also no reason for it to fail. You should focus on the general fixes for ORA-01555. ORA-01555 usually means your UNDO segments are not sufficient to handle the rate of change in your database.

Are you able to reproduce this error on demand? How log does the offending query run before it fails? Or is your real question, "How do I track down the SQL that caused this error?"

Also, you didn't specify your Oracle version and patch level.

3
  • 2
    You realize, of course, that the question is almost six years old.
    – mustaccio
    Apr 24, 2019 at 18:05
  • I wasn't interested in fixing the ORA-01555 (I know how to do that, as I mentioned in my question). I was just interested in the strange RELATIONAL function call / syntax, but my curiosity has waned over the years :-) Apr 25, 2019 at 6:42
  • 1
    If you no longer want feedback on this question, you can delete it or accept an answer. As long as stack exchange considers this question unanswered, it will continue prompting people to help. Jun 11, 2019 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.