Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Cool Unanswered: No of column and No of joins in select statement

    Is anyone can tell me:1)how many columns r possible in single oracle statement?
    2)how many joins are possible in a single select statement in oracle?

    Plz help

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Do you have any Oracle version in mind? Because, these limits may differ for different Oracle version.
    Anyway, these (and many more) figures are available in SQL Reference book, which is part of Oracle documentation, available e.g. online on http://tahiti.oracle.com/.

    For Oracle 10gR2, they are listed in Appendix B "Oracle and Standard SQL", Oracle Compliance with FIPS 127-2 chapter.

    [Edit: typo]

  3. #3
    Join Date
    Sep 2009
    Posts
    7

    No of column and No of joins in select statement

    version is oracle 10 g

  4. #4
    Join Date
    Sep 2009
    Posts
    7
    i said version is oracle 10 g ...i tried searching n the websites u gave but i cud not find answer there

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by greatest_friend
    i said version is oracle 10 g ...i tried searching n the websites u gave but i cud not find answer there
    I am puzzled. If you opened the second link (Oracle Compliance with FIPS 127-2), you must have seen this table:
    Code:
    Table B-12  Sizing for Database Constructs
    Database Constructs 			FIPS 	Oracle Database
    Length of an identifier (in bytes)	18	30
    ...
    Tables referenced in a SQL statement	15	No limit
    Cursors simultaneously open		10	(Note 6)
    Items in a select list			100	1000
    Are these not the figures you wanted?

  6. #6
    Join Date
    Sep 2009
    Posts
    7

    No of column and No of joins in select statement

    hi dear....but the total number of joins in a single select query remains a still mystery for me...i got other anser from u page u recommended....

    plz this quest too....

    thanx

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >2)how many joins are possible in a single select statement in oracle?
    post URL to ISO/ANSI limit for number of joins.

    This SQL language limit; not Oracle.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by greatest_friend
    hi dear....but the total number of joins in a single select query remains a still mystery for me...
    What about "Tables referenced in a SQL statement"?
    Is not join the way how to include multiple tables into a single query?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by greatest_friend
    Is anyone can tell me:1)how many columns r possible in single oracle statement?
    2)how many joins are possible in a single select statement in oracle?
    Don't bother.
    There is no sensible limit whatsoever.
    If there is a limit that you have reached, there is something wrong with your SQL

  10. #10
    Join Date
    Jun 2009
    Posts
    11
    Quote Originally Posted by greatest_friend
    Is anyone can tell me:1)how many columns r possible in single oracle statement?
    2)how many joins are possible in a single select statement in oracle?

    Plz help

    hi greatest friend
    i'm not clear about join,
    but while we creating a table we can add upto 1000 col(max no of col for a table).
    so 1000 columns r possible in single oracle statement.

    >create table table_name(a1 number(2),.....................,a1000 number(2));
    we cannot add ----a1001----to that table.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >we cannot add ----a1001----to that table.
    while above is true, it has NOTHING to do with "number of joins".
    One column can be joined to multiple times in a single SQL.

    Restate your question & provide example if necessary.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Sep 2009
    Posts
    7

    COUNT(*),count(1),count(2).......count(9)

    hm...................
    Last edited by greatest_friend; 09-08-09 at 06:16.

  13. #13
    Join Date
    Sep 2009
    Posts
    7

    No of column and No of joins in select statement

    It means we can have any no of tables in select query and there can be any no of joins since total no of joins in a select query=(n-1)
    where n=total no of tables in a select query

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >since total no of joins in a select query=(n-1) where n=total no of tables in a select query

    Total number of joins can be zero to infinity & is not related to number of tables.

    When number of joins is less than N-1, then Cartesian Product will occur;
    which may be a valid result set.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by anacedent
    When number of joins is less than N-1, then Cartesian Product will occur;
    which may be a valid result set.
    I am afraid you are taking it from the wrong end. As the number of tables is not limited in any kind of SQL statement, it shall not limited in query joining all tables.

    Additionally, according to Oracle documentation (http://download.oracle.com/docs/cd/B...6.htm#i2054012) and even ANSI syntax (CROSS JOIN), cartesian product is also a join (= combines rows from two tables). It just does not have any join condition.

    [Edit: typo]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •